Re: Can you use "If..Then" logic in SQL?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 30 Apr 1999 00:54:17 GMT
Message-ID: <3729fe26.2114580@192.86.155.100>

A copy of this was sent to "Mitch" <mabaza25_at_usaDELETE.net> (if that email address didn't require changing) On Thu, 29 Apr 1999 17:49:54 -0500, you wrote:

>
>
>-- I am programming a VB app that pulls data from an Oracle 8 database into
>Access for further manipulation. I am trying to figure out if there is a
>way using either Oracle or Jet SQL to use "If..Then" logic in my SQL query.
>For example,
>
>"Select Table1.*, Table2.* where Table1.Key = Table2.Key (this is where I'm
>not sure about the syntax) and If Table2.Price and Table2.Date are NULL then
>Table1.Price, Table1.Date Else Table2.Price and Table2.Date"
>

decode is the answer.

decode(t2.price,

            NULL, decode(t2.date, 
                            NULL, t1.price, 
                            t2.price), 
            t2.price)

says  

if t2.price is NULL
then

    if t2.date is null
    then

        return t1.price
    else

        return t2.price
    end if
else

    return t2.price
end if;

decode(t2.price, NULL, decode(t2.date, NULL, t1.date, t2.date ), t2.date )

>I am sure I could just pull all records from the Oracle database into Access
>and then use DAO methods to create another recordset that meets the above
>criteria, but that's bound to be very slow. I'd like to use strictly SQL if
>possible. Any and all suggestions (Oracle, Access or VB) are appreciated.
>
>Thanks
>
>Mitch Abaza
>mabaza25_at_DELETEusa.net
>
>clear "delete" to mail
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Apr 29 1999 - 19:54:17 CDT