Reputation:
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
OR
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: 507
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