Home » SQL & PL/SQL » SQL & PL/SQL » An error can any one help me (ORACLE 9i /WINDOWS)
icon14.gif  An error can any one help me [message #294327] Thu, 17 January 2008 04:16 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

WITH PRB AS(
SELECT M.DT_REQDATE,M.DT_REQBY,M.NN_EXPERIENCE,M.NN_VACANCY,M.VC_NOTE,M.NN_SELECTED,
J.VC_JOBSPEC,QM.VC_QUALIFICATION,QM.C_DEGREE,QM.YN_TECHNICAL,D.VC_DESIGNATION,
M.C_REQSTATUS,M.NN_REQNO
FROM Hr_Man_Request_Mast M
LEFT JOIN HR_MAN_REQUEST_JOB J ON M.NN_REQNO=J.NN_REQNO
INNER JOIN HR_MAN_REQUEST_QUAL Q ON Q.NN_REQNO=M.NN_REQNO
INNER JOIN HR_QUALIFICATION_MAST QM ON Q.NN_QUALCODE=QM.NN_QUALCODE
INNER JOIN HR_DESIGNATION_MAST D ON(D.NN_DESGCODE=M.NN_DESGCODE) AND
M.DT_REQDATE BETWEEN &FDAT AND &TDAT)

SELECT DT_REQDATE FROM PRB WHERE C_REQSTATUS LIKE decode(&STAT,'O','O%','A','O%','Z')
UNION ALL
SELECT DT_REQDATE FROM PRB WHERE C_REQSTATUS LIKE decode(&STAT,'C','C%','A','C%','Z')


when i executing this query this error is obtaining can any one help me

ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "from$_subquery$_003"."NN_REQNO_0_0": invalid identifier

but when i executing this query output is getting

WITH PRB AS(
SELECT M.DT_REQDATE,M.DT_REQBY,M.NN_EXPERIENCE,M.NN_VACANCY,M.VC_NOTE,M.NN_SELECTED,
J.VC_JOBSPEC,QM.VC_QUALIFICATION,QM.C_DEGREE,QM.YN_TECHNICAL,D.VC_DESIGNATION,
M.C_REQSTATUS,M.NN_REQNO
FROM Hr_Man_Request_Mast M,HR_MAN_REQUEST_JOB J,HR_MAN_REQUEST_QUAL Q
,HR_QUALIFICATION_MAST QM,HR_DESIGNATION_MAST D
WHERE M.NN_REQNO=J.NN_REQNO(+) AND Q.NN_REQNO=M.NN_REQNO AND QM.NN_QUALCODE=Q.NN_QUALCODE
AND D.NN_DESGCODE=M.NN_DESGCODE AND M.DT_REQDATE BETWEEN &FDAT AND &TDAT )
SELECT * FROM PRB WHERE C_REQSTATUS LIKE(decode(&STAT,'O','O%','A','O%','Z'))
UNION ALL
SELECT * FROM PRB WHERE C_REQSTATUS LIKE(decode(&STAT,'C','C%','A','C%','Z'))
Re: An error can any one help me [message #294331 is a reply to message #294327] Thu, 17 January 2008 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said in one of your previous posts:
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Use SQL Formatter. Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.
Post your Oracle version (4 decimals).

Regards
Michel

Re: An error can any one help me [message #294426 is a reply to message #294327] Thu, 17 January 2008 09:58 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Also you could help us by indicating the differences between the first and second query.
Previous Topic: Sequence to start with a value known dynamically
Next Topic: Table of Table Definitions
Goto Forum:
  


Current Time: Tue Dec 06 08:52:34 CST 2016

Total time taken to generate the page: 0.08443 seconds