I'm having a problem executing this QUERY from my web. The Query works inside DB2, but for whatever reason, inside the javabean or within JSP, it comes back as a Failed SQL statement.
I tried to play around and I think it has something to do with the COUNT(*). When I test each and every select, it works. When I test Count(*) by itself it works. But when I try to Mix SELECT A, COUNT(*) it doesn't work from jsp or my java bean.
The Query looks something like this, and like i said, it does work within DB2, but it does not work when i call it from the JSP page.
String SQL= "SELECT S.SYSTEM_SITE, " +
"M.MATL_LOC, "+
"DATE(CAST(R.RTST AS CHAR(7))) AS RTST_DATE, " +
"R.MCC, " +
"R.REC_ID, " +
"M.MATL_TYPE, " +
"COUNT(*) AS QTY, " +
"R.ABCOFF_STAT, " +
"R.ABC_STAT, " +
"'DB2DB', " +
"V.VNDR_NAME, " +
"DAYS(DATE(CAST(R.NXT_RTST_DTE AS CHAR(7)))) - DAYS
(CURRENT_DATE) AS DAYS_TO_RETEST, " +
"DATE(CAST(R.REC_REJ_DTE AS CHAR(7))) AS REJECT_DATE, " +
"DAYS(DATE(CAST(R.REC_REJ_DTE AS CHAR(7)))) - DAYS
(CURRENT_DATE) AS DAYS_TO_REJECT, " +
"R.PGM_ID, " +
"R.VNDR_BATCH, " +
"CASE R.REC_REJ_DTE_OVRDE " +
"WHEN 'N' THEN 0 " +
"WHEN 'Y' THEN " +
"CASE MC.CONT_DTE_BASIS " +
"WHEN 'M' THEN DAYS(DATE(CAST(R.REC_REJ_DTE AS CHAR(7)))) - (DAYS(DATE(CAST(R.MFG_DTE AS CHAR(7)))) +
MC.SHELF_LIFE) " +
"WHEN 'S' THEN DAYS(DATE(CAST(R.REC_REJ_DTE AS CHAR(7)))) - (DAYS(DATE(CAST(R.SHPT_DTE AS CHAR(7)))) +
MC.SHELF_LIFE) " +
"WHEN 'R' THEN DAYS(DATE(CAST(R.REC_REJ_DTE AS CHAR(7)))) -
(DAYS(DATE(CAST(R.REC_DTE AS CHAR(7)))) +
MC.SHELF_LIFE) " +
"END " +
"END AS daysPastOrigRjct, " +
"R.P_O_NBR " +
"FROM ABCDEFG.LOCATION AS L, " +
"ABCDEFG.MATERIAL AS M, " +
"ABCDEFG.MATL_CONTROL AS MC, " +
"ABCDEFG.RECEIVAL AS R, " +
"ABCDEFG.SITE_UNIQUE AS S, " +
"ABCDEFG.VENDOR_NAME AS V " +
"WHERE MC.LOT_NBR = 'ABC' " +
"AND R.MCC = MC.MCC " +
"AND R.REC_DISP_STAT = 'I' " +
"AND M.REC_ID = R.REC_ID " +
"AND M.MATL_DISP_STAT = 'I' " +
"AND L.LOCATION = M.MATL_LOC " +
"AND S.SYSTEM_ID = L.SYSTEM_ID " +
"AND V.VNDR_CODE = R.VNDR_CODE " +
"GROUP BY RTST_DATE, S.SYSTEM_SITE, R.MCC, R.REC_ID,
M.MATL_LOC, " +
"M.MATL_TYPE, R.BUYOFF_STAT, R.RTST_STAT, V.VNDR_NAME,
R.REC_REJ_DTE, R.PGM_ID, " +
"R.VNDR_BATCH, R.REC_REJ_DTE_OVRDE, MC.CONT_DTE_BASIS,
R.REC_REJ_DTE, R.MFG_DTE, MC.SHELF_LIFE, " +
"R.SHPT_DTE, R.REC_DTE, R.P_O_NBR " +
"ORDER BY 1, 2, 3 DESC, 4";
I changed some of the names around, so if the names don't seem to match up, its cause i just changed them to display on this forum. And the statement does get lined up with correct quotations and all, it just didn't seem to line up inside this forum. Maybe I missed a comma or something, but I'll check that.
Like I said, the SQL statement works in DB2....... The SQL statement works from JSP and/or the Javabean if i get rid of the COUNT(*) and GROUP BY statements.
The SQL statement works if it's just SELECT COUNT(*).. But for whatever reason within JSP and JDBC, when I combine the two, the SQL statement doesn't execute.
Any ideas on what the problem is? I'm stuck at this point.
Thanks,