CODEWITHSUNDEEP
sqlsql-server
Roho
Roho

Reputation: 101

Case statement for multiple column

I have a table "financials" like:

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

Im creating a new view "actual" derived from previous table which should look like :

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

I have written the below code:

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

But getting the error below:

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

Any suggestion or help please.

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

A case expression returns a single value ("scalar value" in SQL-speak). So, you need to repeat the case for each column:

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

Upvotes: 1

Related Questions