Skip to Content
avatar image
Former Member

HANA SPS12 SQL Error: general error: resolveQuery failed

Hi,

The following function works good in SPS11. But it dosen't work in SPS12:

SAP DBTech JDBC: [2]: general error: "xxx"."xxx": line 42 col 3 (at pos 1698): [2] (range 3): general error: resolveQuery failed

The line 42 : d_table = SELECT DEPT_ID,DEPT_NAME,DEPT_LEVEL FROM :u_table a LEFT JOIN "fangstar.hsAgent.analysis.models::at_department" b ON a.DEPT_ID=b.DEPT_KEY WHERE b.MANDT=:mandt and statistic_at = lv_max_date;

FUNCTION "xxx"."xxx" ( 
 	mandt varchar(3)
	, user_id varchar(20)
	, obj_name varchar(255)
) 
	RETURNS table(
		mandt nvarchar(3)
		, dept_id  nvarchar(11)
   		, dept_name nvarchar(64)
   		, dept1_name nvarchar(64)
   		, dept2_name nvarchar(64)
   		, dept3_name nvarchar(64)
   		, dept4_name nvarchar(64)
   		, dept5_name nvarchar(64)
   		, dept_level integer
   		, dept_pid nvarchar(11)
   		, is_show tinyint
	)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN

 DECLARE dept_id_arr nvarchar(11) ARRAY;
 declare lv_max_date date;
 
 DECLARE v_idx INTEGER;
 DECLARE cnt INTEGER;
 DECLARE dept_level_arr integer ARRAY;
 DECLARE dept_name_arr nvarchar(64) ARRAY;
 
 SELECT COUNT (DEPT_ID) INTO cnt FROM "fangstar.hsAgent.analysis.models::at_user_analytical_privilege" WHERE MANDT=:mandt AND USERID=:user_id AND (OBJ_NAME=:obj_name or obj_name='*');
 dept_table = SELECT :mandt AS mandt, 0 as dept_id,'' as dept_name, '' AS dept1_name,'' AS dept2_name,'' AS dept3_name,'' AS dept4_name,'' AS dept5_name,-99 AS dept_level, -1 AS dept_pid FROM dummy;
 select max(statistic_at) into lv_max_date from "fangstar.hsAgent.analysis.models::at_department" where mandt = :mandt;
 IF :cnt > 0 THEN
	 u_table = SELECT DISTINCT DEPT_ID  FROM "fangstar.hsAgent.analysis.models::at_user_analytical_privilege" WHERE MANDT=:mandt AND USERID=:user_id AND (OBJ_NAME=:obj_name or obj_name='*') AND DEPT_ID <> '*';
	 d_table = SELECT DEPT_ID,DEPT_NAME,DEPT_LEVEL FROM :u_table a LEFT JOIN "fangstar.hsAgent.analysis.models::at_department" b ON a.DEPT_ID=b.DEPT_KEY WHERE b.MANDT=:mandt and statistic_at = lv_max_date;
	 dept_level_arr := ARRAY_AGG(:d_table.DEPT_LEVEL);
	 dept_id_arr := ARRAY_AGG(:d_table.DEPT_ID);
	 dept_name_arr := ARRAY_AGG(:d_table.DEPT_NAME);

	 FOR v_idx IN 1 .. CARDINALITY(:dept_level_arr) DO
		IF :dept_level_arr[:v_idx] = 1 THEN		
		   tep_table = SELECT mandt,dept_key AS dept_id,dept_name,dept1_name,dept2_name,dept3_name,dept4_name,dept5_name,IFNULL (dept_level,1) as dept_level,IFNULL (dept_pid,0) as dept_pid FROM "fangstar.hsAgent.analysis.models::at_department" WHERE MANDT=:mandt AND dept1_name =:dept_name_arr[:v_idx] and statistic_at = lv_max_date;		
		 ELSEIF :dept_level_arr[:v_idx] = 2 THEN
		    tep_table = SELECT mandt,dept_key AS dept_id,dept_name,dept1_name,dept2_name,dept3_name,dept4_name,dept5_name,IFNULL (dept_level,1) as dept_level,IFNULL (dept_pid,0) as dept_pid FROM "fangstar.hsAgent.analysis.models::at_department" WHERE MANDT=:mandt AND dept2_name =:dept_name_arr[:v_idx] and statistic_at = lv_max_date;		
		 ELSEIF :dept_level_arr[:v_idx] = 3 THEN
		    tep_table = SELECT mandt,dept_key AS dept_id,dept_name,dept1_name,dept2_name,dept3_name,dept4_name,dept5_name,IFNULL (dept_level,1) as dept_level,IFNULL (dept_pid,0) as dept_pid FROM "fangstar.hsAgent.analysis.models::at_department" WHERE MANDT=:mandt AND dept3_name =:dept_name_arr[:v_idx] and statistic_at = lv_max_date;
	 	ELSEIF :dept_level_arr[:v_idx] = 4 THEN	 	
	 	    tep_table = SELECT mandt,dept_key AS dept_id,dept_name,dept1_name,dept2_name,dept3_name,dept4_name,dept5_name,IFNULL (dept_level,1) as dept_level,IFNULL (dept_pid,0) as dept_pid FROM "fangstar.hsAgent.analysis.models::at_department" WHERE MANDT=:mandt AND dept4_name =:dept_name_arr[:v_idx] and statistic_at = lv_max_date;		
		 ELSEIF :dept_level_arr[:v_idx] = 5 THEN
		    tep_table = SELECT mandt,dept_key AS dept_id,dept_name,dept1_name,dept2_name,dept3_name,dept4_name,dept5_name,IFNULL (dept_level,1) as dept_level,IFNULL (dept_pid,0) as dept_pid FROM "fangstar.hsAgent.analysis.models::at_department" WHERE MANDT=:mandt AND dept5_name =:dept_name_arr[:v_idx] and statistic_at = lv_max_date;
		 ELSEIF :dept_level_arr[:v_idx] = -1 THEN
		    tep_table = SELECT mandt,dept_key AS dept_id,dept_name,dept1_name,dept2_name,dept3_name,dept4_name,dept5_name,IFNULL (dept_level,1) as dept_level,IFNULL (dept_pid,0) as dept_pid FROM "fangstar.hsAgent.analysis.models::at_department" WHERE MANDT=:mandt AND deptt1_name =:dept_name_arr[:v_idx] and statistic_at = lv_max_date;	 		
	 	ELSEIF :dept_level_arr[:v_idx] = -2 THEN
		   tep_table = SELECT mandt,dept_key AS dept_id,dept_name,dept1_name,dept2_name,dept3_name,dept4_name,dept5_name,IFNULL (dept_level,1) as dept_level,IFNULL (dept_pid,0) as dept_pid FROM "fangstar.hsAgent.analysis.models::at_department" WHERE MANDT=:mandt AND deptt2_name =:dept_name_arr[:v_idx] and statistic_at = lv_max_date;
 	 	ELSEIF :dept_level_arr[:v_idx] = -3 THEN
		   tep_table = SELECT mandt,dept_key AS dept_id,dept_name,dept1_name,dept2_name,dept3_name,dept4_name,dept5_name,IFNULL (dept_level,1) as dept_level,IFNULL (dept_pid,0) as dept_pid FROM "fangstar.hsAgent.analysis.models::at_department" WHERE MANDT=:mandt AND deptt3_name =:dept_name_arr[:v_idx] and statistic_at = lv_max_date;		
	 	ELSE
		   tep_table = SELECT mandt,dept_key AS dept_id,dept_name,dept1_name,dept2_name,dept3_name,dept4_name,dept5_name,IFNULL (dept_level,1) as dept_level,IFNULL (dept_pid,0) as dept_pid FROM "fangstar.hsAgent.analysis.models::at_department" WHERE MANDT=:mandt AND dept_key=:dept_id_arr[:v_idx] and statistic_at = lv_max_date; 
		 END IF;
	 	dept_table = SELECT * FROM :dept_table UNION SELECT * FROM :tep_table;
	 	END FOR ;
 END IF ;
 RETURN SELECT *,1 AS is_show FROM :dept_table ORDER BY dept_level ASC;
END;
Add comment
10|10000 characters needed characters exceeded

  • Ok, it doesn't work anymore. What have you tried so far to narrow down the cause of the problem?

    Have you looked at the query indicated by the error message? At least the last line "and statistic_at = lv_max_date;" seem wrong to me. lv_max_date is a variable and requires the colon when you want to access it.
    So the line should be "and statistic_at = :lv_max_date;"

    Anyhow, if that doesn't solve it, try to narrow the problem down as much as possible, so that others without your information models, tables etc, can reproduce it.

  • Get RSS Feed

0 Answers