How to use IF ELSE in SAP HANA SQLQueries with Examples

 How to use IF ELSE in SAP HANA SQLQueries with Examples

In Sap hana we use IF and ELSE SQL statement to runs other SQL statements depending on whether a condition is true or not. 

There are many ways to use IF Statements like

• It can be as simple as an IF condition is true do something ELSE do something else. 
• Nested IF statements 
• User defined functions as part of the IF statement can also be used in order to increase the condition requirements

1. Simple IF ELSE Condition 

Example:

DO BEGIN

    DECLARE VS_DAY VARCHAR(35):=DAYNAME(CURRENT_DATE);

DECLARE V_MESS VARCHAR(150);

IF VS_DAY<>'MONDAY' THEN

V_MESS:='WRONG DAY NOT YOUR BIRTHDAY'||VS_DAY;

ELSE

V_MESS:='WAO YOUR BIRTHDAY'||TO_CHAR(CURRENT_DATE,'YYYY/MM/DD');

END IF;

SELECT V_MESS FROM DUMMY;

END;

   2.  SAP HANA Nested IF ELSE Condition

Example:

DO BEGIN

   DECLARE VD_DATE DATE := to_date('2020/11/30','YYYY/MM/DD'); 

  DECLARE VI_MONTH INTEGER := EXTRACT(MONTH from VD_DATE) ; 

  DECLARE V_MESS VARCHAR(100);

 

  IF VI_MONTH > 6 THEN

     IF VI_MONTH > 8 AND VI_MONTH < 11 THEN

        V_MESS := to_char(VD_DATE,'YYYY/MM/DD')||' Date between Sept and October ';

     ELSEIF VI_MONTH = 11 or VI_MONTH = 12 THEN

         V_MESS := to_char(VD_DATE,'YYYY/MM/DD')||' Date in Nov or December';

     END IF;

  ELSEIF VI_MONTH > 3 AND VI_MONTH < 7 THEN

      V_MESS := to_char(VD_DATE,'YYYY/MM/DD')||' Date within April and June';

  ELSEIF VI_MONTH < 4 THEN

       V_MESS := to_char(VD_DATE,'YYYY/MM/DD')||' Date Between Jan and March ';

  END IF;                 

                

  SELECT 'Actual day : '||VD_DATE||', '||V_MESS as col1  from dummy;

   END; 

3. Using a user defined function with a SAP HANA IF statement

 Example :CREATE  FUNCTION FVALID_VALUE(IV_VAL INTEGER)

  RETURNS OC_VAL VARCHAR

  LANGUAGE SQLSCRIPT

  READS SQL DATA AS

   BEGIN

      if :IV_VAL < 8 and :IV_VAL > 0

      then

                    OC_VAL := 'Y';

      else

        OC_VAL := 'N'            ;

                end if;

   END;