Re: Rewrite LUW query to work on z/OS

>
> "Lennart" <Erik.Lennart.Jonsson@[EMAIL PROTECTED]
> escribió en el mensaje
news:ac5845b9-50a4-4902-b88d-54aec712439e@[EMAIL PROTECTED]
> >
> > 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