VGD wrote:
> Hello
>
> I've a table Table_val as follows:
> C1 C2
> ---- ----
> V1 val1
> V2 val1
> V2 val2
> V2 val3
> V3 val2
> V3 val4
>
> Also there is a list of values to search in C1:
> V2, V2, V3, V4, Vempty, Vempty
>
> I'm looking for an output like this:
> C1 C2
> ---- ----
> V4 ?
> V2 val1
> V2 val2
> V3 val2
> V2 val3
> V3 val4
>
> Using DB2/NT 7.2.9 and executing this query
> -------------------------------
> with
> Value_list(C1)
> as
> ( values
> ('V2'),
> ('V2'),
> ('V3'),
> ('V4'),
> ('Vempty'),
> ('Vempty')
> )
> select
> distinct
> value_list.C1 ,
> case when Table_val.C2 is null then '?' else Table_val.C2 end
> from
> Table_val
> right outer join
> Value_list
> on
> Table_val.C1 = Value_list.C1
> where
> Table_val.C1 <> 'Vempty';
>
> -------------------------------
>
> it works ok and outputs the desired data.
>
> But I need to run the query against the same data set on a DB2 OS/390
8.1.5
> and then I got this error:
>
> SQL0104N An unexpected token "Value_list" was found following "".
> Expected tokens may include: "DSN_INLINE_OPT_HINT". SQLSTATE=42601
>
> Then the question is: How I need to rewrite the query in order to obtain
the
> desired output using DB2 OS/390 8.1.5 ?
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
|
