Use rownum to always get non null value

Question

select case when (CUST.ADDRESS_TYPE='OFFICE') then 
                 (Select MOBILE 
                  FROM cust_table CUST 
                  where CID = Deal.CID 
                  and ADDRESS_TYPE = 'CURRES' 
                  and rownum = 1)
       else 
                  CUST.MOBILE 
       end as MOBILE 
FROM cust_table CUST 
RIGHT OUTER JOIN (SELECT CID CID 
                         , WNAME
                         , APPLICANT_TYPE 
                  FROM deal_table ) DEAL 
      ON DEAL.CID = CUST.CID 
      AND APPLICANT_TYPE = 'P' 
      and mailing_add = 'true' 
WHERE WNAME='22135' 
and rownum = 1 

#MOBILE#

1. NULL
2. 647432923

OR

#MOBILE#

1. 74238423
2. NULL

This query returns a column named 'MOBILE ' with two rows, one of the entries being always null when I dont use rownum = 1 at the end, but if I put rownum = 1 towards the end then in some cases it returns null value and in some cases non null value. How can I use rownum so that the query always returns non null value.

Answer 1

Do not use rownum for this. rownum will give you the number of the row after your query has been run. Use a properly defined ORDER BY clause to get your NULLS at the end and only fetch first row.

<your query>
WHERE wname='22135' 
ORDER BY mobile NULLS LAST
FETCH FIRST 1 ROWS ONLY