Db2 Sql Error Code 811
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About
Db2 Sql Error Code 104
Us Learn more about Stack Overflow the company Business Learn more about hiring db2 sql error code 803 developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the db2 sql error code 204 Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up CASE Clause on select clause throwing http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n811.html 'SQLCODE=-811, SQLSTATE=21000' Error up vote 0 down vote favorite This query is very well working in Oracle. But it is not working in DB2. It is throwing DB2 SQL Error: SQLCODE=-811, SQLSTATE=21000, SQLERRMC=null, DRIVER=3.61.65 error when the sub query under THEN clause is returning 2 rows. However, my question is why would it execute in the first place as my WHEN clause turns to be false always. http://stackoverflow.com/questions/26072235/case-clause-on-select-clause-throwing-sqlcode-811-sqlstate-21000-error SELECT CASE WHEN (SELECT COUNT(1) FROM STOP ST, FACILITY FAC WHERE ST.FACILITY_ID = FAC.FACILITY_ID AND FAC.IS_DOCK_SCHED_FAC=1 AND ST.SHIPMENT_ID = 2779) = 1 THEN (SELECT ST.FACILITY_ALIAS_ID FROM STOP ST, FACILITY FAC WHERE ST.FACILITY_ID = FAC.FACILITY_ID AND FAC.IS_DOCK_SCHED_FAC=1 AND ST.SHIPMENT_ID = 2779 ) ELSE NULL END STAPPFAC FROM SHIPMENT SHIPMENT WHERE SHIPMENT.SHIPMENT_ID IN (2779); sql db2 case share|improve this question edited Sep 27 '14 at 8:20 marc_s 451k938641029 asked Sep 27 '14 at 7:07 user2661315 111 Which version of db2 are you using? –Joachim Isaksson Sep 27 '14 at 8:17 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote The SQL standard does not require short cut evaluation (ie evaluation order of the parts of the CASE statement). Oracle chooses to specify shortcut evaluation, however DB2 seems to not do that. Rewriting your query a little for DB2 (8.1+ only for FETCH in subqueries) should allow it to run (unsure if you need the added ORDER BY and don't have DB2 to test on at the moment) SELECT CASE WHEN (SELECT COUNT(1) FROM STOP ST, FACILITY FAC WHERE ST.FACILITY_ID = FAC.FACILITY_ID AND FAC.IS_DOCK_SCHED_FAC=1 AND ST.SHIPMENT_ID = 2779) = 1 THEN (SELECT ST.FACILITY_ALIAS
MainFrame >> DB2 Suggest New Category What is sqlcode -811? Question Posted / guest 7 Answers 43473 Views CTS, TCS, I also Faced E-Mail http://www.allinterview.com/showanswers/6301/what-is-sqlcode-811.html Answers Answers were Sorted based on User's Feedback Answer / jdriley Is the SQLCODE returned when your singleton select statement returns/retrieves more than one row of data. Is This Answer Correct ? 63 Yes 7 No Answer / kshitij Without the use of cursor fetching two or more rows. Is This Answer Correct ? 49 Yes 6 No Answer / rami reddy If single select returns db2 sql more than one row then you get SQLCODE = -811 Is This Answer Correct ? 34 Yes 3 No Answer / bharani We get error -811, when 'WHERE' criteria in singleton query matched with multiple rows of a table, it can only fetch and process one row at a time. Only way to avoid this error by using cursor concepts. Is This Answer Correct ? 26 Yes 1 No db2 sql error Answer / toms k jose multiple rows retrieved. Is This Answer Correct ? 7 Yes 0 No Answer / bruno Registro duplicado na tabela DB2 Is This Answer Correct ? 1 Yes 4 No Answer / marek The one correct answer is: "Look at DB2 doc." Who remember all sql error codes? Is This Answer Correct ? 19 Yes 37 No Post New Answer Categories COBOL (884)JCL (728)CICS (459)DB2 (684)IMS (80)IDMS (42)Natural (43)ADABAS (9)REXX (23)Assembler (31)CLIST (6)QMF (30)MVS (12)OS390 (8)OS 2 (6)VSAM (177)QSAM (4)Sysplex (5)IBM MainFrame AllOther (160) More DB2 Interview Questions What is meant by repeatable read? 4 Answers Is it mandatory to use DCLGEN? If not, why would you use it at all? 2 Answers wht happens if we close cursor with cursor with hold option? 3 Answers Valtech, Hi Everyone... Under the Logical files, when am working on multiple rec format logical file, I have joined two pf's using the multiple record format logical file concept, but when i run queried the LF only the first mentioned pf records are reflecting and I am not able to see any of the field records specified under the second pf. Kindly let me know whats the reason behind this. Bel