Question 89739 in PostgreSQL Question posted in
The official documentation can be found here.

How do I use conditions properly in PSQL? – Postgresql

I wanted to do a condition wherein I put values (000000) in DATE_COMPLETED if it see’s the FLAG_CLOSED = Y and if its not Y then do nothing

SELECT 
 "JOB",
 "SUFFIX", 
 "SUFFIX", 
 "DATE_COMPLETED", 
 "FLAG_CLOSED",

CASE "DATE_COMPLETED" 
WHEN "FLAG_CLOSED"='Y' 
THEN "DATE_COMPLETED"='000000' 
END "DATE_COMPLETED"

FROM "JOB_OPERATIONS"

What I got

SQL Execution Error
[LNA][PSQL][SQL Engine]Syntax Error: SELECT 
 "JOB",
 "SUFFIX", 
 "SUFFIX", 
 "DATE_COMPLETED", 
 "FLAG_CLOSED",

CASE "DATE_COMPLETED" WHEN "FLAG_CLOSED" << ??? >> = 'Y' THEN "DATE_COMPLETED" = '000000' END "DATE_COMPLETED"
FROM JOB_OPERATIONS
Tags:

2

Answers


  1. 0

    It looks like you’re attempting to change the DATE_COMPLETED column in your table. You can’t do that with a SELECT statement. CASE / WHEN / THEN helps construct output. UPDATE statements allow clauses like DATE_COMPLETED='000000' that change columns.

    Try something like this.

    SELECT  "JOB",  "SUFFIX", "SUFFIX", "DATE_COMPLETED", "FLAG_CLOSED",
        CASE WHEN "FLAG_CLOSED"='Y' THEN '000000' 
             ELSE "DATE_COMPLETED" END "CLOSED_DATE_COMPLETED" 
FROM "JOB_OPERATIONS"

    I named your CASE-computed output column CLOSED_DATE_COMPLETED so it won’t collide with the DATE_COMPLETED colum you already mentioned.

    Login or Signup to reply.
  2. 0

    Syntax is either:

    CASE a WHEN b

    … or:

    CASE when a=b

    To return the value of DATE_COMPLETED depending on the flag, you can do this:

    CASE "FLAG_CLOSED"
    WHEN 'Y' THEN '000000'
    ELSE "DATE_COMPLETED"
END AS "DATE_COMPLETED"

    Beware that you need to produce a coherent column type. If DATE_COMPLETED is not text, you’ll need to cast it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search