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

 

[LKBrwnDBA]

The group by does not look ok, better 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,
    G.Trans_Cd,
    G.Entry_Date Bill_Date,
    G.Trans_Date,
    G.Billed,
    G.Payment,
    G.Adjustments,
    G.Jag,
    G.Totals,
    G.Dummy
From I_Arc_Permanent_Bill B
(Select
    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
  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.Trans_Cd, G.Entry_Date, G.Trans_Date,
 Having Sum( Decode(Substr(G.Trans_Cd,1,1), 'U', Credit_Amount, 0)) !=0
) G
Where G.Cntrl_No = B.Cntrl_No
  And B.Status   = 'OPEN'
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Gator25]

Thanks LKBrwnDBA,
Will give it a test on Monday.
I noticed a comma at the end of the Group By list.
Should that be eliminated ?
Thanks again.
Gator25

 

[LKBrwnDBA]

Yes, it's a typo.

But you do need a comma after:

-- Etc --
From I_Arc_Permanent_Bill B[red],[/red] --< Here
-- Etc --






----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Gator25]

Ok,
Thanks.
Will let you know the outcome.

 

[ChrisHunt]

What, exactly, do you mean by "I can either get a debit column or I can get all credit columns but not both"? Do you mean that you can't get all the figures to appear on the same line?

If so, it's because you're displaying, and grouping by, trans_cd - which is the field which determines whether it's a credit or debit.

But maybe that isn't what you mean. How about you give us a little data, and tell us how you want it to appear?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Gator25]

Chris,
Thanks for the reply.
These accounts always start with a debit amount and then thru payments, adjustments (credit) they end up with some sort of balance.
I need the debit amount to always show on the same line as the claim number it represents with what ever amount is there for JAG (credit).
I can either get all the JAG amounts by claim number with zeroes in the debit column for that claim or I can get the debit column showing money then the JAG column shows zeroes.
Does this help ?

Thanks
Gator25

 

[LKBrwnDBA]

Give us some sample data and the query result you are looking for.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Gator25]

LKBrwnDBA,
OK, I ran your script and got the ORA:00904 Error at Line 35
"G"."Cntrl_No": Invalid Identifier Any suggestions ?
==========================================================
Here is a sample
Name Billed Adjustments JAG Totals
XXXXXXXXX $0.00 $35.01 12.30 $37.31

I can't show everything do to HIPPA so this is an excerpt
Billed amount was $87.36. As you can see it is only showing the totals of all the credits.
Thanks
Gator25

 

[Dagon]

Cntrl_no is not being selected in the inline view (it is shortened and aliased as "claim"). Add it in e.g.

Select
    g.cntrl_no,
    Substr(G.Cntrl_No,1,10) Claim,
    G.Trans_Cd,
    G.Entry_Date Bill_Date,
    G.Trans_Date,
...

 

[LKBrwnDBA]

Ooops, yes...you need to add this:

-- etc --
    G.Dummy
From I_Arc_Permanent_Bill B
(Select
    G.Cntrl_No,
    Substr(G.Cntrl_No,1,10) Claim,
    G.Trans_Cd,
--- etc ---

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Gator25]

Ok Dagon and LKBrwnDBA,
Now it is saying Invalid Identifier on line 10 for "G"."Entry_date"
So I should remove all the aliases ?

Gator25

 

[Dagon]

Change it to just g.Bill_Date since it has already been aliased in the inline view.

 

[Gator25]

Dagon,
I no longer have errors but the formatting is all over the place. Maybe it is like Chris said that I am only getting data based on the trans_cd but that is how the accounting section, i_arc_general_ledger, handles the data. This new SQL Query still omits the DEBIT, Payments, and Adjustments. I had previously had columns defined and formatted very nice as a master detail report breaking on the insurance company with all claims listed below it before giving a newpage and the next insurance company would print, etc.
Trans Codes are defined as '01' for creating the bill, Debit. Payments are defined as 'R1', credit. Adjustments are defined as anything with a 'W', Credit. Jag is defined as anything with the letter 'U', credit. I thought the decode function would give me all those columns of data but apparently I'm up the creek ?
Sorry this is so long winded.
The master detail report has all the TTitle things including the run date of the report followed by a line of equal signs as a separator then the data for the insurance company prints on two lines then another line of equal signs then the info for the claims prints with the name of the person, date billed, billed amount, payment date, payment amount, adjustments, then JAG amount for collections
Does this help ?
Thanks

 

[LKBrwnDBA]

You could try using an ORDER BY clause for the correct break's to function in sql*plus.

Also the following constraint may be causing to miss those rows with debits and no credit amounts:

Having Sum( Decode(Substr(G.Trans_Cd,1,1), 'U', Credit_Amount, 0)) !=0

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Dagon]

Do you mean the DEBIT, Payments and Adjustments are missing altogether or just on different lines ? LKBrwnDBA's SQL still includes the trans_cd in the group by. To get all the data on one line, I would have thought you would have to miss that out i.e.

G.Cntrl_No, G.Trans_Cd, G.Entry_Date, G.Trans_Date

You may also have to omit entry_date and trans_date if the transactions you are grouping together appear on different dates. You could get round this by taking min or max of the dates.

 

[Gator25]

Dagon,
I mean the debit is not appearing. don't you need trans_cd in the group by if using decode ?
The transaction dates are sometimes different.
It could be 45 days after the bill is created before a payment is made then several more days before an adjustment is made and then 120 days before the bill goes to JAG as needing collection.
Can you post some examples ?
Thanks

 

[Dagon]

No, you don't need the trans_cd in the group by. You are using it in the sum expression, not as a column in the select list. You would, however, have to drop it from the list of columns that you are selecting. But if you are going to denormalise the transaction values from rows into columns, why would you also need to include the trans_type ? The trans_type is now implicit from the column headings of the amounts, so you don't need to include it as a column as well.

Likewise, if the transactions that are being summed together have different dates, you cannot group by the dates. The best you could do with this method would be to take the minimum or maximum date. If you want to include the trans_cds and transaction dates, then you'd have to abandon this method and use windowing functions. However, then you'd still be getting one row for each input record, so the totals would be repeated on every line.

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

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'

As for why the figures aren't there, I can't see any obvious reason. Are you sure they aren't just further down the list because rows are coming back in a different order than previously ? Try putting an "order by g.cntrl_no", so you can see all related transactions together.

 

[Gator25]

Ok, will try your layout.
Would the column definitions mess it up.
See below.
============================================================
COLUMN INS_CO_NAME NEW_VALUE INS_CO_VAR NOPRINT;
COLUMN INS_CO_ADDRESS2 NEW_VALUE INS_ADD_VAR NOPRINT;
COLUMN INS_CO_CITY NEW_VALUE INS_CITY_VAR NOPRINT;
COLUMN INS_CO_STATE NEW_VALUE INS_ST_VAR NOPRINT;
COLUMN INS_CO_ZIP NEW_VALUE INS_ZIP_VAR NOPRINT;
COLUMN CLAIM HEADING "Claim|Number" FORMAT A10;
COLUMN NAME HEADING "Patient" FORMAT A30;
COLUMN CHARGES HEADING "Amt|Billed" FORMAT $99,999.99;
COLUMN PAYMENTS HEADING "Payments" FORMAT $99,999.99;
COLUMN WRITEOFFS HEADING "Adjustments" FORMAT $99,999.99;
COLUMN JAG HEADING "JAG" FORMAT $99,999.99;
COLUMN TOTALS HEADING "Claim|Total" FORMAT $99,999.99;
COLUMN TRANS_DATE HEADING "Trans|Date" FORMAT A11;
COLUMN ENC_DATE HEADING "Date|Of|Service" FORMAT A9;
COLUMN BILL_DATE HEADING "Date|Billed" FORMAT A11;

BREAK ON DUMMY ON Ins_Co_Name SKIP;

COMPUTE SUM OF 'CHARGES' ON DUMMY;
COMPUTE SUM OF 'PAYMENTS' ON DUMMY;
COMPUTE SUM OF 'WRITEOFFS' ON DUMMY;
COMPUTE SUM OF 'JAG' ON DUMMY;
COMPUTE SUM OF 'TOTALS' ON DUMMY;

COLUMN TODAYS_DATE NEW_VALUE TODAYS_DATE NOPRINT;
COLUMN DUMMY NOPRINT;

and of course I have the linesize and pagesize prior to that
as well as set verify off and termout off

Thanks

 

[Dagon]

I'd concentrate on getting the data right before you worry about the format of the report. However, for the "break on ins_co_name", you would have to order by that column. Also, you might be able to drop dummy and just use "report", as in:

BREAK ON REPORT ON Ins_Co_Name SKIP;

COMPUTE SUM OF CHARGES ON REPORT;
COMPUTE SUM OF PAYMENTS ON REPORT;
COMPUTE SUM OF WRITEOFFS ON REPORT;
COMPUTE SUM OF JAG ON REPORT;
COMPUTE SUM OF TOTALS ON REPORT;