1

I have no idea why my coalesce function isn't working.

I have two tables that I'm drawing from.

Table 1 and Table 2 Table 1 price is 0. Table 2 price is 0.33. If I coalesce I should get the 33 cents.

I do a FULL OUTER JOIN on both tables. I run the following query with the coalesce in the select statement.

SELECT 
p.item_id, 
p.date, 
B1.price as backup_1_price,
B2.price as backup_2_price,
      , COALESCE(B1.backup_1_price, 
                B2.backup_2_price)          as coalesce_price         

FROM 
primary_prices AS p 
    FULL OUTER JOIN backup_prices_1 AS b1
      ON p.item_id = b1.item_id
    FULL OUTER JOIN backup_prices AS b2
      ON p.item_id = b2.item_id
    
WHERE 
    1=1 
    AND p.item_id = '1'

Result when I coalesce:

item_id date backup_1_price backup_2_price coalesce_price
1 2022-02-28 0 0.33 0

I'm not sure why my coalesce isn't working here? Thanks so much in a advance!

Maggie Liu
  • 344
  • 1
  • 3
  • 15
  • 1
    COALESCE returns first non-NULL. 0 is not NULL. – alexherm Apr 15 '22 at 20:54
  • You could use `case when p1 = 0 then p2 else p1 end as "price"` –  Apr 15 '22 at 21:00
  • On a side note: You may think you are doing full outer joins here, but it's merely left outer joins, and you should change this in your query in order not to confuse the reader. You select primary_prices with item_id 1 and to these rows you left outer join the two backup prices. – Thorsten Kettner Apr 15 '22 at 21:42
  • Why do you want the price of 0.33 have precedence over the price of 0? Do you always want the higher price? Or is 0 supposed to mean no price stored? In that case you should use NULL, not zero, and then your `COALESCE` would work. – Thorsten Kettner Apr 15 '22 at 21:44

0 Answers0