Re: Rewrite LUW query to work on z/OS

[VGD]

"Serge Rielau" <sr*****@ca.ibm.comescribió en el mensaje
news:6e************@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.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

[Lennart]

On Jul 17, 3:03 pm, "VGD" <[e-mail]:

"Serge Rielau" <[e-mail]ó en el mensajenews:6e************@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.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

[Serge Rielau]

Lennart wrote:

On Jul 17, 3:03 pm, "VGD" <[e-mail]:

>"Serge Rielau" <[e-mail]ó en el mensajenews:6e************@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.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

[VGD]

>

"Lennart" <Er******************@gmail.comescribió en el mensaje

news:ac**********************************@k30g2000 hse.googlegroups.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.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