Lennart wrote:
> On Jul 17, 3:03 pm, "VGD" <vgar...@[EMAIL PROTECTED]
> wrote:
>> "Serge Rielau" <srie...@[EMAIL PROTECTED]
> escribió en el
mensajenews:6e8m5mF5uf8oU1@[EMAIL PROTECTED]
>>
>>
>>
>>> I have my doubts as to whether DB2 V8 for zOS sup****ts 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
|
