Geek Answers Handbook

Conditional SELECT Column

Perhaps you can help me with the SQL query:

I have a conversion value in the 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 set of results. Like the priority of the first column, in the case of NULL, it gives me the second column value multiplied by the transform value stored in the secondary table. Something like, in the pseudocode:

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

I think it should be just using Joins, but I can't figure it out!

Thanks in advance.

+4
source share
5 answers

Pseudocode:

 select id, coalesce(price_brl, price_usd * tbl_2.value) from tbl_1 inner join tbl2
+9
source 
 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 will need to configure the connection. But I think this will do the trick.

+3
source

the coalesce () operator does exactly what you want to do in your example, it checks the null value in the field and then provides a different value if it is null. But if you want to do something other than a test for null (and it will work for zeros as well), you can use the case statement:

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

Another way:

 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 would recommend the Coallesce option above (if you have 3 or more options) or IsNull (since it looks like you have 2 options).

+1
source

Assuming you are using SQL Server or some other system that supports custom functions, I would consider creating a function that encapsulates the logic, so my choice would be

SELECT udf_getPrice (id) from tbl1

Although this would impose some overhead, it seems to me, in my opinion, that you are not in a position where performance is important to the query. The advantage of using the function is that the type of request you are using is the one where business changes can occur, for example, you can start searching in euros or yen in addition to dollars, or the rules used for calculation may change. Encapsulating the conversion logic into a function will add an abstraction layer that could pay beautifully for future maintenance.

0
source

More articles:


All Articles