agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedPartition by outer join
10+ messages / 5 participants
[nested] [flat]
* Partition by outer join
@ 2021-08-23 15:52 aditya desai <admad123@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: aditya desai @ 2021-08-23 15:52 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi,
While trying to migrate Oracle to Postgres wera stuck with PARTITION BY
OUTER JOIN. Can someone help rewriting or helping with Postgres
equivalent of below query? Thanks in advance.
Select csa_mast.cconsol,
csa_mast.tics_code,
csa_mast.csa_id,
csa_mast.csa_type,
csa_mast.ccy,
csa_mast.collateral_curve_id,
csa_mast.check_non_std_flags
from dummy
left outer join csa_mast partition by (csa_mast.cconsol)
on ( (did = 1 and (csa_id is null or csa_mast.csa_type = ‘LEG’))
or
(d.id = 2 and csa_mast.csa_type = ‘REG’)
)
Where d.id = 1
Or (d.id = 2 and csa_mast.csa_type = ‘REG’)
Regards,
Aditya.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partition by outer join
@ 2021-08-24 03:58 aditya desai <admad123@gmail.com>
parent: aditya desai <admad123@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: aditya desai @ 2021-08-24 03:58 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi,
Can someone help? Thanks in advance.
Regards,
Aditya.
On Mon, Aug 23, 2021 at 9:22 PM aditya desai <admad123@gmail.com> wrote:
> Hi,
> While trying to migrate Oracle to Postgres wera stuck with PARTITION BY
> OUTER JOIN. Can someone help rewriting or helping with Postgres
> equivalent of below query? Thanks in advance.
>
> Select csa_mast.cconsol,
>
> csa_mast.tics_code,
>
> csa_mast.csa_id,
>
> csa_mast.csa_type,
>
> csa_mast.ccy,
>
> csa_mast.collateral_curve_id,
>
> csa_mast.check_non_std_flags
>
> from dummy
>
> left outer join csa_mast partition by (csa_mast.cconsol)
>
> on ( (did = 1 and (csa_id is null or csa_mast.csa_type = ‘LEG’))
>
> or
>
> (d.id = 2 and csa_mast.csa_type = ‘REG’)
>
> )
>
> Where d.id = 1
>
> Or (d.id = 2 and csa_mast.csa_type = ‘REG’)
>
>
> Regards,
>
> Aditya.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partition by outer join
@ 2021-08-24 06:32 hubert depesz lubaczewski <depesz@depesz.com>
parent: aditya desai <admad123@gmail.com>
2 siblings, 2 replies; 10+ messages in thread
From: hubert depesz lubaczewski @ 2021-08-24 06:32 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Mon, Aug 23, 2021 at 09:22:47PM +0530, aditya desai wrote:
> While trying to migrate Oracle to Postgres wera stuck with PARTITION BY
> OUTER JOIN. Can someone help rewriting or helping with Postgres
> equivalent of below query? Thanks in advance.
It would make MUCH more sense to show us data and expected output.
Why do you assume Pg dbas know and understand intricacies of Oracle?
depesz
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partition by outer join
@ 2021-08-24 06:39 Thomas Kellerer <shammat@gmx.net>
parent: aditya desai <admad123@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Thomas Kellerer @ 2021-08-24 06:39 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
aditya desai schrieb am 23.08.2021 um 17:52:
> While trying to migrate Oracle to Postgres wera stuck with PARTITION
> BY OUTER JOIN. Can someone help rewriting or helping with Postgres
> equivalent of below query? Thanks in advance.
>
Have a look here:
https://dba.stackexchange.com/questions/227069/what-is-a-partitioned-outer-join
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partition by outer join
@ 2021-08-24 06:49 aditya desai <admad123@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 1 reply; 10+ messages in thread
From: aditya desai @ 2021-08-24 06:49 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Agree!! I will try to get data, which looks difficult actually. Thanks for
your response.
On Tue, Aug 24, 2021 at 12:02 PM hubert depesz lubaczewski <
depesz@depesz.com> wrote:
> On Mon, Aug 23, 2021 at 09:22:47PM +0530, aditya desai wrote:
> > While trying to migrate Oracle to Postgres wera stuck with PARTITION BY
> > OUTER JOIN. Can someone help rewriting or helping with Postgres
> > equivalent of below query? Thanks in advance.
>
> It would make MUCH more sense to show us data and expected output.
> Why do you assume Pg dbas know and understand intricacies of Oracle?
>
> depesz
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partition by outer join
@ 2021-08-24 14:15 David G. Johnston <david.g.johnston@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2021-08-24 14:15 UTC (permalink / raw)
To: Hubert Lubaczewski <depesz@depesz.com>; +Cc: aditya desai <admad123@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
On Mon, Aug 23, 2021 at 11:32 PM hubert depesz lubaczewski <
depesz@depesz.com> wrote:
> On Mon, Aug 23, 2021 at 09:22:47PM +0530, aditya desai wrote:
> > While trying to migrate Oracle to Postgres wera stuck with PARTITION BY
> > OUTER JOIN. Can someone help rewriting or helping with Postgres
> > equivalent of below query? Thanks in advance.
>
> It would make MUCH more sense to show us data and expected output.
> Why do you assume Pg dbas know and understand intricacies of Oracle?
>
>
Yeah, posting with the hope that someone with cross-DB knowledge might be
available to chime in makes sense. But absent any response one should then
try and frame the problem in such a way as to not require knowledge beyond
what PostgreSQL can do.
But a simple paragraph describing what the query is doing would be an ok
starting point - though as always the more detailed and self-contained the
problem statement the better.
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partition by outer join
@ 2021-08-24 16:01 Steve Midgley <science@misuse.org>
parent: aditya desai <admad123@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Steve Midgley @ 2021-08-24 16:01 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: depesz@depesz.com, pgsql-sql <pgsql-sql@lists.postgresql.org>
On Mon, Aug 23, 2021 at 11:49 PM aditya desai <admad123@gmail.com> wrote:
> Agree!! I will try to get data, which looks difficult actually. Thanks for
> your response.
>
> On Tue, Aug 24, 2021 at 12:02 PM hubert depesz lubaczewski <
> depesz@depesz.com> wrote:
>
>> On Mon, Aug 23, 2021 at 09:22:47PM +0530, aditya desai wrote:
>> > While trying to migrate Oracle to Postgres wera stuck with PARTITION BY
>> > OUTER JOIN. Can someone help rewriting or helping with Postgres
>> > equivalent of below query? Thanks in advance.
>>
>> It would make MUCH more sense to show us data and expected output.
>> Why do you assume Pg dbas know and understand intricacies of Oracle?
>>
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partition by outer join
@ 2021-08-24 16:09 David G. Johnston <david.g.johnston@gmail.com>
parent: Steve Midgley <science@misuse.org>
0 siblings, 2 replies; 10+ messages in thread
From: David G. Johnston @ 2021-08-24 16:09 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: aditya desai <admad123@gmail.com>; Hubert Lubaczewski <depesz@depesz.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
On Tue, Aug 24, 2021 at 9:02 AM Steve Midgley <science@misuse.org> wrote:
>
> From that stackexchange post, it appears that partition in Oracle is
> similar to cross joins in Postgres -- where elements are included in the
> result set as a matrix, whether they exist in the source data or not (so
> sparsely populated values are included as nulls when they exist in either
> of the two columns that are cross joined).
>
What you describe sounds like it would be an SQL Standard (I think) "FULL
OUTER JOIN".
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partition by outer join
@ 2021-08-24 16:45 Thomas Kellerer <shammat@gmx.net>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Thomas Kellerer @ 2021-08-24 16:45 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
David G. Johnston schrieb am 24.08.2021 um 18:09:
>> From that stackexchange post, it appears that partition in Oracle
>> is similar to cross joins in Postgres -- where elements are
>> included in the result set as a matrix, whether they exist in the
>> source data or not (so sparsely populated values are included as
>> nulls when they exist in either of the two columns that are cross
>> joined).
>
>
> What you describe sounds like it would be an SQL Standard (I think)
> "FULL OUTER JOIN".
I think the partitioned outer join generates missing values on the fly
based on the "partition" information. So I think it's a bit different
than "just" a full outer join.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Partition by outer join
@ 2021-08-26 03:37 aditya desai <admad123@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: aditya desai @ 2021-08-26 03:37 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Steve Midgley <science@misuse.org>; Hubert Lubaczewski <depesz@depesz.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
Thanks David and Steve!! I will take a look into this. Having a hard time
getting data over here. Apologies.
Regards,
AD.
On Tue, Aug 24, 2021 at 9:39 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Tue, Aug 24, 2021 at 9:02 AM Steve Midgley <science@misuse.org> wrote:
>
>>
>> From that stackexchange post, it appears that partition in Oracle is
>> similar to cross joins in Postgres -- where elements are included in the
>> result set as a matrix, whether they exist in the source data or not (so
>> sparsely populated values are included as nulls when they exist in either
>> of the two columns that are cross joined).
>>
>
> What you describe sounds like it would be an SQL Standard (I think) "FULL
> OUTER JOIN".
>
> David J.
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2021-08-26 03:37 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-23 15:52 Partition by outer join aditya desai <admad123@gmail.com>
2021-08-24 03:58 ` aditya desai <admad123@gmail.com>
2021-08-24 06:32 ` hubert depesz lubaczewski <depesz@depesz.com>
2021-08-24 06:49 ` aditya desai <admad123@gmail.com>
2021-08-24 16:01 ` Steve Midgley <science@misuse.org>
2021-08-24 16:09 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-24 16:45 ` Thomas Kellerer <shammat@gmx.net>
2021-08-26 03:37 ` aditya desai <admad123@gmail.com>
2021-08-24 14:15 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-24 06:39 ` Thomas Kellerer <shammat@gmx.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox