CODEWITHSUNDEEP
mysqlexcelif-statementnested
sqlnewb
sqlnewb

Reputation: 121

Nested IF statement in SQL

I am trying to recreate the following nested IF statement that I am using in excel within a SQL query:

=IF(AE20="",0,IF(AH20="",IF(AG20="",IF(AF20="",IF(AE20="","",AE20),AF20),AG20),AH20))

I did some research, and it appears that I need to use CASE, but in the examples I've found I'm still not able to connect the dots on the proper syntax to use given the example above.

I assumed it would start as

Select Case When ex_table.AE = ""

and I'm not sure where to go from there. I appreciate your help!

EDIT

mysql table is called best_estimate.

i have the following fields: JCE_TOTAL, CDSI_TOTAL, INITIAL_TOTAL and FINAL_TOTAL.

I want my SQL Query to go through those columns and pick the most recent value available (the values become available over time, in the order listed above).

If there is a value in FINAL_TOTAL it should pick the value in FINAL_TOTAL.

If FINAL_TOTAL is blank but there is a value in INITIAL_TOTAL, it should select that value.

If those two fields are both blank, but CDSI_TOTAL has a value, it should pick CDSI_TOTAL, etc.

If all the fields are blank, the result should be 0

Upvotes: 0

Views: 120

Answers (1)

Alex
Alex

Reputation: 17289

If I got your goal correctly you don't need to do this mega nested CASE statment. There is much simplier way:

SELECT COALESCE(AE, AH, AG, ...) AS my_value
FROM my_table

so according to yuor comment your query could be like:

SELECT COALESCE(FINAL_TOTAL, INITIAL_TOTAL, CDSI_TOTAL, JCE_TOTAL,  0 ) AS my_value
FROM best_estimate

EDIT

SELECT 
   OFS_ID,
   COALESCE(FINAL_TOTAL, INITIAL_TOTAL, CDSI_TOTAL, JCE_TOTAL,  0 ) AS my_value
FROM best_estimate

Upvotes: 1

Related Questions