Home » SQL & PL/SQL » SQL & PL/SQL » java.sql.SQLException: ORA-00936: missing expression: Occurign Randomly
java.sql.SQLException: ORA-00936: missing expression: Occurign Randomly [message #580874] Thu, 28 March 2013 20:29 Go to next message
Shillu1
Messages: 3
Registered: March 2013
Location: Montevideo
Junior Member
I am getting the ORA-00936: missing expression error while running the below stated query. Problem is that it is running and fetching the data successfully for the first 5 runs. But from the 6th run it is not fetching the data and throwing the ORA-00936: missing expression error. consider I am passing parameters to the query and for the first 5 values i pass its runnign fine. when i run the query for the 6th time or more with different parameter, its throwing the error. it does not seems to be the data issue because when i pass the 6th parameter for the first run on the next day, it is fetching data. Kindly advise what could be the issue.

(SELECT ABC.POST_SMRY_CORR_I,NVL(ABC.CORR_TYPE_C,' ') AS CORR_TYPE_C,NVL(ABC.ENTR_DOC_I,' ') AS ENTR_DOC_I,
ABC.EST_LIQN_D, '' AS EVNT_TYPE_C, ABC.CORR_STAT_C
FROM POST_SMRY_CORR ABC,POST_ENTR_DISCP PSCWQ,ENTR_DOC ED
WHERE ABC.POST_SMRY_CORR_I = PSCWQ.POST_ENTR_DOC_I
and ED.ENTR_DOC_I = '123' AND ABC.ENTR_DOC_I=ED.ENTR_DOC_I
AND ED.ENTR_TYPE_C != 11
AND POST_ENTR_TYPE_C='PSC' AND DISCP_STAT_C !='CL' AND ABC.CORR_STAT_C !='CLS'

UNION

SELECT TO_NUMBER('') AS POST_SMRY_CORR_I,'' AS CORR_TYPE_C, NVL(PED.ENTR_DOC_I,' ') AS ENTR_DOC_I,
TO_DATE('','mm-dd-yyyy') AS EST_LIQN_D,'' AS EVNT_TYPE_C, '' AS CORR_STAT_C
FROM POST_ENTR_DISCP PED,ENTR_DOC ED
WHERE PED.ENTR_DOC_I=ED.ENTR_DOC_I
and ED.ENTR_DOC_I = '123' AND ED.ENTR_TYPE_C != 11
AND PED.POST_ENTR_TYPE_C='PSC' AND DISCP_STAT_C !='CL' AND PED.ENTR_DOC_I NOT IN(SELECT ENTR_DOC_I from POST_SMRY_CORR WHERE CORR_STAT_C = 'CLS')
AND PED.POST_ENTR_DOC_I NOT IN(SELECT POST_SMRY_CORR_I FROM POST_SMRY_CORR)

UNION all

SELECT ABC.POST_SMRY_CORR_I,NVL(ABC.CORR_TYPE_C,' ') AS CORR_TYPE_C,NVL(ABC.ENTR_DOC_I,' ') AS ENTR_DOC_I,ABC.EST_LIQN_D,
EVNT.EVNT_N, ABC.CORR_STAT_C
FROM POST_SMRY_CORR ABC, POST_ENTR_EVNT EVNT,POST_ENTR_DISCP PSCWQ,ENTR_DOC ED
WHERE ABC.POST_SMRY_CORR_I=PSCWQ.POST_ENTR_DOC_I
and ED.ENTR_DOC_I = '123' AND ABC.ENTR_DOC_I=ED.ENTR_DOC_I
AND ED.ENTR_TYPE_C != 11
AND ABC.POST_SMRY_CORR_I=EVNT.EVNT_DOC_I
AND EVNT.EVNT_TYPE_C='PSC' AND PSCWQ.DISCP_STAT_C !='CL' AND ABC.CORR_STAT_C !='CLS'
AND EVNT.POST_ENTR_EVNT_I IN
(SELECT POST_ENTR_EVNT_I FROM POST_ENTR_EVNT PEVN WHERE PEVN.CRTE_TS IN
(SELECT MAX(CRTE_TS) FROM POST_ENTR_EVNT GROUP BY EVNT_DOC_I)))

MINUS

(SELECT ABC.POST_SMRY_CORR_I,NVL(ABC.CORR_TYPE_C,' ') AS CORR_TYPE_C,NVL(ABC.ENTR_DOC_I,' ') AS ENTR_DOC_I,ABC.EST_LIQN_D,
'' AS EVNT_TYPE_C ,ABC.CORR_STAT_C FROM POST_SMRY_CORR ABC,POST_ENTR_DISCP PSCWQ
WHERE ABC.POST_SMRY_CORR_I = PSCWQ.POST_ENTR_DOC_I
and ABC.ENTR_DOC_I = '123' AND ABC.POST_SMRY_CORR_I IN
(SELECT DISTINCT ABC.POST_SMRY_CORR_I
FROM POST_SMRY_CORR ABC, POST_ENTR_EVNT EVNT, POST_ENTR_DISCP PSCWQ
WHERE ABC.POST_SMRY_CORR_I=EVNT.EVNT_DOC_I AND ABC.POST_SMRY_CORR_I = PSCWQ.POST_ENTR_DOC_I
AND EVNT.EVNT_TYPE_C = 'PSC'))

ORDER BY CORR_STAT_C desc

[Updated on: Fri, 29 March 2013 11:44]

Report message to a moderator

Re: java.sql.SQLException: ORA-00936: missing expression: Occurign Randomly [message #580875 is a reply to message #580874] Thu, 28 March 2013 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

since the posted SQL is unformatted & difficult to read, please elaborate, highlight, describe & clarify the reported "parameters" & their values.

Since we don't have your tables or data, we can't run, test, debug, or improve posted SQL.
Re: java.sql.SQLException: ORA-00936: missing expression: Occurign Randomly [message #580909 is a reply to message #580875] Fri, 29 March 2013 12:12 Go to previous messageGo to next message
Shillu1
Messages: 3
Registered: March 2013
Location: Montevideo
Junior Member
Sorry For the bad formatting of the query sir. Here i have highlighted the value. Kinldy advise if this would help.
Re: java.sql.SQLException: ORA-00936: missing expression: Occurign Randomly [message #580910 is a reply to message #580909] Fri, 29 March 2013 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
Shillu1 wrote on Fri, 29 March 2013 10:12
Sorry For the bad formatting of the query sir. Here i have highlighted the value. Kinldy advise if this would help.


here where?
my crystal ball is broken & I can not understand what you mean.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


how can we reproduce what you report?
Re: java.sql.SQLException: ORA-00936: missing expression: Occurign Randomly [message #580913 is a reply to message #580910] Fri, 29 March 2013 12:36 Go to previous messageGo to next message
Shillu1
Messages: 3
Registered: March 2013
Location: Montevideo
Junior Member
(SELECT ABC.POST_SMRY_CORR_I,NVL(ABC.CORR_TYPE_C,' ') AS CORR_TYPE_C,NVL(ABC.ENTR_DOC_I,' ') AS ENTR_DOC_I,
ABC.EST_LIQN_D, '' AS EVNT_TYPE_C, ABC.CORR_STAT_C
FROM POST_SMRY_CORR ABC,POST_ENTR_DISCP PSCWQ,ENTR_DOC ED
WHERE ABC.POST_SMRY_CORR_I = PSCWQ.POST_ENTR_DOC_I
and ED.ENTR_DOC_I = '123' AND ABC.ENTR_DOC_I=ED.ENTR_DOC_I
AND ED.ENTR_TYPE_C != 11
AND POST_ENTR_TYPE_C='PSC' AND DISCP_STAT_C !='CL' AND ABC.CORR_STAT_C !='CLS'

UNION

SELECT TO_NUMBER('') AS POST_SMRY_CORR_I,'' AS CORR_TYPE_C, NVL(PED.ENTR_DOC_I,' ') AS ENTR_DOC_I,
TO_DATE('','mm-dd-yyyy') AS EST_LIQN_D,'' AS EVNT_TYPE_C, '' AS CORR_STAT_C
FROM POST_ENTR_DISCP PED,ENTR_DOC ED
WHERE PED.ENTR_DOC_I=ED.ENTR_DOC_I
and ED.ENTR_DOC_I = '123' AND ED.ENTR_TYPE_C != 11
AND PED.POST_ENTR_TYPE_C='PSC' AND DISCP_STAT_C !='CL' AND PED.ENTR_DOC_I NOT IN(SELECT ENTR_DOC_I from POST_SMRY_CORR WHERE CORR_STAT_C = 'CLS')
AND PED.POST_ENTR_DOC_I NOT IN(SELECT POST_SMRY_CORR_I FROM POST_SMRY_CORR)

UNION all

SELECT ABC.POST_SMRY_CORR_I,NVL(ABC.CORR_TYPE_C,' ') AS CORR_TYPE_C,NVL(ABC.ENTR_DOC_I,' ') AS ENTR_DOC_I,ABC.EST_LIQN_D,
EVNT.EVNT_N, ABC.CORR_STAT_C
FROM POST_SMRY_CORR ABC, POST_ENTR_EVNT EVNT,POST_ENTR_DISCP PSCWQ,ENTR_DOC ED
WHERE ABC.POST_SMRY_CORR_I=PSCWQ.POST_ENTR_DOC_I
and ED.ENTR_DOC_I = '123' AND ABC.ENTR_DOC_I=ED.ENTR_DOC_I
AND ED.ENTR_TYPE_C != 11
AND ABC.POST_SMRY_CORR_I=EVNT.EVNT_DOC_I
AND EVNT.EVNT_TYPE_C='PSC' AND PSCWQ.DISCP_STAT_C !='CL' AND ABC.CORR_STAT_C !='CLS'
AND EVNT.POST_ENTR_EVNT_I IN
(SELECT POST_ENTR_EVNT_I FROM POST_ENTR_EVNT PEVN WHERE PEVN.CRTE_TS IN
(SELECT MAX(CRTE_TS) FROM POST_ENTR_EVNT GROUP BY EVNT_DOC_I)))

MINUS

(SELECT ABC.POST_SMRY_CORR_I,NVL(ABC.CORR_TYPE_C,' ') AS CORR_TYPE_C,NVL(ABC.ENTR_DOC_I,' ') AS ENTR_DOC_I,ABC.EST_LIQN_D,
'' AS EVNT_TYPE_C ,ABC.CORR_STAT_C FROM POST_SMRY_CORR ABC,POST_ENTR_DISCP PSCWQ
WHERE ABC.POST_SMRY_CORR_I = PSCWQ.POST_ENTR_DOC_I
and ABC.ENTR_DOC_I = '123' AND ABC.POST_SMRY_CORR_I IN
(SELECT DISTINCT ABC.POST_SMRY_CORR_I
FROM POST_SMRY_CORR ABC, POST_ENTR_EVNT EVNT, POST_ENTR_DISCP PSCWQ
WHERE ABC.POST_SMRY_CORR_I=EVNT.EVNT_DOC_I AND ABC.POST_SMRY_CORR_I = PSCWQ.POST_ENTR_DOC_I
AND EVNT.EVNT_TYPE_C = 'PSC'))

ORDER BY CORR_STAT_C desc
Re: java.sql.SQLException: ORA-00936: missing expression: Occurign Randomly [message #580920 is a reply to message #580913] Fri, 29 March 2013 13:39 Go to previous message
Michel Cadot
Messages: 59122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session, the WHOLE session.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Previous Topic: Dynamic Rows Generate
Next Topic: Can I get this required result.?
Goto Forum:
  


Current Time: Thu Sep 18 07:08:46 CDT 2014

Total time taken to generate the page: 0.12245 seconds