0

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?

Phlegon_of_Tralles
  • 181
  • 1
  • 14

2 Answers2

2

Okay I figured it out. Not terribly difficult in the end.

(CASE WHEN B.EMPLID IN ('XXXXXX') THEN '2022-08-19' ELSE (CONVERT(VARCHAR(10), A.TERMINATION_DT, 103) ) END)

Phlegon_of_Tralles
  • 181
  • 1
  • 14
  • 1
    It sounds like you found a solution to your question. If so, please mark question as answered so it no longer appears as unanswered in the queue. – qyb2zm302 Aug 24 '22 at 21:29
  • 1
    You've removed the alias, this isn't really a solution as you changed functionality. – Based Aug 25 '22 at 11:40
  • Based - are you talking about the XXXXX? That was an employee ID, I didn't want to post that on a public forum. – Phlegon_of_Tralles Aug 25 '22 at 16:18
2

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]
Based
  • 950
  • 7
  • 18