I have a rather long query that was so long I put some of the subquery logic into an SQL function for readability and because the "format sql" function in the program we use--DBVisualizer 6.0.15--was beginning to error out. So I got the query complete and tried to run it but got error 583 state 42845. I dropped the function and redefined it as NO EXTERNAL ACTION, but the error remained. I even tried defining the function as deterministic, which I do not believe it is because the table the query comes from can change from time to time so that the function will return different values from the same input values, but the error remained. There is one call in count_lessgrades but it is to a deterministic function that always returns the same values for the same inputs.
What can I do to resolve the error? I can replace the code, but I should be able to place the subqueries in a function. I should then be able to place my overall query in a view.
Code:
CREATE FUNCTION A.COUNT_LESSGRADES
(
PARM_YEAR INT,
PARM_PER_ID VARCHAR(20),
PARM_LOC_ID VARCHAR(20),
PARM_SERVICE_ID VARCHAR(20),
PARM_NUM_OF_PARTS INT,
PARM_PART_NUM INT,
PARM_STANDARD INT
)
RETURNS INT
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
SELECT
COUNT(BISDCUST.GRADE(SG_IN.STU_GRD_TYP_GRP_SEC_V_VCGRDVAL))
FROM
STUDENT S
INNER JOIN
STUDENT_CLASS SC
ON
(
S_IN.PER_ID = SC.PER_ID
)
INNER JOIN
STUDENT_GRD_TYP_GRP_SEC_VAL SG //STUDENT GRADE
ON
(
SC.PER_ID = SG.PER_ID
AND SC.LOC_ID = SG.LOC_ID
AND SC.DIST_COURSE_ID = SG.DIST_COURSE_ID
AND SC.LOC_CRS_SECTION_ID = SG.LOC_CRS_SECTION_ID
AND SC.STUDENT_CAL_ID = SG.STUDENT_CAL_ID
AND SC.STU_GRD_GROUP_ID = SG.STU_GRD_GROUP_ID
AND SC.DIST_COURSE_FISCAL_YEAR = SG.STUDENT_CAL_FISCAL_YEAR
)
INNER JOIN
COURSE_CATALOG CC
ON
(
SC.DIST_COURSE_ID = CC.DIST_COURSE_ID
AND SC.DIST_COURSE_FISCAL_YEAR = CC.DIST_COURSE_FISCAL_YEAR
)
INNER JOIN
LOCATION_CRS_SECTION_TERM LCST_IN
ON
(
SC_IN.LOC_ID = LCST_IN.LOC_ID
AND SC_IN.DIST_COURSE_ID = LCST_IN.DIST_COURSE_ID
AND SC_IN.DIST_COURSE_FISCAL_YEAR = LCST_IN.DIST_COURSE_FISCAL_YEAR
AND SC_IN.STUDENT_CAL_ID = LCST_IN.STUDENT_CAL_ID
AND SC_IN.LOC_CRS_SECTION_ID = LCST_IN.LOC_CRS_SECTION_ID
AND SC_IN.STUDENT_CAL_TERM_TYPE = LCST_IN.STUDENT_CAL_TERM_TYPE
)
WHERE
SC_IN.dist_course_fiscal_year = PARM_YEAR
AND SC_IN.PER_ID = PARM_PER_ID
AND SC_IN.LOC_ID = PARM_LOC_ID
AND DCC_IN.RFDS_SERVICE_ID_CODE = PARM_SERVICE_ID
AND BISDCUST.COURSESEQCODE(DCC_IN.DIST_COURSE_NUM_OF_PARTS, LCST_IN.LOC_CRS_SEC_TAUGHT_PART_NUM) = BISDCUST.COURSESEQCODE ( PARM_NUM_OF_PARTS, PARM_PART_NUM)
AND SG_IN.GRADE_TYPE_NM in ('1st Six Weeks', '2nd Six Weeks', '3rd Six Weeks', '4th Six Weeks', '5th Six Weeks', '6th Six Weeks')
AND BISDCUST.GRADE(SG_IN.STU_GRD_TYP_GRP_SEC_V_VCGRDVAL) < PARM_STANDARD
EXAMPLE:
Code:
SELECT COUNT_LESSGRADES(LCST.DIST_COURSE_FISCAL_YEAR, S.PER_ID, SC.LOC_ID, DCC.RFDS_SERVICE_ID_CODE, 3, 1, 70) != 0
FROM INNER JOIN
STUDENT_GRD_TYP_GRP_SEC_VAL SG
ON
(
SC.PER_ID = SG.PER_ID
AND SC.LOC_ID = SG.LOC_ID
AND SC.DIST_COURSE_ID = SG.DIST_COURSE_ID
AND SC.LOC_CRS_SECTION_ID = SG.LOC_CRS_SECTION_ID
AND SC.STUDENT_CAL_ID = SG.STUDENT_CAL_ID
AND SC.STU_GRD_GROUP_ID = SG.STU_GRD_GROUP_ID
AND SC.DIST_COURSE_FISCAL_YEAR = SG.STUDENT_CAL_FISCAL_YEAR
)
INNER JOIN
DISTRICT_COURSE_CATALOG DCC
ON
(
SC.DIST_COURSE_ID = DCC.DIST_COURSE_ID
AND SC.DIST_COURSE_FISCAL_YEAR = DCC.DIST_COURSE_FISCAL_YEAR
)
INNER JOIN
LOCATION_CRS_SECTION_TERM LCST
ON
(
SC.LOC_ID = LCST.LOC_ID
)
AND
(
SC.DIST_COURSE_ID = LCST.DIST_COURSE_ID
AND SC.DIST_COURSE_FISCAL_YEAR = LCST.DIST_COURSE_FISCAL_YEAR
AND SC.STUDENT_CAL_ID = LCST.STUDENT_CAL_ID
AND SC.LOC_CRS_SECTION_ID = LCST.LOC_CRS_SECTION_ID
AND SC.STUDENT_CAL_TERM_TYPE = LCST.STUDENT_CAL_TERM_TYPE
)
WHERE
SC.dist_course_fiscal_year = bisdcust.CURRENTFISCAL()
AND
(
SG.GRADE_TYPE_NM LIKE '1st Six Weeks'
or SG.GRADE_TYPE_NM LIKE '2nd Six Weeks'
or SG.GRADE_TYPE_NM LIKE '3rd Six Weeks'
or SG.GRADE_TYPE_NM LIKE '4th Six Weeks'
or SG.GRADE_TYPE_NM LIKE '5th Six Weeks'
or SG.GRADE_TYPE_NM LIKE '6th Six Weeks'
or SG.GRADE_TYPE_NM LIKE 'Average 1'
or SG.GRADE_TYPE_NM LIKE 'Average 2'
or SG.GRADE_TYPE_NM LIKE 'Yearly'
)
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09050" with level identifier "03010107". Informational tokens are "DB2 v9.5.0.808", "s071001", "NT3295", and Fix Pack "0". Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
dBforums
>
Error -583, SQLState: 42845
05-18-10, 17:54
Error -583, SQLState: 42845
Linear Mode
