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

 

[Gator25]

Ok.
I have good news. The last example gives the Billed Amount on every claim/cntrl_no.
To correctly capture just those claims for jag I used the having clause from the original script right after Group By G.Cntrl_No
having SUM( DECODE(substr( TRANS_CD,1,1), 'U', CREDIT_AMOUNT, 0)) !=0
) G
This gives 12 pages of data and every claim shows the Billed/debit amount.
Now how to make it fit on landscape
Is there a way in SQL Plus to do that automatically ?
I presume one must not go beyond 140 linesize ?
Thanks

 

[Dagon]

I'm not aware of anything that restricts linesize to 140 in sql*plus, unless you mean that's the maximum width of the landscape report. I don't think sql*plus has any specific features to make it produce landscape reports. You would just generate a report with a large linesize and then print it in landscape format.

 

[Gator25]

I wish to thank everyone on this post for guiding me to the proper conclusion.
I have since made a Master Detail report that prints the claim data below an insurance company and then does a page break once the company changes, etc. etc.



Gator25

 

[Gator25]

Dagon,
Can you help with one more item for this code ?
I'm trying to have the money in the payment, adjustment, and Jag add up to equal the total billed amount in the total column.
The problem is under the adjustments there are codes that are used for credit_amount and debit_amount.
By using these codes the adjustment column is correct for all claims.
The final sum is on the credit_amount and when it encounters a claim that had some debit adjustments then the totals are off by a good bit.
Here is what I have done..................
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)) Jag,
Sum(G.Credit_Amount)Total
etc
The A codes are used to reverse what happened on the credit side
As you can see this is an excerpt from your last suggested script.
Thanks for looking.
Gator25

 

[Dagon]

Let me try to follow this. You are adding up:

R - A01
W - (A8 - A20)
U

then expect it to add up to the same as the sum of all transations i.e. R + W + U + other transaction types if there are codes not covered by your report. Unless all the adjustment amounts are zero, I don't see how this will happen. Do you know why exactly it's going wrong ?

 

[Dagon]

The second one should read:

W - (A8 to A20)

Trying to use - to mean two different things is never a good idea.

 

[Gator25]

Dagon,
Your analysis is correct. Trying to add all 3 columns which should equal the original billed amount.
It appears that the A08,A09,A10,A20 have something to do with these few accounts. Not every claim has these transactions so the total amount does equal the billed amount when adding all 3 columns across. All those A's are debits because they are needed to reverse "A Mistake" that has occurred when doing the WriteOff. If I omit those A's then the Writeoff column is out of balance.
Does that make sense ?
Thanks

 

[Dagon]

Not really. I still don't understand what the problem is exactly. Is this a data issue ? If so, there's not a lot I can do about it.

 

[Gator25]

Ok,
Is the above code using decode the best way to go about this and how would you add all three columns ?
Thanks

 

[Dagon]

Do you mean you want to add up the payments, adjustments and jag columns together to get the total rather than than get the total by summing up all credit amounts ?

 

[Gator25]

Yes, that is exactly the answer.
Thanks

 

[Dagon]

Well, obviously you could just repeat the sums, but that would by a bit clumsy. A better approach would be to use an inline view e.g.

select billed, payments, adjustments, jag,
payments + adjustments + jag as total
from
(select
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)) Jag,
...

 

[Gator25]

Ok, so how would that be applied to your answer on the 19th
Here is what you said on the 19th..................

At the moment I'm probably suggesting something along the lines of:


CODE
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(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)) 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.Entry_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'

 

[Dagon]

You just need to add another outer query:

select
Todays_Date, 
Ins_Co_Name,      
Ins_Co_Address2,
Ins_Co_City,
Ins_Co_State,
Ins_Co_Zip,
Name,             
Claim,
Bill_Date,
Trans_Date,
Billed,
Payment,
Adjustments,
Jag,
Cntrl_no,
payment + adjustments + jag as total
from
(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.Cntrl_no
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)) 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
   From I_Arc_General_Ledger G
  Where G.Entry_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]

Thanks

 

[Gator25]

Dagon,
Last glitch.
I need to exchange two fields.
G.Bill_Date,---> becomes b.bill_Date,
G.Trans_Date,---> becomes b.Enc_Date,
and then the final change is
Where G.Entry_Date Between Trunc(Sysdate) -210 And Trunc(Sysdate) -120
G.Entry_Date becomes B.Enc_date
When I do these changes then I get the "Invalid Identifier"
message and the same for the Where entry.
??
Thanks

 

[Dagon]

What's your current SQL ?

 

[Gator25]

SqlPlus 10.0
Oracle 10g

 

[Dagon]

No, I meant what is the current SQL statement you are trying to run.

 

[Gator25]

The one you submitted on 19 Aug 2008 @9:01

I need to replace those G statements for the Bill_Date and Trans_Date and the G.Entry_Date.
The Bill_Date becomes B.Bill_Date and the Trans_Date becomes B.ENC_Date and also Entry_Date becomes B.ENC_Date
I made a mistake in the original using the General_Ledger fields when I should have been using the Permanent_Bill fields.