多列Case语句

Question

我有一个表格"财务"如:

CODE  NAME  ACTUAL
26    xxx   7654
54    yyy   8543
3     zzz   1873
7     aaa   6543
16    bbb   0321

我正在创建一个新视图"actual"从上一个表中派生出来的，应该是这样的:

invoice_sales    no_of_customer    currency    source_orig          name
7654                0                 USD        MSMDS_FINANCIALS   xxx
8543                0                 USD        MSMDS_FINANCIALS   yyy
0                 1873                USD        MSMDS_FINANCIALS   zzz
0                 6543                USD        MSMDS_FINANCIALS   aaa
0321                0                 USD        MSMDS_FINANCIALS   bbb

我写了下面的代码:

CREATE OR REPLACE TEMPORARY VIEW actual
AS
SELECT
CASE WHEN Code IN ('16','26','54') THEN actual AS invoice_sales and 0 AS no_of_customer 
ELSE WHEN Code IN ('3','7') THEN 0 AS invoice_sales and actual AS no_of_customer END
,'USD' AS currency
,'MSMDS_FINANCIALS' AS source_orig
,name
FROM
financials

但是在下面得到错误:

Error in SQL statement: ParseException: 
mismatched input 'Code' expecting {<EOF>, ';'}
== SQL ==
CREATE OR REPLACE TEMPORARY VIEW actual
AS
SELECT
CASE WHEN Code IN ('16','26','54') THEN actual AS invoice_sales and 0 AS no_of_customer 
ELSE WHEN Code IN ('3','7') THEN 0 AS invoice_sales and actual AS no_of_customer END
-----------^^^
,'USD' AS currency
,'MSMDS_FINANCIALS' AS source_orig
,name
FROM
financials

有任何建议或帮助，请。

Answer 1

case表达式返回一个单个值('标量值';在SQL-speak)。因此，您需要为每一列重复case:

SELECT (CASE WHEN Code IN ('16', '26', '54') THEN actual 
WHEN Code IN ('3', '7') THEN 0
END) AS invoice_sales 
(CASE WHEN Code IN ('16', '26', '54') THEN 0 
WHEN Code IN ('3', '7') THEN actual
END) AS no_of_customer, 
'USD' AS currency
'MSMDS_FINANCIALS' AS source_orig,
name
FROM financials