Re: Interesting query request for PeopleSoft

Ian Michael Gumby wrote: > 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. Check it out. > <http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_022009>; This syntax will not work on V10. I'm curious to know what is the problem with using "1"? Is it in peoplesoft layer? Can't you workaround it? An it only affects the GROUP BY? Not the ORDER BY? Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Fernando, Yes, it appears to only affect the group by. According to the developer, Psoft code will place the entire string "month(creation_dt) || '/' || year(creation_dt) AS month" into the group by. You have to remember, it's PeopleSoft, which was purchase by Obstacle, it doesn't have to make sense!! I made a rec to create a view, then select from the view. Fernando Nunes <domusonline@gmai l.com> To Sent by: informix-list@iiug.org informix-list-bou cc nces@iiug.org Subject Re: Interesting query request for 02/15/2009 02:50 PeopleSoft PM Ian Michael Gumby wrote: > 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. Check it out. > <http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_022009 > This syntax will not work on V10. I'm curious to know what is the problem with using "1"? Is it in peoplesoft layer? Can't you workaround it? An it only affects the GROUP BY? Not the ORDER BY? Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list