Skip to Content

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

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

    1. Denis Sproten 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

      (0) 
    1. Denis Sproten 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

      (0) 
      1. 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

        (0) 
  1. Justin Molenaur

    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

    (0) 
    1. 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

      (0) 

Leave a Reply