mkoni
20-07-21, 14:53
Wenn ich in einer SQL Procedure eine "GLOBAL TEMPORARY TABLE" definiere und die definition eine Variable enthält bekomme ich einen SQL0312 RC9 Fehler.
Geht das gar nicht oder mache ich in der definition etwas falsch?
oder gibts eine umgehung?
Hier als Beispiel eine SQL-Procedur (es geht um die variable BASEDIR):
CREATE OR REPLACE PROCEDURE TEST.CHKIFSDIR ( )
LANGUAGE SQL
SPECIFIC TEST.CHKIFSDIR
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE BASEDIR VARCHAR ( 70 ) ;
SET BASEDIR = '/home/Test/' ;
SET BASEDIR = TRIM(BASEDIR);
DECLARE GLOBAL TEMPORARY TABLE GENIFS0 AS
(
SELECT
SUBSTR(TRIM(PATH_NAME), LOCATE_IN_STRING(TRIM(PATH_NAME),'/',-1) + 1, LENGTH(TRIM(PATH_NAME))) AS GENDIR, PATH_NAME
FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => BASEDIR, -- here is the error, variable is not allowed
SUBTREE_DIRECTORIES => 'YES',
OBJECT_TYPE_LIST => '*ALLDIR',
IGNORE_ERRORS => 'YES' )) t
) WITH DATA WITH REPLACE
;
END;
Geht das gar nicht oder mache ich in der definition etwas falsch?
oder gibts eine umgehung?
Hier als Beispiel eine SQL-Procedur (es geht um die variable BASEDIR):
CREATE OR REPLACE PROCEDURE TEST.CHKIFSDIR ( )
LANGUAGE SQL
SPECIFIC TEST.CHKIFSDIR
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
CONCURRENT ACCESS RESOLUTION DEFAULT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE BASEDIR VARCHAR ( 70 ) ;
SET BASEDIR = '/home/Test/' ;
SET BASEDIR = TRIM(BASEDIR);
DECLARE GLOBAL TEMPORARY TABLE GENIFS0 AS
(
SELECT
SUBSTR(TRIM(PATH_NAME), LOCATE_IN_STRING(TRIM(PATH_NAME),'/',-1) + 1, LENGTH(TRIM(PATH_NAME))) AS GENDIR, PATH_NAME
FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => BASEDIR, -- here is the error, variable is not allowed
SUBTREE_DIRECTORIES => 'YES',
OBJECT_TYPE_LIST => '*ALLDIR',
IGNORE_ERRORS => 'YES' )) t
) WITH DATA WITH REPLACE
;
END;