CODEWITHSUNDEEP
sqloracle-databaseselectrownum
user11147380
user11147380

Reputation:

Use rownum to always get non null value

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.

Upvotes: 0

Views: 508

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18705

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

Upvotes: 1

Related Questions