Skip to Content
-1

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 12 at 02:58 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 11 at 06:07 PM

    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

    Add comment
    10|10000 characters needed characters exceeded