"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,
(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,
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.