RE: Interesting query request for PeopleSoft

Can you rewrite the query as an inner select statement and then do the group by in the outer query? Like : SELECT month, count_of_exceptions FROM ( SELECT month(creation_dt) || '/' || year(creation_dt) AS month, COUNT(*) as count_of_exceptions FROM ps_cxmtchexcptnarc a WHERE a.created_dttm = (SELECT min(b.created_dttm) FROM ps_cxmtchexcptnarc b WHERE b.business_unit = a.business_unit AND b.voucher_id = a.voucher_id AND b.voucher_line_num = a.voucher_line_num AND b.match_rule_id = a.match_rule_id AND b.created_dttm <= today+1 ) ) GROUP BY month ORDER BY month I'm not sure if the syntax is right but you should get the concept. HTH -G > Subject: Interesting query request for PeopleSoft > To: informix-list@iiug.org > From: Darren_Jacobs@carmax.com > Date: Thu, 12 Feb 2009 16:57:17 -0500 > > > Good Afternoon, > > IDS v10FC8 > HPUX 11.23 > > I have a developer that's trying to create a query which uses an expression > in the group by. The group by 1 works (obviously). If he uses the > expression in the group by it does not work. > > Is this possible? This will become a psoft query and you can't use '1' in > the group by in psoft since it actually pulls in the expression. I've > asked if he can parse out just the "month". > > Here's the query: > > select month(creation_dt) || '/' || year(creation_dt) as month, count(*) as > count_of_exceptions > from ps_cxmtchexcptnarc a > where a.created_dttm = > (select min(b.created_dttm) > from ps_cxmtchexcptnarc b > where b.business_unit = a.business_unit > and b.voucher_id = a.voucher_id > and b.voucher_line_num = a.voucher_line_num > and b.match_rule_id = a.match_rule_id > and b.created_dttm <= today+1) > group by month(creation_dt) || '/' || year(creation_dt) as month > order by 1 > > Any ideas would be greatly appreciated. > > Thanks for taking the time! > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Windows Live™: Keep your life in sync. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_022009