Re: Interesting query request for PeopleSoft
Look the text marked with ***
Using Select Numbers
You can use one or more integers in the GROUP BY clause to stand for column
expressions. In the next example, the first SELECT statement uses select numbers
for order_date and paid_date - order_date in the GROUP BY clause. You can group ***only by a combined expression
using the select numbers***.
In the second SELECT statement, you cannot replace the 2 with
the arithmetic expression paid_date - order_date:
SELECT order_date, COUNT(*), paid_date - order_date
FROM orders GROUP BY 1, 3;
SELECT order_date, paid_date - order_date
FROM orders GROUP BY order_date, 2;
by SQL Syntax Manual....
<http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids_sqs_1052.htm
>
--- Em qui, 12/2/09, Darren_Jacobs@carmax.com <Darren_Jacobs@carmax.com> escreveu:
De: Darren_Jacobs@carmax.com <Darren_Jacobs@carmax.com>
Assunto: Interesting query request for PeopleSoft
Para: informix-list@iiug.org
Data: Quinta-feira, 12 de Fevereiro de 2009, 19:57
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
Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com