Re: DB2 group by with date functions
Thanks everyone. Our DBA is on vacation and I noticed it is V7 (though I was sure that what I tried was in the SQL standard). I went with the view method. It was fine. Lindy -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Bass, Walter W Sent: 17. kesäkuuta 2008 1:43 To: IBM-MAIN@BAMA.UA.EDU Subject: Re: DB2 group by with date functions I don't believe this is quite correct. SUBSTR(), YEAR() and MONTH() are not column functions, they are scalar functions. I believe the problem may be that you cannot even use SCALAR functions in the GROUP BY unless you are in version 8 NEW FUNCTION MODE. Check to see if your site is on version 8 in COMPATIBILITY MODE? Bill Bass United Health Care Greenville, SC -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DB2 group by with date functions
-Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Lindy Mayfield Sent: Monday, June 16, 2008 4:06 PM To: IBM-MAIN@BAMA.UA.EDU Subject: DB2 group by with date functions I seem to have forgotten how to do this, and I cannot seem to find the answer in the docs or via Google. I want to group by using two columns which are year() and month(), but the result set isn't grouping. For example: Select Year(Expdate), Month(Expdate) , Category, Sum(Expamt) from EXP.EXPENSES group by expdate, category order by 1 desc, 2 desc, 3 Gives: 20086 Adjustments12.68 20086 Car10.50 20086 Car18.50 What is the syntax for the group columns when they are functions? Thanks! Lindy Select Year(Expdate), Month(Expdate) , Category, Sum(Expamt) from EXP.EXPENSES group by Year(expdate), Month(Expdate), category order by 1 desc, 2 desc, 3 ref: http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPK10/1.1. 6 -- John McKown Senior Systems Programmer HealthMarkets Keeping the Promise of Affordable Coverage Administrative Services Group Information Technology The information contained in this e-mail message may be privileged and/or confidential. It is for intended addressee(s) only. If you are not the intended recipient, you are hereby notified that any disclosure, reproduction, distribution or other use of this communication is strictly prohibited and could, in certain circumstances, be a criminal offense. If you have received this e-mail in error, please notify the sender by reply and delete this message without copying or disclosing it. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DB2 group by with date functions
Thanks John. That's exactly what I thought, then I started doubting myself when I couldn't get it to work. Select Year(Expdate), Month(Expdate), Category, sum(expamt) from EXP.EXPENSES group by Year(Expdate), Month(Expdate), Category order by 1 desc, 2 desc, 3 -+-+-+-+-+-+-+-+ DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL (. SOME SYMBOLS THAT MIGHT BE LEGAL ARE: FOR WITH FETCH ORDER UNION EXCEPT QUERYNO OPTIMIZE DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE DSNT415I SQLERRP= DSNHPARS SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD= 0 0 0 -1 158 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD= X'' X'' X'' X'' X'009E' X'' SQL DIAGNOSTIC INFORMATION -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of McKown, John Sent: 17. kesäkuuta 2008 0:14 To: IBM-MAIN@BAMA.UA.EDU Subject: Re: DB2 group by with date functions -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Lindy Mayfield Sent: Monday, June 16, 2008 4:06 PM To: IBM-MAIN@BAMA.UA.EDU Subject: DB2 group by with date functions Select Year(Expdate), Month(Expdate) , Category, Sum(Expamt) from EXP.EXPENSES group by Year(expdate), Month(Expdate), category order by 1 desc, 2 desc, 3 ref: http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPK10/1.1. 6 -- John McKown Senior Systems Programmer HealthMarkets Keeping the Promise of Affordable Coverage Administrative Services Group Information Technology -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DB2 group by with date functions
Ok, this is getting weird. Right out of the example: SELECT SUBSTR(WORKDEPT,1,1), MIN(EDLEVEL), MAX(EDLEVEL) FROM DSN8910.EMP GROUP BY SUBSTR(WORKDEPT,1,1) ; -+-+-+-+-+-+-+-+ DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL (. SOME SYMBOLS THAT MIGHT BE LEGAL ARE: FOR WITH FETCH ORDER UNION EXCEPT QUERYNO OPTIMIZE DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE DSNT415I SQLERRP= DSNHPARS SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD= 0 0 0 -1 2040 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD= X'' X'' X'' X'' X'07F8' X'' SQL DIAGNOSTIC INFORMATION Must be something I'm doing wrong. Has to be, but I don't yet see it. -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of McKown, John Sent: 17. kesäkuuta 2008 0:14 To: IBM-MAIN@BAMA.UA.EDU Subject: Re: DB2 group by with date functions Select Year(Expdate), Month(Expdate) , Category, Sum(Expamt) from EXP.EXPENSES group by Year(expdate), Month(Expdate), category order by 1 desc, 2 desc, 3 ref: http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPK10/1.1. 6 -- John McKown Senior Systems Programmer HealthMarkets Keeping the Promise of Affordable Coverage Administrative Services Group Information Technology -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DB2 group by with date functions
There isn't one - the fine manual (from V8) says grouping-expression cannot include any of the following items: | A correlated column | A host variable | A column function | Any function that is nondeterministic or that is defined to have | an external action | A scalar fullselect | A CASE expression whose searched-when-clause contains a quantified | predicate, an IN predicate using a fullselect, or an EXISTS | predicate Note column function as part of the list. The standard solution is to create a view which makes the year and month into real columns (in the view) and then you can group by the column name in the view. Mike Select Year(Expdate), Month(Expdate) , Category, Sum(Expamt) from EXP.EXPENSES group by expdate, category order by 1 desc, 2 desc, 3 Gives: 20086 Adjustments12.68 20086 Car10.50 20086 Car18.50 What is the syntax for the group columns when they are functions? Thanks! Lindy -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html -- Mike -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DB2 group by with date functions
Thanks, Mike. So using Group by year(xx) is something new in V9 it seems. Funny, though, I instinctively tried that syntax first and was surprised it didn't work. -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Mike Bell Sent: 17. kesäkuuta 2008 0:52 To: IBM-MAIN@BAMA.UA.EDU Subject: Re: DB2 group by with date functions There isn't one - the fine manual (from V8) says grouping-expression cannot include any of the following items: | A correlated column | A host variable | A column function | Any function that is nondeterministic or that is defined to have | an external action | A scalar fullselect | A CASE expression whose searched-when-clause contains a quantified | predicate, an IN predicate using a fullselect, or an EXISTS | predicate Note column function as part of the list. The standard solution is to create a view which makes the year and month into real columns (in the view) and then you can group by the column name in the view. Mike -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: DB2 group by with date functions
I don't believe this is quite correct. SUBSTR(), YEAR() and MONTH() are not column functions, they are scalar functions. I believe the problem may be that you cannot even use SCALAR functions in the GROUP BY unless you are in version 8 NEW FUNCTION MODE. Check to see if your site is on version 8 in COMPATIBILITY MODE? Bill Bass United Health Care Greenville, SC -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Lindy Mayfield Sent: Monday, June 16, 2008 6:05 PM To: IBM-MAIN@BAMA.UA.EDU Subject: Re: DB2 group by with date functions Thanks, Mike. So using Group by year(xx) is something new in V9 it seems. Funny, though, I instinctively tried that syntax first and was surprised it didn't work. -Original Message- From: IBM Mainframe Discussion List [mailto:[EMAIL PROTECTED] On Behalf Of Mike Bell Sent: 17. kesäkuuta 2008 0:52 To: IBM-MAIN@BAMA.UA.EDU Subject: Re: DB2 group by with date functions There isn't one - the fine manual (from V8) says grouping-expression cannot include any of the following items: | A correlated column | A host variable | A column function | Any function that is nondeterministic or that is defined to have | an external action | A scalar fullselect | A CASE expression whose searched-when-clause contains a quantified | predicate, an IN predicate using a fullselect, or an EXISTS | predicate Note column function as part of the list. The standard solution is to create a view which makes the year and month into real columns (in the view) and then you can group by the column name in the view. Mike -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
