Re: where something IN decode

From: Mark D Powell <mark.powell_at_eds.com>
Date: 23 Jan 2002 06:07:42 -0800
Message-ID: <178d2795.0201230607.5d391e6e@posting.google.com>

zzesers2002_at_yahoo.com (Zzesers) wrote in message news:<99584c0b.0201221337.1cb9dcd3_at_posting.google.com>...
> DStevens <dstevens_at_navidec.com> wrote in message news:<a2k12j$8ju$2_at_newsreader.mailgate.org>...
> > select * from dual where 0 in (decode(0,0,1));
> >
> >
> > >
>
>
> Thanks DStevens, but what I need is a decode statement that allows me
> to have a set of numbers for the in statement. Something that would
> be akin to:
>
> If the decode is true:
>
> select * from dual where 0 in (0,1,2);
>
> otherwise:
>
> select * from dual where 0 in (0);
>
> I'm using hardcoded values for an example, this is part of a far more
> complex SQL query.
>
> Thanks,
> Zzesers

Zzesers, I do not understand exactly what you mean as the in list does not return anything except true/false for the value being in the list.  If you mean that the value 'A' should be considered a match for 'A - D' then I would suggest that the in list query be written such that it returns a set of all valid values for the comparison. You do have the option of nesting decodes.

You should have the case statment available to you. If you want any of several values returned in the in list to be treated as if they are one value then here is a possibly way to code the in-list query:

 case when col = 'A' then 'B'
      when col = 'B' then 'B'
      when col = 'C' then 'B'
      when col = 'D' then 'D'
      else 'X'

  end

HTH

• Mark D Powell --

Received on Wed Jan 23 2002 - 08:07:42 CST