postgresql 如何在PSQL中正确使用条件?

tvokkenx  于 2023-02-18  发布在  PostgreSQL
关注(0)|答案(2)|浏览(134)

我想做一个条件,如果看到FLAG_CLOSED = Y,则将值(000000)放入DATE_COMPLETED,如果不是Y,则不执行任何操作

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"

我得到的

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
kjthegm6

kjthegm61#

您似乎试图更改表中的DATE_COMPLETED列。不能使用SELECT语句执行此操作。CASE / WHEN / THEN有助于构造输出。UPDATE语句允许使用DATE_COMPLETED='000000'等子句更改列。
试试这个。

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"

我将大小写计算的输出列命名为CLOSED_DATE_COMPLETED,这样它就不会与前面提到的DATE_COMPLETED列冲突。

wgx48brx

wgx48brx2#

语法为:

CASE a WHEN b

...或:

CASE when a=b

要根据标志返回DATE_COMPLETED的值,可以执行以下操作:

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

请注意,您需要生成一个一致的列类型。如果DATE_COMPLETED不是文本,则需要对其进行强制转换。

相关问题