CODEWITHSUNDEEP
mysqlhanacase-when
DazM
DazM

Reputation: 1

case when statements in sap hana

I would like to know what can I use to be able to do:

If condition1 true then only display condition 1 value else go to condition 2 and display only condition 2 value.

I have tried to use case when but this seems to be executing both WHEN statements(and this is because both cases exist in the table but i want only the first when to return if both are true). Is there a way to do this?

SELECT 
RI.*,
CASE 
WHEN
  ("applianceId" 
   in 
   ('AlwaysOn','Heating','Cooling','Pool Pump','Hot Water') 
   and "type" in ('peer')) 
THEN 'Measured' 
ELSE 
   ("applianceId" 
   NOT in 
   ('AlwaysOn','Heating','Cooling','Pool Pump','Hot Water') 
   and "type" IN('peer')) 
THEN 'RELEVANCE' 
ELSE 'ERROR'
END AS "Select" FROM "SP_CUSTOMER"."RU_INSIGHT" RI
where RI."userId" in 
('2270BAF2D64948A7031F620645577C82B7529BB26DA46453B4595F0BE61460D6')
and "type" in ('peer')

current output that i am getting

desired output

Upvotes: 0

Views: 14287

Answers (4)

xQbert
xQbert

Reputation: 35333

Why do you need a case at all?

You're not executing two cases, you have at last two applianceID's per record in your userID table. A case statement in the select doesn't limit the data returned, it simply controls what is visible for the records displayed.

The reason you're getting multiple records is because you have multiple records in your table. This is evidenced by the fact that you have multiple records for a single userID in your current results. One for 'total' one for 'AlwaysOn' with multiple dates. Since 'Total' isn't in your first list, the "SELECT" value is getting set to 'Relevance' on the second record.

Since your Ri.userID table has the ID in question listed multiple times with different applianceID's and perhaps other values and based on your output I think you just want...

SELECT RI.*, 'Measured' AS "Select" 
FROM "SP_CUSTOMER"."RU_INSIGHT" RI
WHERE  RI."userId" in 
('2270BAF2D64948A7031F620645577C82B7529BB26DA46453B4595F0BE61460D6')
  and "type" in ('peer')
  and "applianceId" in ('AlwaysOn','Heating','Cooling','Pool Pump','Hot Water')

I don't agree with naming a column "Select" as this is a key/reserved word.

I don't see why you need a case statement at all... since error or 'Relevance' isn't in your desired output.

and not in is dangerous if a field can be null. A not in for a null value will return the record because when the system compares null to (the list) null isn't in the list therefor it gets returned. Null can't be compared using = not in, not exists. only a is not null or is null.

Upvotes: 0

sveer
sveer

Reputation: 472

    SELECT 
    RI.*,
    CASE WHEN ("applianceId" in ('AlwaysOn','Heating','Cooling','Pool Pump','Hot Water') and "type" in ('peer')) 
    THEN 'Measured' 
    WHEN ("applianceId" NOT in ('AlwaysOn','Heating','Cooling','Pool Pump','Hot Water') and "type" IN('peer')) 
    THEN 'RELEVANCE' 
    ELSE 'ERROR'
    END AS "Select" FROM "SP_CUSTOMER"."RU_INSIGHT" RI
    where RI."userId" in 
    ('2270BAF2D64948A7031F620645577C82B7529BB26DA46453B4595F0BE61460D6')
    and "type" in ('peer')

My post is same as the other posts, you are not observing the CASE, In the second condition the CASE after ELSE is the mistake. you are not supposed to use CASE immediately after ELSE either end the statement with END and start the second condition with CASE or write a condition with WHEN. In your case the later is relevant. I hope you understand!

Upvotes: 0

irfanengineer
irfanengineer

Reputation: 1300

Check this:

SELECT
RI.*,
CASE
WHEN
  ("applianceId"
   in
   ('AlwaysOn','Heating','Cooling','Pool Pump','Hot Water')
   and "type" in ('peer'))
THEN 'Measured'
WHEN 
   ("applianceId"
   NOT in
   ('AlwaysOn','Heating','Cooling','Pool Pump','Hot Water')
   and "type" IN('peer'))
THEN 'RELEVANCE'
ELSE 'ERROR'
END AS "Select" FROM "SP_CUSTOMER"."RU_INSIGHT" RI
where RI."userId" in
('2270BAF2D64948A7031F620645577C82B7529BB26DA46453B4595F0BE61460D6')
and "type" in ('peer')

Upvotes: 1

S_sauden
S_sauden

Reputation: 300

Try this:-

SELECT RI.*, 
  CASE WHEN ("applianceId" in ('AlwaysOn','Heating','Cooling','Pool Pump','Hot Water') and "type" in ('peer')) THEN 'Measured' 
  WHEN ("applianceId" NOT in ('AlwaysOn','Heating','Cooling','Pool Pump','Hot Water') and "type" IN('peer')) THEN 'RELEVANCE' 
  ELSE 'ERROR' END AS "Select" FROM "SP_CUSTOMER"."RU_INSIGHT" RI 
  where RI."userId" in ('2270BAF2D64948A7031F620645577C82B7529BB26DA46453B4595F0BE61460D6') and "type" in ('peer');

Upvotes: 1

Related Questions