SQL Plus 10.0

[Gator25]

The following script tells me invalid table name.
I'm confused as this is defined.
I'm trying to count all the claims and money by biller and show a total for everything.
Here is the code.
------CLRK_DAILY8
set verify off
set linesize 80
set pagesize 60

INPUT
ACCEPT EDATE ENTRY_DATE PROMPT "Date: "

COLUMN CLERK FORMAT A10;
COLUMN CLAIMS HEADING "# Bills" FORMAT 9999;
COLUMN AMOUNT FORMAT $999,999.99;
COLUMN ENTRY_DATE NEW_VALUE ED_ID_VAR NOPRINT;
-----BREAK ON CLERK SKIP 1 ON REPORT SKIP2;
BREAK ON REPORT;
COMPUTE COUNT OF 'CLAIMS' ON CLERK;
COMPUTE SUM OF 'AMOUNT' ON CLERK;
-----1COMPUTE SUM OF 'AMOUNT' ON REPORT ON CLERK;

TTITLE CENTER 'Womack Army Medical Center' -
, SKIP CENTER 'TPOCS ITEMIZED BILLING' -
, SKIP CENTER 'TPOCS Clerk Billing Summary Report'-
, SKIP CENTER "BILLING DATE:" ED_ID_VAR SKIP2-
, LEFT 'REPORT DATE: 'TODAYS_DATE RIGHT 'Page:'SQL.PNO SKIP 2;
COLUMN TODAYS_DATE NEW_VALUE TODAYS_DATE NOPRINT;
COLUMN DUMMY NOPRINT;

Spool Test_Claims.txt

select to_char (SYSDATE, 'MM/DD/YY HH24MI') TODAYS_DATE,
at.entry_user AS Clerk,
g.Claim,
g.trans_cd Amount,
g.Dummy
From Audit_Trail at,
Select substr(g.cntrl_no,1,10) Claim,
COUNT(g.cntrl_no),
SUM( DECODE(substr( gl.TRANS_CD,1,2),'01', gl.Debit_Amount, 0)) AMOUNT,
SUM(0) DUMMY
from i_general_ledger g
Group By at.entry_user) g
where substr(at.entry_date,1,9) = to_date('&EDATE','DD-MON-YY')
and at.action = 'READY_TO_POST->POSTED'
and at.key =g.cntrl_no
order by at.entry_user
/
Spool Off
Thanks in advance

 

[Dagon]

It looks as if you're trying to use an inline view here:

Select  substr(g.cntrl_no,1,10) Claim, 
        COUNT(g.cntrl_no),
        SUM( DECODE(substr( gl.TRANS_CD,1,2),'01', gl.Debit_Amount, 0)) AMOUNT,
        SUM(0) DUMMY
from i_general_ledger g
Group By at.entry_user)

If so, I would expect it to start with a bracket:

(Select  substr(g.cntrl_no,1,10) Claim, 
        COUNT(g.cntrl_no),
        SUM( DECODE(substr( gl.TRANS_CD,1,2),'01', gl.Debit_Amount, 0)) AMOUNT,
        SUM(0) DUMMY
from i_general_ledger g
Group By at.entry_user)

 

[Gator25]

Yes,
I was attempting my first try with an inline view.
Thanks for the quick response.
Will apply your suggestions and let you know.

 

[Gator25]

Dagon,
Added your suggestion(s) and now get this error
Line 12 AT"."ENTRY_USER": invalid identifier
?

 

[BJCooperIT]

AT is a reserved word. Try changing the alias:

From Audit_Trail at,

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[Dagon]

I tried running this and it worked fine:

select * from dual at 
where at.dummy = 'X'

so I doubt if that is the problem.

The problem is that you are trying to use entry_user, which comes from audit_trail, in the inline view. The inline view is a completely autonomous piece of SQL - it cannot reference any tables from other parts of the query. Since the inline view only accesses the general_ledger table, it will not be able to see columns from audit_trail.

You would have to do one of two things:

a) add a join to audit_trail to the inline view
b) move the group by outside of the inline view and into the main query.

 

[Gator25]

dagon,
Could you illustrate what you mean.
Thanks

 

[Dagon]

Try this and see how close it gets you to the answer:

select to_char (SYSDATE, 'MM/DD/YY HH24MI') TODAYS_DATE,
        at.entry_user AS Clerk,
        g.Claim,
        g.Amount,
        g.Dummy
From Audit_Trail at,
(Select  substr(g.cntrl_no,1,10) Claim, 
        COUNT(g.cntrl_no),
        SUM( DECODE(substr( gl.TRANS_CD,1,2),'01', gl.Debit_Amount, 0)) AMOUNT,
        SUM(0) DUMMY
from i_general_ledger g
Group By g.cntrl_no) g
where substr(at.entry_date,1,9) = to_date('&EDATE','DD-MON-YY')
and at.action = 'READY_TO_POST->POSTED'
and at.key =g.cntrl_no
order by at.entry_user

 

[Gator25]

OK, copied it over to a new file and got this error...
ERROR at line 9:
ORA-00904: "GL"."DEBIT_AMOUNT": invalid identifier
This deals with the DECODE

 

[Dagon]

Your table alias is g not gl.

select to_char (SYSDATE, 'MM/DD/YY HH24MI') TODAYS_DATE,
        at.entry_user AS Clerk,
        g.Claim,
        g.Amount,
        g.Dummy
From Audit_Trail at,
(Select  substr(g.cntrl_no,1,10) Claim, 
        COUNT(g.cntrl_no),
        SUM( DECODE(substr( g.TRANS_CD,1,2),'01', g.Debit_Amount, 0)) AMOUNT,
        SUM(0) DUMMY
from i_general_ledger g
Group By g.cntrl_no) g
where substr(at.entry_date,1,9) = to_date('&EDATE','DD-MON-YY')
and at.action = 'READY_TO_POST->POSTED'
and at.key =g.cntrl_no
order by at.entry_user

 

[Gator25]

Thanks.