assistance with an SQL Query - Help on retrieving null values

SELECT Distinct
persmain.IDENTIFIER ClientNo,
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,
persmain.DATE_OF_BIRTH DOB,
initcap(nvl(moretel.TELEPHONE_NUMBER,'Not Recorded')) MORETEL,
moretel.PRIMARY_CONTACT_INDICATOR PCI,
decode (oref.ertype_code,'HNO', OREF.REFERENCE_ID, 'Null', 'Not Recorded' ) Hospital_No
FROM
PERSONS persmain,
CODED_TITLES ctitle,
TELEPHONES moretel,
OTHER_REFERENCES oref
WHERE
( persmain.IDENTIFIER = '178998')
AND (persmain.CTITLE_IDENTIFIER=ctitle.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.

Answer:

Not knowing the table structures we can only comment in general terms. Dissecting the query however we can see that you're doing SQL outer joins from the PERSONS table to CODED_TITLES, TELEPHONES and OTHER_REFERENCES tables. We assume from the way you've written your SQL query that the PERSONS table will always have values for the foreign keys to the other tables. If not you would need to reverse the outer joins.

We can demonstrate this quite easily. Let's create a couple of tables emp and dept as follows:

CREATE TABLE emp (emp_id NUMBER, emp_name VARCHAR2(200), dept_id NUMBER);

CREATE TABLE dept (dept_id NUMBER,
                   dept_name VARCHAR2(30));

and populate them like so:-

insert into emp values(1,'Mike Smith',1);
insert into emp values(2,'Bill Jones',2);

insert into dept values (1,'IT');
insert into dept values (3,'HR');

Notice that there's no department 2 in the dept table so if we want any data to be returned from a query joining the 2 tables we need an outer join. If it's emp that's missing the record then the SQL would look like this:

>select emp_id,emp_name,dept_name from emp,dept
where emp.dept_id(+)=dept.dept_id

which would produce the following results:

emp_id  emp_name       dept_name
------  -----------    ----------
1       Mike Smith     IT
                       HR

Notice that there's no values for emp_id or emp_name for the 2nd record. This is because there are no rows in the emp table with dept_id equal to 3.

If we reverse the outer join in our SQL query like so:

select emp_id, emp_name,dept_name from emp,dept
where emp.dept_id=dept.dept_id(+);

we get the following results:

emp_id  emp_name       dept_name
------  -----------    ----------
1      Mike Smith     IT
2      Bill Jones    

This time we get all the records from emp and if there's a match on dept_id we get dept_name as well.

To get back to the question, though, we need to know how to deal with missing values. In Oracle these are known as NULL which means they are not known/undefined/don't exist. The mistake made by Peter though is in treating NULL as if it has a value of 'Null'. This is a common mistake and very easy to do. NULL is a reserved word in Oracle and the query optimizer recognizes it so don't put in quotes.

Let's demonstrate. The following query changes the value of dept_name from 'IT' to 'geeks' and if not defined (NULL) to 'Unknown'.

select emp_id, emp_name, decode(dept_name, 'IT', 'geeks', null, 'Unknown') from emp,dept
where emp.dept_id=dept.dept_id(+)

and produces these results:

emp_id  emp_name       dept_name
------  -----------    ----------
1       Mike Smith     geeks
2       Bill Jones    Unknown

In summary, therefore, Peter's SQL query needs to use the reserved word NULL rather than the string 'Null' in the decode clause and may need to reverse the outer join.