Re: Rewrite LUW query to work on z/OS

[VGD]

"Serge Rielau" <srielau@ca.ibm .comescribió en el mensaje
news:6e8m5mF5uf 8oU1@mid.indivi dual.net...

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.sysdummy 1 union
select 'V2' as C1 from sysibm.sysdummy 1 union
select 'V3' as C1 from sysibm.sysdummy 1 union
select 'V4' as C1 from sysibm.sysdummy 1 union
select 'Vempty' as C1 from sysibm.sysdummy 1 union
select 'Vempty' as C1 from sysibm.sysdummy 1 union
) as Value_list
on
Table_val.C1 = Value_list.C1
where
Table_val.C1 <'Vempty';


Thank you very much

Vicente

[Lennart]

Re: Rewrite LUW query to work on z/OS

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

"Serge Rielau" <srie...@ca.ibm .comescribió en el mensajenews:6e8 m5mF5uf8oU1@mid .individual.net ...
>
>
>

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.sysdummy 1 union
select 'V2' as C1 from sysibm.sysdummy 1 union
select 'V3' as C1 from sysibm.sysdummy 1 union
select 'V4' as C1 from sysibm.sysdummy 1 union
select 'Vempty' as C1 from sysibm.sysdummy 1 union
select 'Vempty' as C1 from sysibm.sysdummy 1 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

[Serge Rielau]

Re: Rewrite LUW query to work on z/OS

Lennart wrote:

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

>"Serge Rielau" <srie...@ca.ibm .comescribió en el mensajenews:6e8 m5mF5uf8oU1@mid .individual.net ...
>>
>>
>>

>>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.sysdummy 1 union
> select 'V2' as C1 from sysibm.sysdummy 1 union
> select 'V3' as C1 from sysibm.sysdummy 1 union
> select 'V4' as C1 from sysibm.sysdummy 1 union
> select 'Vempty' as C1 from sysibm.sysdummy 1 union
> select 'Vempty' as C1 from sysibm.sysdummy 1 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

[VGD]

Re: Rewrite LUW query to work on z/OS

>

"Lennart" <Erik.Lennart.J onsson@gmail.co mescribió en el mensaje

news:ac5845b9-50a4-4902-b88d-54aec712439e@k3 0g2000hse.googl egroups.com...

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.sysdummy 1 union
select 'V2' as C1 from sysibm.sysdummy 1 union
select 'V3' as C1 from sysibm.sysdummy 1 union
select 'V4' as C1 from sysibm.sysdummy 1 union
select 'Vempty' as C1 from sysibm.sysdummy 1 union
select 'Vempty' as C1 from sysibm.sysdummy 1 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.sysdummy 1 union
select :SearchFor[1] as C1 from sysibm.sysdummy 1 union
select :SearchFor[2] as C1 from sysibm.sysdummy 1 union
select :SearchFor[3] as C1 from sysibm.sysdummy 1 union
select :SearchFor[4] as C1 from sysibm.sysdummy 1 union
select :SearchFor[5] as C1 from sysibm.sysdummy 1 union
select :SearchFor[6] as C1 from sysibm.sysdummy 1 union
select :SearchFor[7] as C1 from sysibm.sysdummy 1 union
select :SearchFor[8] as C1 from sysibm.sysdummy 1 union
select :SearchFor[9] as C1 from sysibm.sysdummy 1
) as Value_list
on
Table_val.C1 = Value_list.C1
where
Table_val.C1 <'Vempty';


Regards

Vicente