Re: SELECT with IF THEN ELSE

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 20 Dec 2004 10:25:51 -0800
Message-ID: <113567151.00002fc9.071@drn.newsguy.com>

In article <1103566487.843697.97680_at_c13g2000cwb.googlegroups.com>, WebTracer says...
>
>Thanks. Can't seem to get this to work:
>
>SELECT SELECT_FROM_PART, SUM(QTY_DUE), CASE SUBSTR(REQUEST_ID,1,1) WHEN
>!= 'I' THEN SUBSTR (REQUEST_ID,3,4) ELSE NULL END, NEED_DATE,
>REQUEST_ID
>FROM REQ1
>WHERE STATUS IN ('O','H')
>AND (SELECT_FROM_SC LIKE '1ASL%' OR SELECT_FROM_SC = 'LASR')
>GROUP BY SELECT_FROM_PART, SUBSTR (REQUEST_ID,3,4), NEED_DATE,
>REQUEST_ID
>ORDER BY SELECT_FROM_PART
>
>Doesn't like != 'I' ... why is that?
>

case when substr(request_id,1,1) <> 'I'

     then substr(request_id,3,4)
     else null

 end

or

decode(substr(request_id,1,1),'I',null,substr(request_id,3,4))

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Mon Dec 20 2004 - 12:25:51 CST