This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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.
Isaac, That is one complicated SQL query. When you say it works in db2, have you tried copy/pasting the results of: System.out.println(SQL) or just a similar query?
Trying the exact query will tell you if you have missing whitespace, a missing comma or a column in the select that is not in the group by clause. (I lean towards the later since it works without count(*).)
If that works in db2, try simplifying the query in the JSP. If you remove a column from the select and group by at a time, you will have a similar problem to solve. Keep doing this to troubleshoot until you have the smallest query you can that exhibits the problem. Then you can post that here and we will be more likely to be able to help you.
In these cases, does sql error messages help? There arent any helpful error msg number that gives a clue?
Joined: Jan 08, 2007
I cut the SQL down to just one parameter and then built it upon it from there. the SQL statement worked using unix and some mainframe program that runs with DB2. But i'm creating a web version of the mainframe app so besides a few changes here and there, there are some things that need to be changed. Formatting, quotes, and so on.
In the end, I guess the problem was I didn't have enough ))) or placed a Period in the wrong place and so on.. But i finally got the query working and for all intensive purposes I just did it line by line and in the end, it worked.. Time consuming, but not as bad as I originally imagined.
Like I said, i must have misplaced a quote or paranthesis or period and so on. But i finally got it to work.....
thanks for the replies though.
Joined: Feb 26, 2005
So you mean you just missed some characters when you brought it from mainframe to pc?
You can use mainframe emulators and other ways to download your mainframe file to pc. That will be exact byte per byte.
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com