Home » SQL & PL/SQL » SQL & PL/SQL » Help with CASE statement
Help with CASE statement [message #190822] Fri, 01 September 2006 07:33 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I want to print 'Unknown' when res2.max_end_ddt is NULL and I'm still getting a NULL (nothing) result. What is wrong with my CASE statement?

Thanks,
Stan
SELECT DISTINCT
   res2.Patient_Name,
   res2.patient_id,
   res2.dept_id,
   res2.Room_Bed,
Last_Diet_Date*/
   CASE res2.max_end_ddt
      WHEN NULL THEN 'Unknown'
      ELSE TO_CHAR(res2.max_end_ddt)
   END AS Last_Diet_Date
FROM
(SELECT
   patient.last_name || ', ' || patient.middle_name || ' ' || patient.first_name Patient_Name,
   patient.patient_id,
   patient.dept_id,
   patient.room_id || ' - ' || patient.bed_id Room_Bed,
   o_pat.end_ddt,
   MAX(o_pat.end_ddt)
   OVER
   (PARTITION BY
      patient.patient_id) max_end_ddt
FROM
   patient,
   o_pat
WHERE
   patient.pat_seq = o_pat.pat_seq AND
   patient.room_id IS NOT NULL AND
   patient.discharge_dt IS NULL AND
   patient.facility_id IN ('P') AND
   o_pat.frequency_id = 'MEALS' AND
   patient.patient_id NOT IN
  (SELECT
    res1.patient_id
   FROM
    (SELECT /*+ ORDERED */ DISTINCT
      patient.last_name|| ',' || patient.first_name|| ' ' || patient.middle_name Patient_name,
      patient.patient_id,
      patient.dept_id,
      patient.room_id,
      patient.bed_id,
      o_item.order_name,
    CASE patient.facility_id
      WHEN 'B' THEN 'MMC'
      WHEN 'P' THEN 'PWMC'
      WHEN 'F' THEN 'FSRMC'
      WHEN 'S' THEN 'FSS'
      WHEN 'L' THEN 'FLMC'
    END AS fsn
    FROM
      patient,
      o_pat,
      o_item
    WHERE
      patient.pat_seq = o_pat.pat_seq(+) AND
      o_pat.order_item_seq = o_item.order_item_seq AND
      o_pat.status IN ('A','F') AND
      o_item.perf_dept_id IN ('NOU','DTP','NOP','DTB','NOB','DTF','NOF','DTS','NOS','DTL','NOL') AND
      patient.facility_id IN ('P') AND
      ((DDT.TODATE(o_pat.dc_ddt) IS NULL) OR
        (DDT.TODATE(o_pat.dc_ddt) > sysdate)) AND
      patient.discharge_dt IS NULL AND
      DDT.TODATE(o_pat.start_ddt) < (SYSDATE + (((.01)*60)/1440)))res1))res2
WHERE
   res2.end_ddt = res2.max_end_ddt OR
   res2.max_end_ddt IS NULL
Re: Help with CASE statement [message #190829 is a reply to message #190822] Fri, 01 September 2006 08:19 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
use the NVL function
NVL(to_char(res2.max_end_ddt), 'Unknown')

Re: Help with CASE statement [message #190830 is a reply to message #190829] Fri, 01 September 2006 08:23 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

or

select case
when max_end_ddt is null then 'unknown'
else TO_CHAR(max_end_ddt)
end Last_Diet_Date

or

select decode(max_end_ddt,null,'unknown',TO_CHAR(max_end_ddt))
Re: Help with CASE statement [message #190831 is a reply to message #190822] Fri, 01 September 2006 08:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try
   CASE WHEN res2.max_end_ddt IS NULL THEN 'Unknown'
      ELSE TO_CHAR(res2.max_end_ddt,'dd-Mon-yyyy')
   END 

or, if you want to be lowtech about this
NVL(to_char(res2.max_end_ddt,'dd-Mon-yyyy'),'Unknown')

(I added in the Date format masks that you forgot Cool )

Beaten TWICE!!!
Curse my slow metal body....

[Updated on: Fri, 01 September 2006 08:43]

Report message to a moderator

Re: Help with CASE statement [message #190833 is a reply to message #190831] Fri, 01 September 2006 08:39 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
LOL, Cheers JRowbottom
Re: Help with CASE statement [message #190841 is a reply to message #190822] Fri, 01 September 2006 09:15 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks guys for all of the suggestions.

Have a great weekend,
Stan
Previous Topic: DBMS_CRYPTO for BLOB Encryption using VB6
Next Topic: replacing certain characters in string
Goto Forum:
  


Current Time: Mon Dec 05 23:48:55 CST 2016

Total time taken to generate the page: 0.04999 seconds