Re: Oracle 8i SQL Statement

From: Onismus Radebe <onismusrATabsaDOTcoDOTza_at_za>
Date: Fri, 19 Sep 2003 15:04:45 +0200
Message-ID: <3f6afed4$0$64724@hades.is.co.za>

"Kent Prokopy" <kent_prokopy_at_stream.com> wrote in message news:ec8c1578.0309190353.28346e7d_at_posting.google.com...
> When using the DECODE function, is there any way of passing it the
> IN() clause?
>
> Example:
> DECODE(FIELDNAME,IN(1,2,3),TRUE,FALSE)
>
> This returnning TRUE if the values of 1, 2 or 3 are found. So far the
> only way I can get this to work is as follows:
>
> DECODE(FIELDNAME,1,TRUE,2,TRUE,3,TRUE,FALSE) "This leaving a lot of
> room for typo errors."
>
> Thank you.
> Kent Prokopy

Try it and see what happens.
NOTE that you can only use boolean values in PL/SQL not SQL if recall.

SQL> select decode(1, in (1,2,3), 'FALSE', 'TRUE')   2 from dual
  3 /
select decode(1, in (1,2,3), 'FALSE', 'TRUE')

                 *

ERROR at line 1:
ORA-00936: missing expression

Elapsed: 00:00:00.00
SQL> As you can see that it doesn't work. Use this instead :

SQL> select (case when 1 in (1,2,3) then

  2             'TRUE'
  3          else
  4             'FALSE'
  5          end ) as results

  6 from dual
  7 /

RESUL

---

TRUE Elapsed: 00:00:00.01
SQL> 1
  1* select (case when 1 in (1,2,3) then SQL> c/1/4
  1* select (case when 4 in (1,2,3) then SQL> run
  1 select (case when 4 in (1,2,3) then

  2             'TRUE'
  3          else
  4             'FALSE'
  5          end ) as results

  6* from dual

RESUL

---

FALSE Elapsed: 00:00:00.01
SQL> Onismus

--
                                       \|/
                                       o o
 __________________________________oOO_(_)_OOo______________________________

 E-Business and Information Management   |   God said ...
 Knowledge Discovery & Dissemination     |            1. div D = p
 onismusr-AT-absa-DOT-co-DOT-za          |            2. curl E = -dB/dt
 Tel: +27 11 350 3414                    |            3. div B = 0
 Fax: +27 11 350 8585                    |            4. curl H = dD/dt + J
 Cel: +27 83 591 5310                    |   ... and there was light.
 ___________________________________________________________________________

Received on Fri Sep 19 2003 - 08:04:45 CDT