From: | Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it> |
---|---|
To: | PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org> |
Subject: | How to conditionally change the 2nd part of a full join |
Date: | 2019-08-14 16:49:39 |
Message-ID: | 988173e8-b327-285a-6461-9c5d17613089@evolu-s.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I need to obtain a single record from 2 records in the same table
grouping for a key, say
id value value2
1 2 5
1 2 7
the result would be
1 2 5 7
and that works fine with a full join:
SELECT * FROM
(SELECT idp, data, i1, i2, m1, m2 from tblwk WHERE recordkey = 4) s1
FULL JOIN
(SELECT idp, data, i1, m1, m2 from tblwk WHERE recordkey = 10) s2
USING (data, idp)
Now, to get another dataset, I need the second subselect to change based
on a value acquired from the first one. I tried with a case
SELECT * FROM (
SELECT idp, data, idd, rif1, rif2, t1, t2, t3, t5, t7, t9, t10, i1,
i2, i3, dg from tblwk WHERE recordkey = 1) s1
FULL JOIN
case
when i1=1 then (SELECT idp, data, desc, rif1, rif3, t1, t2,
t5 from tblwk WHERE recordkey = 2) s2
when i1=2 then (SELECT idp, data, desc, rif1, t4, t5, i2
from tblwk WHERE recordkey = 3 order by i2) s2
when i1=3 then (SELECT idp, data, desc, rif1, t2, t5, t6, i2
from tblwk WHERE recordkey = 4 order by i2) s2
when i1=4 then (SELECT idp, data, desc, i2 from tblwk WHERE
recordkey = 9) s2
end
USING (data, idp)
but it doesn't like "case" after a FULL JOIN.
I read a bit of docs and discovered LATERAL, but AFAIK it's useless here.
Is it necessary to write a function (which would be my last resort, not
just because I'm not so good in writing functions) or there is some SQL
syntax that can come in help?
Thanks
Moreno.-
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2019-08-14 17:09:09 | Converting Access .mdb to postgres |
Previous Message | Adrian Klaver | 2019-08-14 14:13:43 | Re: ORA-24345: A Truncation or null fetch error occurred -ora2pg |