Re: if in a select

From: Wilfred van der Deijl <W.van.der.Deijl_at_inter.nl.net>
Date: 1996/02/20
Message-ID: <Dn3JLF.KuM_at_solair1.inter.NL.net>#1/1

vgwlu_at_chevron.com (greg w. luft) wrote:

>Is there any way to do an if statement list conditional within a select statement?
 

>Greg

No there is not.

BUT: You can use a decode to get something like IF var=value1 THEN
  result := res1;
ELSIF var=value2 THEN
  result := res2;
ELSE
  result := res3;
END IF; You can get this by:

SELECT DECODE(var, value1, res1
                 , value2, res2
                 ,         res3)

FROM DUAL; If you have a PL/SQL like this:
IF a < b THEN
  result := res1;
ELSE
  result := res2;
END IF; Than you might want to use this:
SELECT DECODE (SIGN(A-B), -1, res1

                        ,     res2)

FROM DUAL; Or if you want to make it a part of the WHEN-clause SELECT something
FROM sometable
WHERE SIGN(A-B) = -1; This is based on the following:

    A < B
--> A - B < 0
--> SIGN(A-B) = -1 The same is:

    A <= B

--> A - B <= 0
--> SIGN(A-B) = 0 or -1
--> SIGN(A-B)-1 = -1 or -2
--> SIGN(SIGN(A-B)-1) = -1   the value -1 will indicate that A<=B





>-- 

>Greg Luft                    Phone: (403) 234-5570

>Chevron Canada Resources     Email: gwlu_at_chevron.com

>500 5th Ave
>Calgary Alberta

Bye,
Wilfred
The Netherlands

---

E-mail : W.van.der.Deijl_at_inter.nl.net Homepage: http://www.inter.nl.net/users/W.van.der.Deijl

          (including the MicroProse Grand Prix II FAQ!) Received on Tue Feb 20 1996 - 00:00:00 CET