How to conditionally change the 2nd part of a full join

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.-

Browse pgsql-general by date

  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