Re: Rewrite LUW query to work on z/OS

On Jul 17, 3:03 pm, "VGD" <vgar...@boxcounty.comwrote:

Quote: "Serge Rielau" <srie...@ca.ibm.comescribió en el mensajenews:6e8m5mF5uf8oU1@mid.individual.net... > > > Quote: I have my doubts as to whether DB2 V8 for zOS supports the WITH clause. Try this: select distinct value_list.C1 , case when Table_val.C2 is null then '?' else Table_val.C2 end from Table_val right outer join ( values ('V2'), ('V2'), ('V3'), ('V4'), ('Vempty'), ('Vempty') ) AS value_list ... > Quote: I'm wondering about the VALUES clause as well. For a LEFT OUTER JOIN the rewrite would be an IN list. Not obvious (to me) how to get rid of it for a ROJ other than inserting the content into a temp table. > Quote: Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab > Hello Serge > I've checked that DB2 OS/390 8.1.5 accepts WITH clause but doesn't accept VALUES clause in the context I need. > This is the final SQL sentence we will use: > select distinct value_list.C1 , case when Table_val.C2 is null then '?' else Table_val.C2 end from Table_val right outer join ( select 'V2' as C1 from sysibm.sysdummy1 union select 'V2' as C1 from sysibm.sysdummy1 union select 'V3' as C1 from sysibm.sysdummy1 union select 'V4' as C1 from sysibm.sysdummy1 union select 'Vempty' as C1 from sysibm.sysdummy1 union select 'Vempty' as C1 from sysibm.sysdummy1 union ) as Value_list on Table_val.C1 = Value_list.C1 where Table_val.C1 <'Vempty'; > Thank you very much > Vicente

Why do you have V2 and Vempty twice in Value_list?

/Lennart

Lennart wrote:

Quote: On Jul 17, 3:03 pm, "VGD" <vgar...@boxcounty.comwrote: Quote: >"Serge Rielau" <srie...@ca.ibm.comescribió en el mensajenews:6e8m5mF5uf8oU1@mid.individual.net... >> >> >> Quote: >>I have my doubts as to whether DB2 V8 for zOS supports the WITH clause. >>Try this: >> select >> distinct >> value_list.C1 , >> case when Table_val.C2 is null then '?' else Table_val.C2 end >> from >> Table_val >> right outer join >>( values >> ('V2'), >> ('V2'), >> ('V3'), >> ('V4'), >> ('Vempty'), >> ('Vempty') >> ) AS value_list >>... >>I'm wondering about the VALUES clause as well. >>For a LEFT OUTER JOIN the rewrite would be an IN list. >>Not obvious (to me) how to get rid of it for a ROJ other than inserting >>the content into a temp table. >>Cheers >>Serge >>-- >>Serge Rielau >>DB2 Solutions Development >>IBM Toronto Lab >Hello Serge >> >I've checked that DB2 OS/390 8.1.5 accepts WITH clause but doesn't accept >VALUES clause in the context I need. >> >This is the final SQL sentence we will use: >> > select > distinct > value_list.C1 , > case when Table_val.C2 is null then '?' else Table_val.C2 end > from > Table_val > right outer join > ( > select 'V2' as C1 from sysibm.sysdummy1 union > select 'V2' as C1 from sysibm.sysdummy1 union > select 'V3' as C1 from sysibm.sysdummy1 union > select 'V4' as C1 from sysibm.sysdummy1 union > select 'Vempty' as C1 from sysibm.sysdummy1 union > select 'Vempty' as C1 from sysibm.sysdummy1 union > ) as Value_list > on > Table_val.C1 = Value_list.C1 > where > Table_val.C1 <'Vempty'; >> >Thank you very much >> > Vicente > Why do you have V2 and Vempty twice in Value_list?

...a ssuming that that's purpose then you want to use UNION ALL to
preserve the duplicates.
(You want UNION ALL wherever possible)
Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>

Quote: "Lennart" <Erik.Lennart.Jonsson@gmail.comescribió en el mensaje

news:ac5845b9-50a4-4902-b88d-54aec712439e@k30g2000hse.googlegroups.com...

Quote: Quote: This is the final SQL sentence we will use: select distinct value_list.C1 , case when Table_val.C2 is null then '?' else Table_val.C2 end from Table_val right outer join ( select 'V2' as C1 from sysibm.sysdummy1 union select 'V2' as C1 from sysibm.sysdummy1 union select 'V3' as C1 from sysibm.sysdummy1 union select 'V4' as C1 from sysibm.sysdummy1 union select 'Vempty' as C1 from sysibm.sysdummy1 union select 'Vempty' as C1 from sysibm.sysdummy1 union ) as Value_list on Table_val.C1 = Value_list.C1 where Table_val.C1 <'Vempty'; Thank you very much Vicente > Why do you have V2 and Vempty twice in Value_list? > /Lennart >

I need to search for up to 10 items and I need to know those who have not
been found. And, for unused SearchFor arguments, the main program will fill
it up with 'Vempty' ("never spected to be found" value) because C1 can be
blank.

In fact, the real SQL is more like this:

select
distinct
value_list.C1 ,
case when Table_val.C2 is null then '<C2 not found>' else
Table_val.C2 end
case when Table_val.C3 is null then '<C3 not found>' else
Table_val.C3 end
case when Table_val.C4 is null then '<C4 not found>' else
Table_val.C4 end
case when Table_val.C5 is null then '<C5 not found>' else
Table_val.C5 end
from
Table_val
right outer join
(
select :SearchFor[0] as C1 from sysibm.sysdummy1 union
select :SearchFor[1] as C1 from sysibm.sysdummy1 union
select :SearchFor[2] as C1 from sysibm.sysdummy1 union
select :SearchFor[3] as C1 from sysibm.sysdummy1 union
select :SearchFor[4] as C1 from sysibm.sysdummy1 union
select :SearchFor[5] as C1 from sysibm.sysdummy1 union
select :SearchFor[6] as C1 from sysibm.sysdummy1 union
select :SearchFor[7] as C1 from sysibm.sysdummy1 union
select :SearchFor[8] as C1 from sysibm.sysdummy1 union
select :SearchFor[9] as C1 from sysibm.sysdummy1
) as Value_list
on
Table_val.C1 = Value_list.C1
where
Table_val.C1 <'Vempty';


Regards

Vicente