I have a dataflow I've built in Magic ETL, and I want to add a new column using a conditional statement to return the value from one of 3 existing columns (doing this in Beast Mode prevents me from getting the correct aggregations, so I'm trying to add in the dataflow itself).
So I want to have Invoice Sales returned if >0, if not then return j_quote_ if >0, if not then return WP Price. The Magic ETL dataflow basically looks like this, and I want to add the column in bold:
Job No_1 | J_JOB_STATUS | Invoice Sales | WP Price | J_QUOTE_ | Total Job |
123456 | JOB INVOICED. INVOICE# 12345678 | 2992.5 | 3021.02 | 0 | 2992.5 |
123457 | JOB ENTERED | | 340 | 0 | 340 |
123458 | JOB CARD PRINTED | | 6196 | 6240 | 6240 |
This is the syntax I have in MySql, but I'm a newbie at SQL, and I'm sure I'm missing something simple. Any suggestions are welcome:
Thanks!