Home » SQL & PL/SQL » SQL & PL/SQL » query help (oracle 10g)
query help [message #420055] |
Wed, 26 August 2009 14:43  |
aarti81
Messages: 235 Registered: December 2007 Location: USA
|
Senior Member |
|
|
Hi
I have the following query which works fine:
select sum(t1.AMOUNT) po_paymnts,t1.PO_NO,t2.PS_QTY,t2.AMOUNT encumb_amnt,t2.ORIGINAL_AMOUNT,(t2.AMOUNT)/(t2.PS_QTY) unit_cost,(sum(t1.AMOUNT))/((t2.AMOUNT)/(t2.PS_QTY)) ytd_qty_purchased_tons,(t2.AMOUNT-sum(t1.AMOUNT)) balance_amnt
from dw_ps_all_payments_fact t1,dw_ps_ap_po_rq_fact t2
where t1.PO_NO=t2.PS_NO
and t2.OBJECT='545000'
group by t1.PO_NO,t2.AMOUNT,t2.ORIGINAL_AMOUNT,t2.PS_QTY
But when i try to insert a date field "Invoice_dt" I'm getting multiple rows for each PO_NO (which makes sense for each date), What i want to accomplish is to summarise these dates for each fiscal year, so that i get data summarised to each fiscal year.Any help is greatly appreciated.
Thanks
[fixed code tags]
[Updated on: Thu, 27 August 2009 04:25] by Moderator Report message to a moderator
|
|
|
|
Re: query help [message #420057 is a reply to message #420056] |
Wed, 26 August 2009 14:54   |
aarti81
Messages: 235 Registered: December 2007 Location: USA
|
Senior Member |
|
|
Sorry about the earlier post:
SELECT Sum(t1.amount) po_paymnts,
t1.po_no,
t2.ps_qty,
t2.amount encumb_amnt,
t2.original_amount,
(t2.amount) / (t2.ps_qty) unit_cost,
(Sum(t1.amount)) / ((t2.amount) / (t2.ps_qty)) ytd_qty_purchased_tons,
(t2.amount - Sum(t1.amount)) balance_amnt
FROM dw_ps_all_payments_fact t1,
dw_ps_ap_po_rq_fact t2
WHERE t1.po_no = t2.ps_no
AND t2.OBJECT = '545000'
GROUP BY t1.po_no,
t2.amount,
t2.original_amount,
t2.ps_qty
Thanks
|
|
|
Re: query help [message #420058 is a reply to message #420055] |
Wed, 26 August 2009 14:57   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>Sorry about the earlier post:
so much better
SELECT Sum(t1.amount) po_paymnts,
t1.po_no,
t2.ps_qty,
t2.amount encumb_amnt,
t2.original_amount,
(t2.amount) / (t2.ps_qty) unit_cost,
(Sum(t1.amount)) / ((t2.amount) / (t2.ps_qty)) ytd_qty_purchased_tons,
(t2.amount - Sum(t1.amount)) balance_amnt
FROM dw_ps_all_payments_fact t1,
dw_ps_ap_po_rq_fact t2
WHERE t1.po_no = t2.ps_no
AND t2.OBJECT = '545000'
GROUP BY t1.po_no,
t2.amount,
t2.original_amount,
t2.ps_qty
>But when i try to insert a date field "Invoice_dt"
INSERT? Invoice_dt?
>I'm getting multiple rows for each PO_NO (which makes sense for each date),
I don't see multiple rows
>What i want to accomplish is to summarise these dates for each fiscal year,
Again what dates?
>so that i get data summarised to each fiscal year.Any help is greatly appreciated.
You offer us no real clues, so you get no real advice.
[Updated on: Wed, 26 August 2009 15:03] Report message to a moderator
|
|
|
Re: query help [message #420060 is a reply to message #420058] |
Wed, 26 August 2009 15:09   |
aarti81
Messages: 235 Registered: December 2007 Location: USA
|
Senior Member |
|
|
Can you please tell me how to post my results as a table, should i copy paste?
BlackSwan wrote on Wed, 26 August 2009 14:57 | >Sorry about the earlier post:
so much better
SELECT Sum(t1.amount) po_paymnts,
t1.po_no,
t2.ps_qty,
t2.amount encumb_amnt,
t2.original_amount,
(t2.amount) / (t2.ps_qty) unit_cost,
(Sum(t1.amount)) / ((t2.amount) / (t2.ps_qty)) ytd_qty_purchased_tons,
(t2.amount - Sum(t1.amount)) balance_amnt
FROM dw_ps_all_payments_fact t1,
dw_ps_ap_po_rq_fact t2
WHERE t1.po_no = t2.ps_no
AND t2.OBJECT = '545000'
GROUP BY t1.po_no,
t2.amount,
t2.original_amount,
t2.ps_qty
>But when i try to insert a date field "Invoice_dt"
INSERT? Invoice_dt?
>I'm getting multiple rows for each PO_NO (which makes sense for each date),
I don't see multiple rows
>What i want to accomplish is to summarise these dates for each fiscal year,
Again what dates?
>so that i get data summarised to each fiscal year.Any help is greatly appreciated.
You offer us no real clues, so you get no real advice.
|
|
|
|
|
Re: query help [message #420147 is a reply to message #420060] |
Thu, 27 August 2009 04:31   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Assuming that you're just trying to add the Year part of the invoice date as one of the columns that you're grouping by, try this:
SELECT Sum(t1.amount) po_paymnts,
t1.po_no,
t2.ps_qty,
t2.amount encumb_amnt,
t2.original_amount,
(t2.amount) / (t2.ps_qty) unit_cost,
(Sum(t1.amount)) / ((t2.amount) / (t2.ps_qty)) ytd_qty_purchased_tons,
(t2.amount - Sum(t1.amount)) balance_amnt,
to_char(invoice_dt,'yyyy') inv_year
FROM dw_ps_all_payments_fact t1,
dw_ps_ap_po_rq_fact t2
WHERE t1.po_no = t2.ps_no
AND t2.OBJECT = '545000'
GROUP BY t1.po_no,
t2.amount,
t2.original_amount,
t2.ps_qty,
to_char(invoice_dt,'yyyy')
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 14:51:12 CST 2025
|