Re: Between function in Decode statement

DFN wrote:
> I stand corrected Mr. Morgan. Two of us from my team were trying
> unsuccessfully but our syntax was erroneous.
> Thank you for the clarification.
> JD
>
> SELECT "REQUEST_NO","REQUEST_LOG_DATE",
>
> round (sysdate - REQUEST_LOG_DATE) aging,
>
> CASE WHEN ((ROUND ( SYSDATE - REQUEST_LOG_DATE )) BETWEEN 0 AND 30) THEN
> NULL
>
> WHEN ((ROUND ( SYSDATE - REQUEST_LOG_DATE )) BETWEEN 31 AND 60) THEN 30
>
> WHEN ((ROUND ( SYSDATE - REQUEST_LOG_DATE )) BETWEEN 61 AND 90) THEN 60
>
> WHEN ((ROUND ( SYSDATE - REQUEST_LOG_DATE )) BETWEEN 91 AND 120) THEN 90
>
> ELSE 120
>
> END
>
> AGING_DECODED
>
> from ART_REQUEST A
>
> where A.REQUEST_STATUS = 'H' OR A.REQUEST_STATUS = 'P'
>
> And A.REQUEST_NO NOT IN (SELECT B.REQUEST_NO FROM ART_REQUEST_LETTER B)

Rewrite this as

CASE
  WHEN ((ROUND ( SYSDATE - REQUEST_LOG_DATE )) < 31) THEN

  WHEN ((ROUND ( SYSDATE - REQUEST_LOG_DATE )) < 61) THEN

  WHEN ((ROUND ( SYSDATE - REQUEST_LOG_DATE )) < 91) THEN

  WHEN ((ROUND ( SYSDATE - REQUEST_LOG_DATE )) < 121) THEN

It evaluates the first condition, then the second then the third sequentially.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)