HANA Procedure : SAP DBTech JDBC: [2]: general error: qp_expr_subquery

[Rushikesh_Yeole]

PROCEDURE "SCHEMA_NAME"."AAA::ZCO_BACKJOBS" ( ) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER
	DEFAULT SCHEMA SCHEMA_NAME
	--READS SQL DATA 
	AS
BEGIN
/***************************** 
	Write your procedure logic 
 *****************************/
 call "SCHEMA_NAME"."public::ZYY_WRITEDATA" ( ( select concat (FISCAL_YEAR , lpad(FISCAL_PERIOD,3, 0)  ) from "_SYS_BI"."M_FISCAL_CALENDAR"   
where date_SQL =  add_days ( CURRENT_DATE, -60 ) and calendar_variant = 'V3' ) ,
 ( select concat (FISCAL_YEAR , lpad(FISCAL_PERIOD,3, 0)  ) from "_SYS_BI"."M_FISCAL_CALENDAR"   
where date_SQL = CURRENT_DATE and calendar_variant = 'V3' ));
 
 call "SCHEMA_NAME"."public::ZXX_WRITEDATA" ( ( 	select to_dats ( add_days( CURRENT_DATE, -60 ) )from dummy )  , 
 	( select to_dats (CURRENT_DATE)  from dummy ) );
 	
END;

HI,

I have created procedure to scheduled in background; which contains 2 procedures which writes data into tables.

On execution of this procedure statement in SQL console, it gives error as SAP DBTech JDBC: [2]: general error: qp_expr_subquery.

Both these SQL statements in this procedure gets executed successfully in SQL console.

I have checked index server trace but did not found helpful. Please advice.

[lbreddemann · Accepted solution]

Sergio is spot on here!

Don't put the SQL query text as the parameters for your procedure calls.

Instead, assign those to table variables and use the variables for the parameters.

PROCEDURE "SCHEMA_NAME"."AAA::ZCO_BACKJOBS" ( ) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER
	DEFAULT SCHEMA SCHEMA_NAME
	AS
BEGIN

tab_A = select concat (FISCAL_YEAR , lpad(FISCAL_PERIOD,3, 0)  ) 
        from "_SYS_BI"."M_FISCAL_CALENDAR"
        where 
        date_SQL =  add_days ( CURRENT_DATE, -60 ) 
        and calendar_variant = 'V3' );

tab_B = select concat (FISCAL_YEAR , lpad(FISCAL_PERIOD,3, 0)  ) 
        from "_SYS_BI"."M_FISCAL_CALENDAR"   
        where 
        date_SQL = CURRENT_DATE 
        and calendar_variant = 'V3';

call "SCHEMA_NAME"."public::ZYY_WRITEDATA" ( :tab_A, :tab_B);
 
tab_C = select to_dats (add_days( CURRENT_DATE, -60 ) ) from dummy ; 
tab_D =	select to_dats (CURRENT_DATE)  from dummy;

call "SCHEMA_NAME"."public::ZXX_WRITEDATA" (:tab_C, :tab_D );
 	
END;

By re-writing the code like this, it's easy to spot the nonsense, too:

- all queries effectively look for single values and not sets of tuples

- for tab_C and tab_D there is no need for any SELECT query - it's a simple formula evaluation.

So this whole thing can be rewritten to:

PROCEDURE "SCHEMA_NAME"."AAA::ZCO_BACKJOBS" ( ) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER
	DEFAULT SCHEMA SCHEMA_NAME
	AS
BEGIN
DECLARE fiscYearPeriodA NVARCHAR(7);
DECLARE fiscYearPeriodB NVARCHAR(7);
DECLARE todayDATS NVARCHAR(8);
DECLARE twoMonthPastDATS NVARCHAR(8);

       select 
            concat (FISCAL_YEAR , lpad(FISCAL_PERIOD,3, 0)) 
            into fiscYearPeriodA
        from "_SYS_BI"."M_FISCAL_CALENDAR"
        where 
        date_SQL =  add_days ( CURRENT_DATE, -60 ) 
        and calendar_variant = 'V3';

        select 
            concat (FISCAL_YEAR , lpad(FISCAL_PERIOD,3, 0)  ) 
            into fiscYearPeriodB
        from "_SYS_BI"."M_FISCAL_CALENDAR"   
        where 
        date_SQL = CURRENT_DATE 
        and calendar_variant = 'V3';


todayDATS :=  to_dats (add_days( CURRENT_DATE, -60 )); 
twoMonthPastDATS := to_dats (CURRENT_DATE);

call "SCHEMA_NAME"."public::ZYY_WRITEDATA"
                   (:fiscYearPeriodA, :fiscYearPeriodB);
 
call "SCHEMA_NAME"."public::ZXX_WRITEDATA" 
                   (:todayDATS, :twoMonthPastDATS );
 	
END;

With this, you can at least focus on whether the logic of this procedure makes any sense.

[SergioG_TX]

it seems like the child procs getting called inside this stored proc don't have the correct input params. try defining your table type as variables before passing them as input params... then in the child stored proc call do something like:

call <proc_name> (var_tt, var_tt2...) ; -- for each of your child proc calls