user117911 user117911 - 2 months ago 15
SQL Question

SQL custom column based on other columns

How can I create custom column based on two other columns.
This is what I tried but I get an error.

SELECT data_tbl.start_date as [Date],
data_tbl.start_time as [Time],
data_tbl.job as [Job Number],
data_tbl.part as [Part Number],
workType as CASE
WHEN data_tbl.is_setup='Yes' THEN 'Setup'
WHEN data_tbl.is_production='Yes' THEN 'Production'
FROM data_tbl


'workType' is my custom column, I want to populate it with iether "setup" or "production" based on 'is_setup' and 'is_production' fields (both Yes/No fields).

I use MS-Access

Error message: The Select statement includes a reserved word or an argument name that is misspelled or missing, or punctuation is incorrect

Answer
SELECT data_tbl.start_date as [Date], 
       data_tbl.start_time as [Time], 
       data_tbl.job as [Job Number], 
       data_tbl.part as [Part Number],
       IIF(data_tbl.is_setup='Yes', 'Setup',
           IIF(data_tbl.is_production='Yes', 'Production', '')
           ) as workType

FROM data_tbl
Comments