Re: QMQRY QQRYSRC issues group by and order by

["Frances Denoncourt" <Frances.Denoncourt@xxxxxxxxxxxxxxxxx>]

Numeric edit: I tried this for the 'R' record and it seemed to work. I should be all set for the time being.
0010.00 R         1      I     7   Source 
Thanks,
 
 
Fran Denoncourt
Sr. Programmer/Analyst
Pinal County Treasurer's Office
Florence, AZ 85232
(520) 866-6404

Receipt of this message does not grant you permission to send me 
Unsolicited Commercial Email


> > > <vhamberg@xxxxxxxxxxx> 08/27/2008 3:25 PM >>>
Fran

Looks like the example is using a QMFORM for presentation - and you're going to have a miserable time putting that stuff into source - can be done but ugly tag stuff. You'd be well off to use Query/400 to do the layout, then RTVQMFORM from the resulting *QRYDFN.

But that's chapter 2!

Carry on, Jeeves!
Vern

-------------- Original message -------------- 
From: "Frances Denoncourt" <Frances.Denoncourt@xxxxxxxxxxxxxxxxx> 

> Eric, 
> I got the quotes down pretty well now after reading a lot of posts (again) and 
> testing yesterday. Thanks. 
>
> Rob, 
> Well, that sure worked. I used option to include all of the fields in the group 
> by. Doesn't make sense, but it does work. 
>
> But, why doesn't the sum(RPREFA) work? If you could point me to the right 
> manual, I'd appreciate it. 
>
> Here is an example from the manual that doesn't even 'sum' but the result they 
> show does: 
>
>
> Example of report breaks in Query Management 
> The following SQL statement causes the report to be sorted using the values in 
> the JOB column. 
> SELECT * FROM STAFF ORDER BYJOB 
>
>
> Their results 
> ID NAME DEPT JOB YEARS SALARY 
> ------- --------- ------- ----- ------- ----------- 
> 100 Plotz 42 mgr 7 18,352.80 
> ----------- 
> * 18,352.80 
> 90 Koonitz 42 sales 6 18,001.75 
> ----------- 
> * 18,001.75 
>
>
> Fran Denoncourt 
> Sr. Programmer/Analyst 
> Pinal County Treasurer's Office 
> Florence, AZ 85232 
> (520) 866-6404 
>
> Receipt of this message does not grant you permission to send me 
> Unsolicited Commercial Email 
>
>
> > > > 08/27/2008 12:12 PM >>> 
> These two lines alone are what's important to this problem 
> SELECT RPPRCL, RPYEAR, RPBTCH, RPNAME, RPREFA, RPCHCK, RPAPYR 
> ... 
> GROUP BY RPPRCL 
> ... 
>
> Once you do a GROUP BY then the fields in the select have to be one of 
> three things: 
> - a function (like SUM, AVG, MIN, MAX...) 
> - included in the group by. 
> - omitted from the SELECT. 
>
> In other words you have to do one of three things 
>
> I - Change your select to 
> SELECT RPPRCL, MIN(RPYEAR), MAX(RPBTCH), MAX(RPNAME), SUM(RPREFA), 
> AVG(RPCHCK), SUM(RPAPYR) 
> - - tossing in a variety of functions as an example 
>
> II - Change your group by to include these: 
> SELECT RPPRCL, RPYEAR, RPBTCH, RPNAME, RPREFA, RPCHCK, sum(RPAPYR) 
> GROUP BY RPPRCL, RPYEAR, RPBTCH, RPNAME, RPREFA, RPCHCK 
>
> III - Drop some fields from the SELECT 
> SELECT RPPRCL, sum(RPAPYR) 
> GROUP BY RPPRCL 
>
> Rob Berendt 
> -- 
> Group Dekko Services, LLC 
> Dept 01.073 
> Dock 108 
> 6928N 400E 
> Kendallville, IN 46755 
> http://www.dekko.com 
>
>
>
>
>
> "Frances Denoncourt" 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx 
> 08/27/2008 03:00 PM 
> Please respond to 
> Midrange Systems Technical Discussion 
>
>
> To 
>
> cc 
>
> Subject 
> QMQRY QQRYSRC issues group by and order by 
>
>
>
>
>
>
> As many of you suggested earlier, I am trying to switch from query/400 (or 
> whatever it is these days) to Query Management queries. 
>
> I cannot use STRQM because the command is only in two prior release 
> libraries. 
>
> But, that's not the issue at the moment. 
>
> I'm following examples in the SQL Reference manual for small tests. 
> SELECT WORKDEPT, AVG(SALARY) 
> FROM EMPLOYEE 
> GROUP BY WORKDEPT 
> ORDER BY 2 
>
> and this example: 
> SELECT PROJNAME, PRSTDATE, PRENDATE 
> FROM PROJECT 
> ORDER BY PRENDATE DESC 
>
>
>
>
> My source is (I tried using Select * which didn't work either): 
> SELECT RPPRCL, RPYEAR, RPBTCH, RPNAME, RPREFA, RPCHCK, RPAPYR 
> FROM TXOVRPP 
> WHERE RPNAME = &NAME OR RPREFA > &NEGAMT AND RPREFA < &POSAMT 
> OR RPPRCL = 'B018034101' 
> GROUP BY RPPRCL 
> ORDER BY RPPRCL 
>
>
> I am calling it via a CL which worked for other examples. 
> This is the error I am getting - it's not consistent with the example from 
> the manual. 
> Any ideas which is the problem? thanks 
>
> Message ID . . . . . . : SQL0122 Severity . . . . . . . : 30 
> Message type . . . . . : Diagnostic 
> Date sent . . . . . . : 08/27/08 Time sent . . . . . . : 
> 11:27:30 
>
> Message . . . . : Column RPYEAR or expression in SELECT list not valid. 
>
> Cause . . . . . : One of the following has occurred: 
> -- The statement contains column name RPYEAR and a column function in 
> the 
> SELECT clause and no GROUP BY clause is specified, 
> -- Column name RPYEAR is specified in the SELECT clause but not in 
> the 
> GROUP BY clause. 
> -- An expression is specified in the SELECT clause but not in the 
> GROUP BY 
> clause. 
> -- The RRN, PARTITION, NODENAME, or NODENUMBER function is specified 
> in 
> the SELECT clause with a column function or a GROUP BY clause. 
> -- A column or expression that is specified in the ORDER BY clause, 
> but 
> not in the SELECT clause, does not conform to the grouping rules listed 
>
> above. 
> Recovery . . . : Do one of the following and try the request again: 
> -- If a GROUP BY clause is required, make certain that all columns or 
>
> expressions in the SELECT list and ORDER BY clause are also in the 
> GROUP BY 
> clause. Do not specify the RRN, PARTITION, NODENAME, or NODENUMBER 
> function. 
> -- If a GROUP BY clause is not needed, the SELECT list and ORDER BY 
> clause 
> should not contain column functions with column names or the RRN, 
> PARTITION, 
> NODENAME, or NODENUMBER function. 
>
>
>
> Fran Denoncourt 
> Sr. Programmer/Analyst 
> Pinal County Treasurer's Office 
> Florence, AZ 85232 
> (520) 866-6404 
>
> Receipt of this message does not grant you permission to send me 
> Unsolicited Commercial Email 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l.