This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes Count(*) and SQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Count(*) and SQL Query" Watch "Count(*) and SQL Query" New topic
Author

Count(*) and SQL Query

Isaac Mcmahnus
Greenhorn

Joined: Jan 08, 2007
Posts: 16
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,
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30356
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jesus Angeles
Ranch Hand

Joined: Feb 26, 2005
Posts: 2053
In these cases, does sql error messages help? There arent any helpful error msg number that gives a clue?
Isaac Mcmahnus
Greenhorn

Joined: Jan 08, 2007
Posts: 16
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.
Jesus Angeles
Ranch Hand

Joined: Feb 26, 2005
Posts: 2053
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.
 
 
subject: Count(*) and SQL Query