How to conditionally change the 2nd part of a full join
От | Moreno Andreo |
---|---|
Тема | How to conditionally change the 2nd part of a full join |
Дата | |
Msg-id | 988173e8-b327-285a-6461-9c5d17613089@evolu-s.it обсуждение исходный текст |
Список | 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.-
В списке pgsql-general по дате отправления: