SQL Plus

5 pts.
Tags:
SQLPlus
Subquery
I have a subquery that works but I need to change some parameters. Specifically 2 fields in a different table. When I do, using aliases, it stops working, "Invalid Identifier". The first select gets all the fields from 2 tables then the subquery uses the 2nd table. Table B has some fields as well as some from G. Here is the code.......... Select To_Char (Sysdate, 'Mm/Dd/Yy Hh24mi') Todays_Date, (Sysdate) -232 Report_Date, (Sysdate) -120 End_Date, B.Ins_Co_Name, B.Ins_Co_Address2, B.Ins_Co_City, B.Ins_Co_State, B.Ins_Co_Zip, B.Pat_Lname| |','| |B.Pat_Fname| |' '| |Pat_Middle_Initial Name, G.Claim, G.Bill_Date, G.Trans_Date, G.Billed, G.Payments, G.Adjustments, G.Jag, G.Balance, G.Dummy From I_Arc_Permanent_Bill B, (Select Substr(G.Cntrl_No,1,10) Claim, g.cntrl_no, min(G.Entry_Date) Bill_Date, min(G.Trans_Date) Trans_Date, Sum( Decode(Substr(G.Trans_Cd,1,2),'01',G.Debit_Amount,0)) Billed, Sum( Decode(Substr( G.Trans_Cd,1,1), 'R', G.Credit_Amount, 0)) - Sum( Decode(Substr( G.Trans_Cd,1,3), 'A01', G.Debit_Amount, 0)) Payments, Sum( Decode(Substr( G.Trans_Cd,1,1), 'W', G.Credit_Amount, 0)) - Sum( Decode(Substr( G.Trans_Cd,1,3), 'A08', G.Debit_Amount, 0)) - Sum( Decode(Substr( G.Trans_Cd,1,3), 'A09', G.Debit_Amount, 0)) - Sum( Decode(Substr( G.Trans_Cd,1,3), 'A10', G.Debit_Amount, 0)) - Sum( Decode(Substr( G.Trans_Cd,1,3), 'A20', G.Debit_Amount, 0)) Adjustments, Sum( Decode(Substr(G.Trans_Cd,1,1),'U',G.Credit_Amount,0)) - Sum( Decode(Substr(G.Trans_Cd,1,2),'AU',G.Debit_Amount,0)) Jag, Sum(G.Debit_Amount - G.Credit_Amount) Balance, Sum(0) Dummy From I_Arc_General_Ledger G Where G.Entry_Date Between Trunc(Sysdate) -232 And Trunc(Sysdate) -120 -----And Substr(G.Trans_Cd,1,2) !='AU' Group By G.Cntrl_No having SUM( DECODE(substr( TRANS_CD,1,1), 'U', CREDIT_AMOUNT, 0)) !=0) G Where G.Cntrl_No = B.Cntrl_No And B.Status = 'OPEN' Order By b.Ins_Co_Name I need to add B.Bill_date and B.Enc_Date.....This will replace G.Entry_Date and G.Trans_Date Please help
ASKED: August 25, 2008  5:55 PM
UPDATED: September 16, 2008  11:26 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’m not sure if the code you pasted here is the ‘old’ query, which was working, or it is the new one, which fails.
I think it could be useful to know the complete error message (which always contains the line number in which the error was detected).

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following