Netteza Dynamic SQL Boolean(Any) Statements

Question

I want to use dynamic sql in Netezza for this example. But I can't. In general, I want to know if I can use dynamic instances for parts of the code that do not require "execute immediate". For instance the condition of an If else statement. I basically dont know how to use dynamic sql when I have several statements in a row. Any comments are appreciated.

CREATE OR REPLACE PROCEDURE "SP_Count_Ndays_between_date1_date2_where"  (DATE, DATE, varchar(400), varchar(400))
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
START_DATE ALIAS FOR $1;
END_DATE ALIAS FOR $2;
whichweekdays ALIAS FOR $3;
whichdaysofmonth ALIAS FOR $4;
CURR_DATE DATE;
DAY_COUNT INT;
sql varchar(40000);
BEGIN
DAY_COUNT = 0;


CURR_DATE  =  START_DATE;


WHILE CURR_DATE <=  END_DATE
LOOP

IF  '(extract(dow from CURR_DATE) IN'|| whichweekdays||' and extract(day from CURR_DATE) IN'|| whichdaysofmonth||')' 
    THEN DAY_COUNT  = DAY_COUNT + 1; 
End if ;


  CURR_DATE  = CURR_DATE + 1;
END LOOP;




 RETURN DAY_COUNT;
 END;
 END_PROC;

Answer 1

You can use a mixture of static and dynamic SQL to tackle this problem.

Keep your outer loop, and then reate a dynamic SQL string on each step of your outer loop. Then use that result in an inner FOR IN EXECUTE LOOP block.

Mine the boolean result from that, do your non-dynamic IF-THEN, and then keep on looping.

CREATE OR REPLACE PROCEDURE SP_Count_Ndays_between_date1_date2_where  (DATE, DATE, varchar(400), varchar(400))
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE

START_DATE ALIAS FOR $1;
END_DATE ALIAS FOR $2;
whichweekdays ALIAS FOR $3;
whichdaysofmonth ALIAS FOR $4;
CURR_DATE DATE;
DAY_COUNT INT;
sql varchar(40000);

vResult record;

BEGIN

DAY_COUNT = 0;
CURR_DATE  =  START_DATE;

WHILE CURR_DATE <=  END_DATE
LOOP

    sql := 'SELECT (
    extract(dow from ''' || CURR_DATE|| '''::DATE) IN ('|| whichweekdays||') and extract(day from ''' || CURR_DATE || '''::DATE) IN ('|| whichdaysofmonth|| ')
    ) bool_test' ;

    RAISE NOTICE '%', sql;

    for vResult in execute sql LOOP

        RAISE NOTICE 'Date: %, Test Result %', CURR_DATE, vResult.bool_test;

        IF vResult.bool_test then 
            DAY_COUNT = DAY_COUNT +1;
        END IF;

    END LOOP;

    CURR_DATE  = CURR_DATE + 1;

END LOOP;

RETURN DAY_COUNT;

END;
END_PROC;

Results, with my overly verbose debug statements, here:

TESTDB.ADMIN(ADMIN)=> exec SP_Count_Ndays_between_date1_date2_where (CURRENT_DATE, CURRENT_DATE + '4 days'::interval, '4,5,6', '7,8,9,10,11');
NOTICE:  SELECT (
        extract(dow from '2015-08-07'::DATE) IN (4,5,6) and extract(day from '2015-08-07'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-07, Test Result t
NOTICE:  SELECT (
        extract(dow from '2015-08-08'::DATE) IN (4,5,6) and extract(day from '2015-08-08'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-08, Test Result f
NOTICE:  SELECT (
        extract(dow from '2015-08-09'::DATE) IN (4,5,6) and extract(day from '2015-08-09'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-09, Test Result f
NOTICE:  SELECT (
        extract(dow from '2015-08-10'::DATE) IN (4,5,6) and extract(day from '2015-08-10'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-10, Test Result f
NOTICE:  SELECT (
        extract(dow from '2015-08-11'::DATE) IN (4,5,6) and extract(day from '2015-08-11'::DATE) IN (7,8,9,10,11)
        ) bool_test
NOTICE:  Date: 2015-08-11, Test Result f
 SP_COUNT_NDAYS_BETWEEN_DATE1_DATE2_WHERE
------------------------------------------
                                        1
(1 row)