asked on

SELECT CASE Statements and Aliasing - AS400 SQL

Hi,

I am attempting to return a result set from an AS400 where I need to apply a CASE statement within the SELECT statement.
Syntactically this is not an issue - but alas when it comes to giving the data column a nicer name than the default 'CASE' it doesn't work.

The SQL in simple terms:
SELECT Field1,
CASE Field2 WHEN 0 THEN 'No' else 'Yes' END CASE
FROM Table1

Result Set:
Field1 CASE
-----------------
1 No
2 No
3 Yes

but adding an alias tag results in a 'keyword AS not expected' error:
SELECT Field1,
CASE Field2 WHEN 0 THEN 'No' else 'Yes' END CASE AS NowCased
FROM Table1

Aliasing works fine for aggregated columns or simple column selections:
SELECT Field1 AS New1
CASE Field2 WHEN 0 THEN 'No' else 'Yes' END CASE
FROM Table1

New1 CASE
-----------------
1 No
2 No
3 Yes

farsight

I don't know the AS400 SQL dialect, but here's a couple of rational guesses given my experience with other products. Assuming that it's easy to test, give these a quick try.

SELECT Field1,
(CASE Field2 WHEN 0 THEN 'No' else 'Yes' END CASE) AS NowCased
FROM Table1

or (a long shot):

SELECT Field1,
CASE Field2 AS NowCased WHEN 0 THEN 'No' else 'Yes' END CASE
FROM Table1

Edwin1608

ASKER

Thank you for this - sorry, I should have mentioned that I've tried all the obvious combinations before getting desperate enough to post a question on ExpertExchange! :o?

Further info/discussion:
It doesn't want to behave anything like SQLServer, which I know well, but as the SELECT CASE field is then labelled as 'CASE' there must be way - as isn't this what alaising is meant for??!! My actual query requires 3 SELECT CASE staetments - and so attempting to reference one of 3 fields named 'CASE' via ADO is frustrating... and I don't want to reference the field by Index but by name as I also display the column name to my users.

Unfortunately the SQL used is being dynamically generated, so I am not in a position to SELECT CASE into a temp table and then returnign the result set to the user as it must be part of a single SELECT statement.

Regards,

Edwin1608

ASKER

Resolution:
An error in my original syntax is the problem. To end a CASE statement in AS400 SQL is simply END - not END CASE as I had written.
The alias is then simply the trailing string, with AS as optional.
Thus:

SELECT Field1,
CASE Field2 WHEN 0 THEN 'No' else 'Yes' END AS NewField2
FROM Table1

and

SELECT Field1,
CASE Field2 WHEN 0 THEN 'No' else 'Yes' END NewField2
FROM Table1

are both valid, and return the result set:
Field1 NewField2
-----------------
1 No
2 No
3 Yes

Regards, Edwin1608

Edwin1608

ASKER

Closed

farsight

Edwin1608, you can get your points refunded, since you answered your own question, by posting a request at https://www.experts-exchange.com/Community_Support/ (I think).

Thanks for posting the answer.

I looked for AS400 SQL syntax on the web but didn't find anything helpful. I did find the syntax for the CASE statement itself, but I, too, ignored the end of it. It's so easy to do.

Edwin1608

ASKER

Thanks for the advise on getting a refund - you are correct.

Thanks for participating.

Regards,

Edwin 1608

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial