Skip to Content
Author's profile photo Denis Sproten

Useful SQL in CASE WHEN you need it

Some of these formulas I just converted from post on the internet to SAP HANA SLQ syntax. Some of it I built myself, slowly getting better at it.

Something which may be used in different situations like “First day of the week” and this sort of SQL, I have one here for the previous Saturday:

SELECT ADD_DAYS(CURRENT_DATE, MOD((-(WEEKDAY(CURRENT_DATE)) – 2),7)) FROM Dummy;

SELECT T1.“DATE”, ADD_DAYS(T1.“DATE”, MOD((-(WEEKDAY(T1.“DATE”)) – 2),7)) FROM “_SYS_BI”.“M_FISCAL_CALENDAR” as T1;

MOD being Modulo and retrieving the remainder of the division by 7, some more information on Wikipedia:

http://en.wikipedia.org/wiki/Modulo_operation

Now to creating a filter in a Business Objects Universe (or SQL directly in HANA) which shows a usecase of a CASE statement:

“_SYS_BIC”.”mypackage/CV_FIN _ALL”.”DATE” >=

  (   CASE WHEN MONTH(CURRENT_DATE) >=6 AND MONTH(CURRENT_DATE) <= 11

THEN

                ( TO_DATE(CONCAT(TO_CHAR(CURRENT_DATE, ‘YYYY’),’-06-01′), ‘YYYY-MM-DD’)

                )

ELSE

                (TO_DATE(CONCAT(TO_CHAR(CURRENT_DATE, ‘YYYY’),’-12-01′), ‘YYYY-MM-DD’)

                )

END  )

AND

“_SYS_BIC”.”mypackage/CV_FIN_ALL”.”DATE” <= CURRENT_DATE

I had some confusion as to putting the CASE statement correctly together, as there are 2 types of CASE WHEN statements, one evaluates and expression and the one above evaluates a condition. The documentation wasn’t showing any examples and it took some testing to figure out that when testing for a condition, it will appear after the “WHEN”.

This being the “wrong” link which shows the syntax for the expression and not the condition check:

http://help.sap.com/hana/html/_fsql_expressions.html#sql_expressions_case_expressions

And this being the more complete documentation, where I used the <search_case_expression>:

https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a4389775191014b5a6bf2ccc0df2ed/content.htm

And just in case the link breaks I am copying the content in:

Case Expressions

A case expression allows the user to use IF – THEN – ELSE logic without using procedures in SQL statements.

Syntax

<case_expression> ::= <simple_case_expression> | <search_case_expression>

<simple_case_expression> ::=

            CASE <expression>

            WHEN <expression> THEN <expression>

            [{ WHEN <expression> THEN <expression>}…]

            [ ELSE <expression>]

            END

<search_case_expression> > ::=

            CASE

            WHEN <condition>THEN <expression>

            [{ WHEN <condition> THEN <expression>}…]

            [ ELSE <expression>]

            END

<condition> ::= <condition> OR <condition> | <condition> AND <condition>

              | NOT <condition> | ( <condition> ) | <predicate>

Happy SQL Days

Denis

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Good Information.

      Author's profile photo Andy Silvey
      Andy Silvey

      Hi Denis,

      very useful SQL.

      May I please add this is a section (credited to you) to the SAP Hana Reference For NetWeaver Basis Administrators ?

      Best regards,

      Andy.

      Author's profile photo Denis Sproten
      Denis Sproten
      Blog Post Author

      Hi Andy,

      Even it is useful, I am not sure this is relevant to Adminstration operations. I had a look at your blog and something which would be useful is a hint to setting a different account expiration to service accounts created, as the default 6month expiry disables them: DISABLE PASSWORD LIFETIME

      Kind Regards,

      Denis

      Author's profile photo Rajnish Tiwari
      Rajnish Tiwari

      Hi Denis,

      Can you explain with example for SWITCH case  ? like in JAVA we have switch case ?

      Thanks, rajnish

      Author's profile photo Denis Sproten
      Denis Sproten
      Blog Post Author

      Hi Rajnish,

      SWITCH doesn't exist and as an alternative you would need to use lots of IF THEN ELSE statements, which can be used in Stored Procedures not in SQL expressions.

      (as mentioned in my original post: A case expression allows the user to use IF - THEN - ELSE logic without using procedures in SQL statements.)

      For further help, have a look in the SQL guide:

      http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

      7.3.1 Conditionals

      This statement can be used to simulate the switch-case statement known from many programming languages.

      I hope this helps.

      Kind Regards,

      Denis

      Author's profile photo Rajnish Tiwari
      Rajnish Tiwari

      Hi Denis,

      Thanks Denis, I have followed the same approach used  IF-THEN-ELSE in stored procedure.

      Example

      IF :SAMPLE_COUNT = 0

      THEN INSERT INTO SOURCE_TABLE VALUES(5);


      ELSEIF :SAMPLE_COUNT = 1

      THEN INSERT INTO SOURCE_TABLE VALUES(6);

      END IF;

      In this way we can have multiple switch conditions. 

      -Rajnish

      Author's profile photo Former Member
      Former Member

      Might not be super obvious (or it might be), but thought this was worth sharing in this context.

      During an update with a case statement, if you don't define an ELSE condition - all records that don't match a case will have that column set to NULL.

      UPDATE <TABLE1>

      SET "COLUMN1" =

      CASE WHEN "COLOR" = 'B' THEN 'BLUE'

      WHEN "COLOR" = 'R' THEN 'RED'

      END;

      If you had a record with "COLOR" = 'Y', then "COLUMN1" would be set to NULL. Sometimes it may or may not be a desired effect, but just good to know.

      Regards,

      Justin

      Author's profile photo Rajnish Tiwari
      Rajnish Tiwari

      Hi Justin,

      Thanks for your inputs..In my code I have used else part also but here I just put the sample code till elseif  part.

      Regards,

      rajnish

      Author's profile photo Former Member
      Former Member

      Hi Rajnish, I was not addressing your code explicitly, just wanted to share this finding in the thread.

      Regards,

      Justin