Thread: Partition by outer join
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.
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.
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
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
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
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.
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?
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). So I would start digging on cross joins.
And if you can produce a data set (ideally with create / insert statements) that represents your source, and you can produce a desired output, I've found members of this group pretty quickly produce a solution in Postgres.
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.
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.
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.