SELECT name, CASE WHEN type = 1 THEN 'A' WHEN type = 2 THEN 'B' ELSE 'C' END AS typeflag FROM custtable WHERE typeflag = 'B'
I know this can be done by doing "where type = 2" but I can't give you an easier example. When I try this, SQL says "typeflag is not a column in the table."
To do this on a single statement, you can use an SQL common table expression. I find table expressions helpful in breaking a complex SQL statement into smaller, simpler components. Here's an example of implementing your statement with a common table expression:
WITH custexp AS (SELECT name, CASE WHEN type = 1 THEN 'A' WHEN type = 2 THEN 'B' ELSE 'C' END AS typeflag FROM custtable) SELECT name, typeflag FROM custexp WHERE typeflag='B'
Dig deeper on iSeries SQL commands and statements
Related Q&A from Kent Milligan
To monitor members stuck within a physical file on AS/400, you can periodically use the display file description (DSPFD) command to create an output ...continue reading
Create a host variable of the where in statement on the fly with dynamic SQL.continue reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.