DECODE in SQL Plus version 10 2

[Gator25]

Hi,
I am new to this forum and have created a script that pulls data from two files.
The one table has accounting data and the second one has demographic data.
The accounting table is the one with the problem.
I can either get a debit column or I can get all credit columns but not both.
I appologize if this is not the correct place or that the code is too long.
select to_char (SYSDATE, 'MM/DD/YY HH24MI') TODAYS_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,
substr(g.cntrl_no,1,10) Claim,
g.trans_cd,
g.entry_date BILL_DATE,
g.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)) Payment,
SUM( DECODE(substr( g.TRANS_CD,1,1), 'W', g.CREDIT_AMOUNT, 0)) Adjustments,
SUM( DECODE(substr(g.TRANS_CD,1,1),'U',g.CREDIT_AMOUNT,0)) Jag,
SUM(g.CREDIT_AMOUNT) Totals,
SUM(0) DUMMY
FROM i_arc_general_ledger g,i_arc_permanent_bill b
where g.entry_date between trunc(sysdate) -210 and trunc(sysdate) -120
and g.cntrl_no IN b.cntrl_no
and substr(g.trans_cd,1,2) !='AU'
and b.status = 'OPEN'
group by b.ins_co_name,b.ins_co_address2,b.ins_co_city,b.ins_co_state,b.ins_co_zip,g.cntrl_no,g.debit_amount,
g.credit_Amount,g.trans_cd,g.entry_date,g.trans_date,b.Pat_Lname,b.Pat_Fname,b.Pat_Middle_Initial
having SUM( DECODE(substr( TRANS_CD,1,1), 'U', CREDIT_AMOUNT, 0)) !=0
/
Thanks in advance

 

[Dagon]

Select To_Char (Sysdate, 'Mm/Dd/Yy Hh24mi') Todays_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.Payment,
    G.Adjustments,
    G.Jag,
    G.Totals,
    G.Dummy,
    G.Cntrl_no
From I_Arc_Permanent_Bill B,
(Select
    Substr(G.Cntrl_No,1,10) Claim,
    g.cntrl_no,
    min(B.Bill_Date) Bill_Date,
    min(B.Enc_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)) Payment,
    Sum( Decode(Substr( G.Trans_Cd,1,1), 'W', G.Credit_Amount, 0))  Adjustments,
    Sum( Decode(Substr(G.Trans_Cd,1,1),'U',G.Credit_Amount,0)) Jag,
    Sum(G.Credit_Amount) Totals,
    Sum(0) Dummy
   From I_Arc_General_Ledger G
  Where G.Enc_Date Between Trunc(Sysdate) -210 And Trunc(Sysdate) -120
    And Substr(G.Trans_Cd,1,2) !='AU'
  Group By  G.Cntrl_No) G
Where G.Cntrl_No = B.Cntrl_No
  And B.Status   = 'OPEN'

 

[Gator25]

I had tried the above changes but I also had gone to the B alias and added the B.Bill_Date and B.Enc_Date. I thought they had to be part of the first select.

Where G.Enc_Date Between Trunc(Sysdate) -210 And Trunc(Sysdate) -120
Should not the G become a B since the ENC_Date is part of the permanent_bill table ?

 

[Gator25]

I get this error

"G"."ENC_DATE": invalid identifier

 

[Dagon]

Since I don't have your table definitions, I can't test the SQL. Try this.

Select To_Char (Sysdate, 'Mm/Dd/Yy Hh24mi') Todays_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,
    B.Bill_Date,
    B.Enc_Date as Trans_Date,
    G.Billed,
    G.Payment,
    G.Adjustments,
    G.Jag,
    G.Totals,
    G.Dummy,
    G.Cntrl_no
From I_Arc_Permanent_Bill B,
(Select
    Substr(G.Cntrl_No,1,10) Claim,
    g.cntrl_no,
    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)) Payment,
    Sum( Decode(Substr( G.Trans_Cd,1,1), 'W', G.Credit_Amount, 0))  Adjustments,
    Sum( Decode(Substr(G.Trans_Cd,1,1),'U',G.Credit_Amount,0)) Jag,
    Sum(G.Credit_Amount) Totals,
    Sum(0) Dummy
   From I_Arc_General_Ledger G
  Where Substr(G.Trans_Cd,1,2) !='AU'
  Group By  G.Cntrl_No) G
Where G.Cntrl_No = B.Cntrl_No
  And B.Status   = 'OPEN'
  And B.Enc_Date Between Trunc(Sysdate) -210 And Trunc(Sysdate) -120

 

[Gator25]

Dagon,
We have a working script.
I addded your change to the B.Bill_Date and B.Enc_Date
and I changed the last 4 lines to read...........
HAVING SUM ( DECODE(substr( TRANS_CD,1,1)'U',G.Credit_Amount, 0)) !=0) G
Where B.Bill_Date Between Trunc(Sysdate) -232 And Trunc(Sysdate) -120
And G.Cntrl_No = B.Cntrl_No
and B.Status = 'OPEN'
Order By Ins_Co_Name

Thanks You very much.

 

[Gator25]

LKBrwnDBA,
Your replies to the ansi thread have been deleted.
I know no other way to correspond.
Could you tell me how ?
Thanks