Avatar of Parthap
Parthap asked on

PL/SQL: ORA-00904: Invalid Identifier

"select 11 number_of_columns,dm.matter_number||decode(dm.case_number,null,null,'/'||dm.case_number) matter_number,
1, dmt.matter_type, dms.matter_subtype, dm.matter_title,
df.desc_of_filing, fn_Get_AllFilingCompanies(df.filing_seq,null) company,
fn_Get_AllPreviousFilingResp(df.filing_seq) response,
decode('E0009','E0009',df.date_filed,dm.created_date) date_filed,
(SELECT * FROM (select audit_log_text from dps_matter_audit_log a
WHERE a.matter_seq = dm.matter_seq -- here it gives error
ORDER BY matter_audit_log_seq DESC)
WHERE ROWNUM=1)  AS comments,
(select * from (select ds.status_type from dps_status ds,dps_matter_audit_log dmal
where ds.status_code = dmal.status_code
and dmal.matter_seq = dm.matter_seq -- here it gives error
order by matter_audit_log_seq DESC)
WHERE ROWNUM=1)  as rejection_reason
from dps_matter dm,
dps_matter_type dmt,
dps_matter_subtype dms,
dps_filing df
where dm.matter_seq = df.matter_seq
and dm.matter_type_seq = dmt.matter_type_seq
and dm.matter_subtype_seq = dms.matter_subtype_seq
and df.filing_seq = 30;"

We are using the above SQL statement inside a package at database server with Oracle 10g ver. 10.2. Both development and production have same version of database server. But the package failed to complie at production with error message PL/SQL: ORA-00904: "DM"."MATTER_SEQ": invalid identifier. At development it is working fine. We also tried to run only the above SQL statement at production but it didn't execute, where at development both package and only SQL statement working fine.

Not able to understand what whould be the exact problem is. Please help.

Not able to find out the actual problem.
Oracle DatabaseDatabases

Avatar of undefined
Last Comment
Thomasian

8/22/2022 - Mon
Thomasian

hi Parthap,

Try this
select 
  11 number_of_columns
 ,dm.matter_number||decode(dm.case_number,null,null,'/'||dm.case_number) matter_number
 ,1
 ,dmt.matter_type
 ,dms.matter_subtype
 ,dm.matter_title
 ,df.desc_of_filing
 ,fn_Get_AllFilingCompanies(df.filing_seq,null) company
 ,fn_Get_AllPreviousFilingResp(df.filing_seq) response
 ,decode('E0009','E0009',df.date_filed,dm.created_date) date_filed
 ,(SELECT audit_log_text 
     FROM (select matter_seq, audit_log_text 
             from dps_matter_audit_log a
             ORDER BY matter_audit_log_seq DESC) T
     WHERE T.matter_seq = dm.matter_seq AND ROWNUM=1) AS comments
 ,(select status_type 
     from (select matter_seq,ds.status_type 
             from dps_status ds, dps_matter_audit_log dmal
             where ds.status_code = dmal.status_code
             order by matter_audit_log_seq DESC) T
     WHERE T.matter_seq = dm.matter_seq AND ROWNUM=1)  as rejection_reason
from 
  dps_matter dm,
  dps_matter_type dmt,
  dps_matter_subtype dms,
  dps_filing df
where 
  dm.matter_seq = df.matter_seq
  and dm.matter_type_seq = dmt.matter_type_seq
  and dm.matter_subtype_seq = dms.matter_subtype_seq
  and df.filing_seq = 30

Open in new window

ASKER
Parthap

Thanks Thomasian for your reply. My concern here is why the sql statement is compling and executing at one machine, but not at other one though both of them have same version of Oracle.
ASKER CERTIFIED SOLUTION
Thomasian

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck