Conditional SELECT of a column

Question

Heyall!

Maybe you can help me with a SQL Query:

I have a conversion value in a secondary table and the following structure:

ID PRICE_BRL PRICE_USD
-- --------- ---------
1  10        5
2  12        NULL
3  NULL      3
4  14        NULL
5  NULL      4
6  NULL      NULL

I need a Result Set Like that prioritizes the first column, in case of NULL, gives me the second column value multiplied by the conversion value stored in the secondary table. Something like, in pseudo-code:

SELECT 
  id, 
  (
   IF (price_brl != null) 
     price_brl
   ELSE 
     price_usd * tbl_2.value
  ) as final_price
FROM tbl_1

I think it must be simple using Joins, but I can't figure it out!

Thanks in advance.

Answer 1

Pseudo code as well:

select id, coalesce(price_brl, price_usd * tbl_2.value) 
from tbl_1
inner join tbl2

Answer 2

select  id, isnull( price_brl, price_usd * tbl_2.value)
from    tbl_1
    inner join tbl_2
     on tbl_1.id=tbl_2.id

Obviously, you'll need to adjust the join. But I think this will do the trick.

Answer 3

the coalesce() statement does exactly what you are wanting to do in your example, it tests for null in the field and then provides a different value if it is null. But if you wanted to do something besides test for null (and it will work for nulls as well) you can use a case statement:

SELECT id, CASE WHEN price_brl != NULL THEN price_brl ELSE price_usd * tbl_2.value END as final price...

Answer 4

Assuming you're using SQL Server, or some other system that supports user-defined functions I would consider creating a function that encapsulated the logic so my select would be

SELECT udf_getPrice(id) from tbl1

Although this would impose some overhead it would seem likely to me from your question you are not in a position where performance is critical for the query. The advantage of using a function is that the type of query you are using is one where business changes are likely to occur - for instance you might start sourcing in Euros or Yen in addition to Dollars, or the rules used for calculation may change. Encapsulating the conversion logic in a function will add a layer of abstraction that could pay off handsomely for future maintenance.

Answer 5

Another way would be:

SELECT id, 
CASE WHEN price_brl IS NULL THEN (price_usd * tbl_2.Value)
ELSE 
price_brl
END AS Final_Price
FROM tbl_1
JOIN tbl_2 ON /*Join Conditions and then Where conditions*/

But I'd recommend either the Coallesce option above (if you have 3 or more options), or IsNull (since it looks like you have 2 choices).