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