Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql rownum usage in preparedstatement

 
boniface nasah
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I get ORA:00908 Missing NULL keyword when I try to execute the following as a prepared statement. The same statement works well from TOAD or sqlprompt. I am using Oracle 10g

" select * from users where rownum <= 100".

PLEASE HELP ME!
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you post the code?
 
Santosh Kumar Nayak
Ranch Hand
Posts: 147
Eclipse IDE IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
String sql = "SELECT A.LOAN_NUM, A.LTR_SEQ_NUM SEQ_NUM, A.RQST_ID,"
+ " (CASE WHEN A.QC_STS IN ('QLTY_CHCK_LTR_CMPL','QLTY_CHCK_LTR_XCLD','QLTY_CHCK_LTR_RJCT') AND RQP.LTR_GRP_STS NOT IN ('QLTY_CHCK_LTR_GRP_IN_PRGS','QLTY_CHCK_LTR_GRP_PNDG') THEN 'Y' ELSE 'N' END) QUAL_CHCK_SUCC,"
+"(CASE WHEN A.QC_STS IN('QLTY_CHCK_LTR_XCLD','QLTY_CHCK_LTR_RJCT') AND RQP.LTR_GRP_STS NOT IN ('QLTY_CHCK_LTR_GRP_IN_PRGS','QLTY_CHCK_LTR_GRP_PNDG') THEN 'Y' ELSE 'N' END) XCLD_FLG,"
+"(CASE "
+" WHEN A.DSPTCH_STS='DSPTCH_LTR_XCLD' THEN "
+"TO_CHAR((SELECT F.CRT_ON FROM RDOC_CMNTS F WHERE F.CMNT_ID=A.DSPTCH_CMNT_ID),'RRRR/MM/DD') "
+" WHEN A.QC_STS IN('QLTY_CHCK_LTR_XCLD','QLTY_CHCK_LTR_RJCT') AND A.QC_DT IS NULL THEN TO_CHAR(RRG.RQST_END_TM,'RRRR/MM/DD')"
+"WHEN A.QC_STS IN ('QLTY_CHCK_LTR_XCLD','QLTY_CHCK_LTR_RJCT') THEN TO_CHAR(A.QC_DT,'RRRR/MM/DD') END) XCLD_DT, "
+"CASE WHEN A.CMNT_ID IS NOT NULL"
+"THEN"
+" (SELECT trim(replace (F.cmnt_dtls,chr(10),' ')) FROM RDOC_CMNTS F WHERE F.CMNT_ID=A.CMNT_ID) "
+" WHEN A.CMNT_ID IS NULL AND A.QC_STS = 'QLTY_CHCK_LTR_XCLD' "
+" THEN 'Auto Excluded for missing key columns'"
+" END XCLD_CMNT, "
+" (SELECT RBG.BTCH_NUM FROM RDOC_RQST_GEN B,RDOC_BTCH_GEN RBG WHERE B.RQST_ID=A.RQST_ID and B.BTCH_ID = RBG.BTCH_ID) BTCH_ID, "
+"A.LTR_NAME, "

+"(case "
+"when ((SELECT TO_CHAR(E.VNDR_PRNT_DT,'RRRR/MM/DD') FROM RDOC_DSPTCH_PRGS E"
+"WHERE E.DSPTCH_ID=A.DSPTCH_ID"
+"AND E.DSPTCH_SEQ=A.DSPTCH_SEQ)"
+") is not NULL then "
+"(CASE WHEN A.VNDR_STS IN ('DSPTCH_VNDR_LTR_XFER_SUCC','VNDR_RECN_FL_UPD_SUCC','VNDR_RECN_FL_XFER_SUCC') THEN 'Y'"
+"WHEN A.VNDR_STS IN('DSPTCH_VNDR_LTR_FAIL','DSPTCH_VNDR_LTR_XFER_FAIL','VNDR_RECN_FL_UPD_FAIL','VNDR_RECN_FL_XFER_FAIL','NOT FOUND')"
+"THEN 'F' END)"
+"when ((SELECT TO_CHAR(E.VNDR_PRNT_DT,'RRRR/MM/DD') FROM RDOC_DSPTCH_PRGS E"
+" WHERE E.DSPTCH_ID=A.DSPTCH_ID"
+" AND E.DSPTCH_SEQ=A.DSPTCH_SEQ)"
+"is NULL and "
+"A.VNDR_STS IN('DSPTCH_VNDR_LTR_FAIL','DSPTCH_VNDR_LTR_XFER_FAIL','VNDR_RECN_FL_UPD_FAIL','VNDR_RECN_FL_XFER_FAIL','NOT FOUND')) then 'F' "
+"else"
+"(CASE WHEN A.PRNT_STS='DSPTCH_PRNT_LTR_SUCC' THEN 'Y' "
+"WHEN A.PRNT_STS IN ('DSPTCH_PRNT_LTR_FAIL','NOT FOUND','DSPTCH_PRNT_LTR_SBMT_FAIL') THEN 'F'"
+"ELSE 'N' END) "
+"end) PRNT_FLG,"

+"(case when ((SELECT TO_CHAR(E.VNDR_PRNT_DT,'RRRR/MM/DD') FROM RDOC_DSPTCH_PRGS E"
+"WHERE E.DSPTCH_ID=A.DSPTCH_ID AND E.DSPTCH_SEQ=A.DSPTCH_SEQ)"
+" ) is not NULL then "
+"(SELECT TO_CHAR(E.VNDR_PRNT_DT,'RRRR/MM/DD') FROM RDOC_DSPTCH_PRGS E"
+"WHERE E.DSPTCH_ID=A.DSPTCH_ID AND E.DSPTCH_SEQ=A.DSPTCH_SEQ)"
+" when ((SELECT TO_CHAR(E.VNDR_PRNT_DT,'RRRR/MM/DD') VNDR_PRNT_DT FROM RDOC_DSPTCH_PRGS E"
+" WHERE E.DSPTCH_ID=A.DSPTCH_ID AND "
+"E.DSPTCH_SEQ=A.DSPTCH_SEQ) IS NULL and "
+" A.VNDR_STS IN('DSPTCH_VNDR_LTR_FAIL','DSPTCH_VNDR_LTR_XFER_FAIL','VNDR_RECN_FL_UPD_FAIL','VNDR_RECN_FL_XFER_FAIL','NOT FOUND'))"
+" then (SELECT TO_CHAR(E.DSPTCH_END_TM,'RRRR/MM/DD') PRNT_DT FROM RDOC_DSPTCH_CHNNL_DTLS E"
+" WHERE E.DSPTCH_ID=A.DSPTCH_ID AND E.DSPTCH_SEQ=A.DSPTCH_SEQ AND E.CHNNL_ID='FTP')"
+" else (SELECT TO_CHAR(E.DSPTCH_END_TM,'RRRR/MM/DD') FROM RDOC_DSPTCH_CHNNL_DTLS E "
+" WHERE E.DSPTCH_ID=A.DSPTCH_ID"
+" AND E.DSPTCH_SEQ=A.DSPTCH_SEQ"
+" AND E.CHNNL_ID='PRINT') end ) AS PRNT_DT,"

+" (CASE WHEN A.FAX_STS IN ('DSPTCH_FAX_LTR_SUCC') THEN 'Y' "
+" WHEN A.FAX_STS IN('DSPTCH_FAX_LTR_FAIL','NOT FOUND') THEN 'F'"
+" ELSE 'N' END) FAX_FLG, "
+" (CASE WHEN A.FAX_STS LIKE 'DSPTCH_FAX%' THEN (TO_CHAR(A.DSPTCH_END_TM,'RRRR/MM/DD')) ELSE NULL END) FAX_DT,"
+" (CASE WHEN A.EMAIL_STS IN ('DSPTCH_EMAIL_LTR_SUCC') THEN 'Y' "
+" WHEN A.EMAIL_STS IN('DSPTCH_EMAIL_LTR_FAIL','NOT FOUND') THEN 'F'"
+" ELSE 'N' END) EMAIL_FLG, "

+" (CASE WHEN A.VNDR_STS IN ('DSPTCH_VNDR_LTR_XFER_SUCC','VNDR_RECN_FL_UPD_SUCC','VNDR_RECN_FL_XFER_SUCC') THEN 'Y'"
+" WHEN A.VNDR_STS IN('DSPTCH_VNDR_LTR_FAIL','DSPTCH_VNDR_LTR_XFER_FAIL','VNDR_RECN_FL_UPD_FAIL','VNDR_RECN_FL_XFER_FAIL','NOT FOUND') "
+" THEN 'F' "
+" ELSE NULL END) VNDR_FLG,"

+" (CASE WHEN (SELECT TO_CHAR(E.VNDR_PRNT_DT,'RRRR/MM/DD') VNDR_PRNT_DT FROM RDOC_DSPTCH_PRGS E"
+" WHERE E.DSPTCH_ID=A.DSPTCH_ID"
+" AND E.DSPTCH_SEQ=A.DSPTCH_SEQ) IS NOT NULL THEN"
+" (SELECT TO_CHAR(E.VNDR_PRNT_DT,'RRRR/MM/DD') VNDR_PRNT_DT FROM RDOC_DSPTCH_PRGS E"
+" WHERE E.DSPTCH_ID=A.DSPTCH_ID"
+" AND E.DSPTCH_SEQ=A.DSPTCH_SEQ)"
+" when ((SELECT TO_CHAR(E.VNDR_PRNT_DT,'RRRR/MM/DD') VNDR_PRNT_DT FROM RDOC_DSPTCH_PRGS E"
+" WHERE E.DSPTCH_ID=A.DSPTCH_ID AND "
+" E.DSPTCH_SEQ=A.DSPTCH_SEQ) IS NULL and "
+" A.VNDR_STS IN('DSPTCH_VNDR_LTR_FAIL','DSPTCH_VNDR_LTR_XFER_FAIL','VNDR_RECN_FL_UPD_FAIL','VNDR_RECN_FL_XFER_FAIL','NOT FOUND')"
+" )"
+" then"
+" (SELECT TO_CHAR(E.DSPTCH_END_TM,'RRRR/MM/DD') PRNT_DT FROM RDOC_DSPTCH_CHNNL_DTLS E"
+" WHERE E.DSPTCH_ID=A.DSPTCH_ID AND E.DSPTCH_SEQ=A.DSPTCH_SEQ "
+" AND E.CHNNL_ID='FTP') END) AS VNDR_PRNT_DT, "

+" (CASE WHEN A.EMAIL_STS LIKE 'DSPTCH_EMAIL%' THEN (TO_CHAR(A.DSPTCH_END_TM,'RRRR/MM/DD')) ELSE NULL END) EMAIL_DT,"
+" A.QC_USR QUAL_CHCK_USR, "
+" (CASE WHEN A.DSPTCH_STS='DSPTCH_LTR_XCLD' THEN"
+" (SELECT F.CRT_BY FROM RDOC_CMNTS F WHERE F.CMNT_ID=A.DSPTCH_CMNT_ID)"
+" WHEN A.QC_STS IN ('QLTY_CHCK_LTR_XCLD','QLTY_CHCK_LTR_RJCT') THEN NVL(A.QC_USR,NULL)"
+" END ) "
+" XCLD_USR,"
+" (CASE WHEN A.DSPTCH_STS='DSPTCH_LTR_XCLD' THEN 'Y' ELSE 'N' END) DSPTCH_XCLD, "
+" (SELECT trim(replace (F.cmnt_dtls,chr(10),' ')) FROM RDOC_CMNTS F WHERE F.CMNT_ID=A.DSPTCH_CMNT_ID) DSPTCH_XCLD_CMNT"
+" FROM RDOC_LTR_SMRY_RRCT A,"
+" RDOC_RQST_GEN RRG,"
+" RDOC_QC_PRGS RQP "
+" WHERE A.XFER_UNT_ID ='RRCT'"
+" AND RRG.RQST_ID=A.RQST_ID"
+" AND RQP.BTCH_ID=RRG.BTCH_ID"
+" AND RQP.LTR_NAME=RRG.LTR_NAME"
+" AND RQP.LTR_TYPE=RRG.LTR_TYPE"
+" AND A.RQST_ID =?";
 
Santosh Kumar Nayak
Ranch Hand
Posts: 147
Eclipse IDE IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The above piece of code also gives ORA:00908 Missing NULL keyword Error.
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Print out the SQL statement before you execute it.
Copy and paste it into some SQL IDE and format it properly and see if it executes.

That lot is too much for us to trawl through looking for what is possibly a simple missing NULL or misplaced bracket.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic