CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11gplsqldeveloper
Jaanna
Jaanna

Reputation: 1670

returning 0 as value for non-existing row

I have two tables.

table 1:

-------------------------------
| product_id | product number |
-------------------------------
|    1001    |   67E432D      |
|    1002    |   888CDE32     | 
|    1003    |   54D32EC2     |
-------------------------------

table 2:

--------------------------
| product_id |   desc    |
--------------------------
|  1001      | product 1 |
|  1003      | peoduct 3 |
--------------------------

After joining table 1 and table 2 I get the following result.

-------------------------------------------
| product_id | product number |   desc    |
-------------------------------------------
|    1001    |   67E432D      | product 1 |
|    1002    |   888CDE32     |           |
|    1003    |   54D32EC2     | product 3 |
-------------------------------------------

Now as you can see that the 'desc' column of product 3 is empty now. How may I have a 0 in that column? Something like:

-------------------------------------------
| product_id | product number |   desc    |
-------------------------------------------
|    1001    |   67E432D      | product 1 |
|    1002    |   888CDE32     |     0     |
|    1003    |   54D32EC2     | product 3 |
-------------------------------------------

There might be some function which can do the job but I guess I am not aware of it..yet.

Upvotes: 0

Views: 88

Answers (2)

Beryllium
Beryllium

Reputation: 12998

As this question is tagged with Oracle, Justin's answer is fine.

If you care about portability, consider using coalesce: Based on Justin's answer:

SELECT product_id, product_number, coalesce( desc, '0' )
  FROM table_1
       LEFT OUTER JOIN table_2 USING (product_id)
ORDER BY product_id

As for the difference, look here

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

It sounds like you just want to use NVL

SELECT product_id, product_number, NVL( desc, '0' )
  FROM table_1
       LEFT OUTER JOIN table_2 USING (product_id)
ORDER BY product_id

SQLFiddle Demo

Upvotes: 4

Related Questions