Okay I'm using PeopleSoft's Query Manager trying to write an expression. The original expression converted a date format into DD MON YY, here's the original expression that works:
CONVERT(VARCHAR(10), A.TERMINATION_DT, 103) AS [DD MON YY]
I need to add termination dates for a few people, manually (they're not termed yet, but will be, so I have to input it like this).
So I thought I could do a case statement:
(CASE WHEN B.EMPLID IN ('XXXXXX') THEN '2022-08-19' ELSE (CONVERT(VARCHAR(10), A.TERMINATION_DT, 103) AS [DD MON YY]) END)
When I input the above I get the following error:
A SQL error occurred. Please consult your system log for details. Error in running query because of SQL Error, Code=8603, Message=[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'AS'. [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'PS_JOB'. [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax nea란ᆌŪ (50,380)
Where am I going wrong?
The problem with your edit of the query is that you put AS [DD MON YY] statement, which sets the alias for the column, into the CASE statement.
It should be after the entire CASE statement.
Provided that the rest of the syntax is correct, it should be:
(CASE
WHEN B.EMPLID IN ('XXXXXX') THEN '2022-08-19'
ELSE CONVERT(VARCHAR(10), A.TERMINATION_DT, 103)
END) AS [DD MON YY]