Re: Rewrite LUW query to work on z/OS

"Serge Rielau" <srielau@[EMAIL PROTECTED]
> escribió en el mensaje
news: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