Query Help 2

[fmrock]

I have the following query i am working on.

I have run the inner query with the union and it works fine, however, I am trying to group them together, and must have something wrong with the syntax.

I have tried severy variations with no luck. I even had some more cafine to see if that would help today.

Select 
	Group_Num, 
	Sum(U.Num_Accounts) "Num_Accounts", 
	Sum(U.Num_of_99053) "Num_of_99053",
	Sum(U.Charge_Amount) "Total_Of_Charges",
	Sum(U.Payment_Count) "Num_of_Payments",
	Sum(U.Payment_Amount) "Total_of_Payments"
from (
SELECT 
	Account.ACCTCPCODE "Group_Num", 
	count(Account.ACCTCODE) "Num_Accounts", 
	count(Charges.PRCODE) "Num_of_99053", 
	sum(Charges.PRAMOUNT) "Charge_Amount",
	0 "Payment_Count", 
	0 "Payment_Amount"
FROM   
	MEDACCOUNT MEDACCOUNT 
	JOIN MEDCHARGES MEDCHARGES ON ((Account.ACCTCPCODE=Charges.CPCODE) AND (Account.ACCTCODE=Charges.ACCOUNT) AND (Charges.TYPE='C') AND (Charges.SPLITFLAG IS  NULL)  AND (Charges.PRCODE='99053'))
WHERE 
	(Account.ACCTDEACTIVE IS  NULL)
	AND to_date(Account.ACCTLOGDATE,'j')>=TO_DATE ('01-09-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
	AND (Account.ACCTCPCODE = '155003')
GROUP BY
	Account.ACCTCPCODE
UNION ALL
SELECT 
	Account.ACCTCPCODE "Group_Num", 
	0 "Num_Accounts", 
	0 "Num_of_99053", 
	0 "Charge Amount",
	count(Charges_1.PRCODE) "Payment_Count", 
	sum(Charges_1.PRAMOUNT) "Payment_Amount"
FROM   
	MEDACCOUNT MEDACCOUNT 
	JOIN MEDCHARGES MEDCHARGES ON ((Account.ACCTCPCODE=Charges.CPCODE) AND (Account.ACCTCODE=Charges.ACCOUNT) AND (Charges.TYPE='C') AND (Charges.SPLITFLAG IS  NULL)  AND (Charges.PRCODE='99053'))
	JOIN MEDCHARGES Charges_1 ON ((Charges.CPCODE=Charges_1.CPCODE) AND (Charges.ACCOUNT=Charges_1.ACCOUNT) AND (Charges.SEQNO=Charges_1.APPLYSEQNO) AND (Charges_1.TYPE='P') AND (Charges_1.SPLITFLAG IS  NULL))
WHERE 
	(Account.ACCTDEACTIVE IS  NULL)
	AND to_date(Account.ACCTLOGDATE,'j')>=TO_DATE ('01-09-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
	AND (Account.ACCTCPCODE = '155003')
GROUP BY
	Account.ACCTCPCODE
) U
GROUP BY Group_Num

 

[SantaMufasa]

FMRock,

I don't benefit from caffeine, but a copy-and-paste of the error message would help. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

The error i am getting is

ERROR at line 44:
ORA-00904: "GROUP_NUM": invalid identifier

 

[cmmrfrds]

Maybe it has to do with using quoted identifiers.

see link.

http://www.itk.ilstu.edu/docs/oracle/server.101/b10759/sql_elements008.htm

 

[SantaMufasa]

Yes, ,FM, my vote goes to Cmmrfrds for the correct answer..."Group_Num" (which you create in your in-line view) is not the same identifier as Group_Num to which you refer in your GROUP BY clause.

To fix it, I'd recommend your removing the double quotes in all references Group_Num or (less desirable), add quotes to Group_Num in the GROUP BY clause.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[fmrock]

I will try this first thing in the morning when i get back to work. Thanks for all your help everyone.

 

[fmrock]

Worked by taking off the double quotes