CODEWITHSUNDEEP
sqloracle-database
jason
jason

Reputation: 3615

Selecting NVL With Joins

I am trying to write an SQL statement that will pull the value from one table and its corresponding value from another. This works fine unless one of the values that are being used in a join are null, then it returns nothing. I would like the script to return a value (like 'Nothing') in the event the join is null. Is this possible? I know I can use NVL in the select but what about the join? Here is my script:

 SELECT  
 i.equip_pk,
 i.request_pk, 
 MY_ALIAS.EQUIP_ALIAS_ID 
 FROM
 tableOne i
 JOIN table2 MY_ALIAS ON (i.EQUIP_PK = MY_ALIAS.EQUIP_PK)
 WHERE i.request_pk=:requestPk

I am using Oracle 10g but this script is not going to be used as PL/SQL.

Thanks for any help

jason

Upvotes: 2

Views: 11370

Answers (2)

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

You can use a LEFT JOIN. That will return all rows from tableOne, and when it can't find a match in the second table, it will return null. Then you can use NVL like you mentioned.

SELECT  
 i.equip_pk,
 i.request_pk, 
 NVL(MY_ALIAS.EQUIP_ALIAS_ID, 'Nothing here')
 FROM tableOne i
 LEFT OUTER JOIN table2 MY_ALIAS ON (i.EQUIP_PK = MY_ALIAS.EQUIP_PK)
 WHERE i.request_pk=:requestPk

If you're expecting nulls from equip_pk, you can apply NVL to that to. Even if it's null, the LEFT JOIN will still produce the proper result.

Upvotes: 3

realnumber3012
realnumber3012

Reputation: 1062

SELECT  
 NVL(i.equip_pk, 'Nothing'),
 NVL(i.request_pk, 'Nothing)', 
 MY_ALIAS.EQUIP_ALIAS_ID 
 FROM
 tableOne i
 FULL JOIN table2 MY_ALIAS ON (i.EQUIP_PK = MY_ALIAS.EQUIP_PK)
 WHERE i.request_pk=:requestPk

Upvotes: 0

Related Questions