Help on retrieving null values in SQL
by Peter Haig
(The Rhondda, South Wales ,UK)
,initcap(nvl(ltrim( persmain.FIRST_NAMES||' '||persmain.FAMILY_NAME) , 'Not Recorded')) NAME
,REPLACE(BOF_first_add(TO_NUMBER(( persmain.IDENTIFIER ))), 'County', NULL) ADDR
,initcap(nvl(ctitle.MEANING,'Not Recorded')) TITLE
,initcap(nvl(moretel.TELEPHONE_NUMBER,'Not Recorded')) MORETEL
,decode (oref.ertype_code,'HNO', REF.REFERENCE_ID, 'Null', 'Not Recorded' ) Hospital_No
( persmain.IDENTIFIER = '178998')
persmain.CTITLE_IDENTIFIER = title.IDENTIFIER(+) )
and (moretel.PERSON_IDENTIFIER(+) = persmain.IDENTIFIER)
and (Moretel.Primary_Contact_Indicator = 'Y')
and oref.person_identifier(+) = persmain.identifier
The above code returns data for all the select items and shows the expected value 'null' for Hospital_No. (Client has no other references recorded).
However if the client has other references recorded the result for Hospital_No shows all the other references expected but I want to show only the result that refers to a specific type of reference code in this case the code would be 'HNO'. I can achieve this by adding the line "And oref.ertype_code = 'HNO' as the last line of the SQL.
By adding this line the query returns nothing at all for clients with no 'other references' recorded and the Hospital_No for those with this reference number
I want to retrieve all the select statements for those who have a Hospital_No recorded and the same for those clients without a Hospital_No but showing 'Not Recorded' instead of the hospital number.