case when statements in sap hana

Question

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

It's a bit hard to understand the requirement here. Can you share some sample data and the result you're trying to get for it? — Mureinik, Jun 20 '17 at 06:41
hi so basically what i am trying to do is if the first case when is satisfied then only return that row if the first case when is not satisfied only then return the second case when — DazM, Jun 20 '17 at 23:42
bad syntax? `Case when...then ... when... then... else ... end`. Your first else should be a when I think. but also the case is being aliased `as "SELECT...." `that seems odd too... a select statemetn as the alias... strange. https://blogs.sap.com/2013/10/05/useful-sql-with-sap-hana/ shows multiple when, 1 else when you have more than 2 evaluations. — xQbert, Jun 27 '17 at 15:02

score 1 · Answer 1 · answered Jun 20 '17 at 06:53

1

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');

answered Jun 20 '17 at 06:53

S_sauden

302
2
10

hi @s_sauden thanks for your response, but your answer seems to be missing. the code is the same as mine – DazM Jun 21 '17 at 11:27
hi @DazM...yeah your and my code same but not exactly. I used when instead of else. You used two times else condition in your code i do not think it make sense. – S_sauden Jun 22 '17 at 10:13
This doens't appear to limit to just the 'Measured' records as desired in the expected results image. – xQbert Jun 27 '17 at 15:58

score 1 · Answer 2 · answered Jun 20 '17 at 07:06

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')

hi @irfan thanks for your response, but your answer seems to be missing. the code is the same as mine — DazM, Jun 21 '17 at 11:27

sveer · Answer 3 · 2017-06-27T15:04:44.463

    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!

xQbert · Answer 4 · 2017-06-27T15:57:42.950

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.

case when statements in sap hana

4 Answers4