Db2 Sql Error Sqlcode=-811 Sqlstate=21000
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 Us Learn more about Stack Overflow the company Business
Db2 Sql Error Sqlcode 803 Sqlstate 23505
Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation
Db2 Sql Error Sqlcode 104 Sqlstate 42601
Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like db2 sql error sqlcode 206 sqlstate 42703 you, helping each other. Join them; it only takes a minute: Sign up CASE Clause on select clause throwing 'SQLCODE=-811, SQLSTATE=21000' Error up vote 0 down vote favorite This query is very well working in Oracle. But http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n811.html 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. 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 http://stackoverflow.com/questions/26072235/case-clause-on-select-clause-throwing-sqlcode-811-sqlstate-21000-error 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_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 ORDER BY ST.SHIPMENT_ID FETCH FIRST 1 ROWS ONLY ) ELSE NULL END STAPPFAC FROM SHIPMENT SHIPMENT WHERE SHIPMENT.SHIPMENT_ID IN (2779); share|improve this answer answered Sep 27 '14 at 9:05 Joachim Isaksson 116k11134170 Syntactically, the ORDER B
MainFrame >> DB2 Suggest New Category What is sqlcode -811? Question Posted / guest 7 Answers 43471 Views CTS, TCS, I also Faced E-Mail Answers Answers were Sorted based http://www.allinterview.com/showanswers/6301/what-is-sqlcode-811.html on User's Feedback Answer / jdriley Is the SQLCODE returned when your singleton http://www.dbforums.com/showthread.php?1682623-db2-global-variable 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 more than one row then you get SQLCODE = -811 db2 sql 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 Answer / toms k jose multiple rows retrieved. Is This Answer Correct ? 7 db2 sql error 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 image copy? 1 Answers Virtusa, Suppose we have a query for update update table1 set col1 = 'val1' where col2=(select .... from ...) suppose the subquery does not return any record, what will happen to update? 1 Answers IBM, Assuming that a site's standard is that pgm name = plan name, what is the easiest way to find out which Will precompile of an DB2-COBOL program bomb, if DB2 is down? 1 Answers if i made sme changes in sub pgm wht r the things to be done for this ? 1 Answers TCS, How to create a table using embedded sql? 1 Answers When do you specify the isolation level? How? 1 Answers What does the RUNSTATS Utility do? 4 Answers How to solve S0C7 abend with out using DISPlay in 10 mi
the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 3 of 3 Thread: db2 global variable Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 07-10-12,12:45 #1 sabari321 View Profile View Forum Posts Registered User Join Date May 2009 Posts 4 Unanswered: db2 global variable Deal All, Any body faced the issues like below while using global variables? db2 "CREATE OR REPLACE VARIABLE TEST.TABLES VARCHAR(40) DEFAULT((select tabname from syscat.tables where tabname like '%LEN'))" DB20000I The SQL command completed successfully. db2 "select TEST.TABLES from SYSIBM.DUAL" SQL0727N An error occurred during implicit system action type "10". Information returned for the error includes SQLCODE "-811", SQLSTATE "21000" and message tokens "". SQLSTATE=56098 I am using v9 and fix5, not sure whts wrong here, Regards, Sabari Reply With Quote 07-10-12,12:49 #2 sathyaram_s View Profile View Forum Posts Visit Homepage Super Moderator Join Date Aug 2001 Location UK Posts 4,650 Checking the error message -811 ........... SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. Explanation: One of the following caused the error: Execution of an embedded SELECT INTO or VALUES INTO statement resulted in a result table of more than one row. Execution of a scalar fullselect resulted in a result table of more than one row. Federated system users: this situation can be detected by federated server or by the data source. The statement cannot be processed. User response: Ensure that the statement contains the proper condition specifications. If it does, there may be a data problem that is causing more than one row to be returned when only one is expected. Federated system users: isolate the problem to the data source failing the request (refer to the Troubleshooting Guide to determine which data source is failing to process the SQL statement) and examine the selection criteria and data for that object. sqlcode: -811 sqlstate: 21000 Visit the new-look IDUG Website , register to gain access to the excellent content. Reply With Quote 07-10-12,