Db2 Sql Error Sqlcode=-153 Sqlstate=42908
Contents |
SQLSTATE 42908 z/os Fixes are available DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and db2 sql error sqlcode 206 sqlstate 42703 Windows DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows DB2 Version 9.7 Fix db2 sql error sqlcode 407 sqlstate 23502 Pack 9 for Linux, UNIX, and Windows DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows DB2 Version 9.7 Fix Pack
Db2 Sql Error Sqlcode 440 Sqlstate 42884
10 for Linux, UNIX, and Windows Subscribe You can track all active APARs for this component. APAR status Closed as fixed if next. Error description Declaring a PL/SQL static cursor may incorrectly return SQLCODE -153, SQLSTATE 42908. This http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n153.html happens if the query includes an ORDER BY column that is not in the select list and the select list includes an expression with the same column name. For example: create table T ( col1 int ) DB20000I The SQL command completed successfully. create procedure P is CURSOR cur1 IS SELECT col1+1 col1 from T order by T.col1; -- begin return 0; -- end SQL0153N The statement does not include a required column list. LINE NUMBER=3. http://www.ibm.com/support/docview.wss?uid=swg1IC74423 SQLSTATE=42908 Local fix Change the column name of the expression in the select list. Problem summary **************************************************************** * USERS AFFECTED: * * DB2LUW * **************************************************************** * PROBLEM DESCRIPTION: * * Declaring a PL/SQL static cursor may incorrectly return * * SQLCODE * * -153, SQLSTATE 42908. This happens if the query includes an * * ORDER BY column that is not in the select list and the * * select * * list includes an expression with the same column name. For * * example: * * * * create table T ( col1 int ) * * DB20000I The SQL command completed successfully. * * * * create procedure P * * is * * CURSOR cur1 IS * * SELECT col1+1 col1 from T order by T.col1; -- * * begin * * return 0; -- * * end * * SQL0153N The statement does not include a required column * * list. LINE NUMBER=3. SQLSTATE=42908 * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 9.7 fixpack 5 * **************************************************************** Problem conclusion Temporary fix Change the column name of the expression in the select list. Comments APAR Information APAR numberIC74423 Reported component nameDB2 FOR LUW Reported component IDDB2FORLUW Reported release970 StatusCLOSED FIN PENoPE HIPERNoHIPER Special AttentionNoSpecatt Submitted date2011-02-11 Closed date2012-02-01 Last modified date2012-02-01 APAR is sysrouted FROM one or more of the following: APAR is sysrou
question and get tips & solutions from a community of 418,478 IT Pros & Developers. It's quick & easy. Problem When Creating Views https://bytes.com/topic/db2/answers/185621-problem-when-creating-views P: n/a pankaj_wolfhunter hi, The Query to Create View is: CREATE VIEW PROJECT_vw http://errors.it2agri.org/page/430/ AS \ SELECT \ PROJNO \ ,PROJNAME \ ,DEPTNO \ ,RESPEMP \ ,PRSTAFF \ ,CHAR(SUBSTR(monthname(PRSTDATE),1,3))||' '||RTRIM(CHAR(day(PRSTDATE)))||' '||LTRIM(RTRIM(CHAR(year(PRSTDATE)))) \ ,CHAR(SUBSTR(monthname(PRENDATE),1,3))||' '||RTRIM(CHAR(day(PRENDATE)))||' '||LTRIM(RTRIM(CHAR(year(PRENDATE)))) \ ,MAJPROJ \ FROM PROJECT But it gives the following error: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. db2 sql During SQL processing it returned: SQL0153N The statement does not include a required column list. SQLSTATE=42908 What is wrong with the query? May be its giving problem with the concatenation part but i want the data into the views to be like that only. What is the correct query to do this? Help will be appreciated TIA Nov 12 '05 #1 Post Reply db2 sql error Share this Question 4 Replies P: n/a Tonkuma Following columns have no name. ,CHAR(SUBSTR(monthname(PRSTDATE),1,3))||' '||RTRIM(CHAR(day(PRSTDATE)))||' '||LTRIM(RTRIM(CHAR(year(PRSTDATE)))) \ ,CHAR(SUBSTR(monthname(PRENDATE),1,3))||' '||RTRIM(CHAR(day(PRENDATE)))||' '||LTRIM(RTRIM(CHAR(year(PRENDATE)))) \ Add column list. CREATE VIEW PROJECT_vw(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF,PRSTDATE, RENDATE) AS \ Nov 12 '05 #2 P: n/a Tonkuma Sorry, I forgot MAJPROJ. CREATE VIEW PROJECT_vw(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF,PRSTDATE, RENDATE, MAJPROJ) AS \ Nov 12 '05 #3 P: n/a pankaj_wolfhunter It worked. Thanx for the reply. Nov 12 '05 #4 P: n/a Knut Stolze Tonkuma wrote: Following columns have no name. ,CHAR(SUBSTR(monthname(PRSTDATE),1,3))||' '||RTRIM(CHAR(day(PRSTDATE)))||' '||LTRIM(RTRIM(CHAR(year(PRSTDATE)))) \ ,CHAR(SUBSTR(monthname(PRENDATE),1,3))||' '||RTRIM(CHAR(day(PRENDATE)))||' '||LTRIM(RTRIM(CHAR(year(PRENDATE)))) \ Add column list. CREATE VIEW PROJECT_vw(PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF,PRSTDATE, RENDATE) AS \ Or give the columns an explicit name in the select list: SELECT col1, col2, col3 + col4 AS myName FROM ... -- Knut Stolze DB2 Information Integration Development IBM Germany Nov 12 '05 #5 This discussion thread is closed Start new discussion Replies have been disabled for this discussion. Similar topics Re: PROBLEM WITH DISCOVERER JOINS (ADP) form problem: "This Recordset is not updatable" Problem with MFC extension DLL, threads, and .Net loader lock MVC delegates problem... PostgreSQL 7.4.2 initdb problem 7.4.2 initdb probl
the output SQLDA (select list). SQL16030N The value "value" returned by the name expression of a computed attribute constructor is in the namespace SQL30083N Attempt to change password for user id "uid" failed with security reason "reason-code" ("reason-string"). SQL30082N Security processing failed with reason "reason-code" ("reason-string"). Archives February 2010 November 2009 October 2009 September 2009 August 2009 February 2009 October 2008 August 2008 July 2008 Categories db2errors (RSS) (2,253) Meta RSS 2.0 Feed Atom Feed Comments RSS Feed Log in Valid XHTML Tag Cloud access backup bind codepage constraint container cursor das datalink datatype datetime dbcfg DPF duplicate exceeded export full function historyfile hostvariable I/O import index invalid LDAP LOAD logs media memory messagefile partitioningkey partitioningmaps path precompiler reorg restore rollforward runstats satellite syntax tablespace toolong tooshort utility view SQL0157N "name" is not allowed in a FOREIGN KEY clause because it does not identify a base table August 19, 2008 Explanation: The object "name" was identified in a FOREIGN KEY clause of a CREATE or ALTER TABLE statement. A FOREIGN KEY clause must identify a base table. The statement cannot be processed. The specified table is not created or altered. User Response: Correct the statement to specify a base table name in the FOREIGN KEY clause. If an alias name was supplied, ensure that the alias resolves to a base table. sqlcode : -157 sqlstate : 42810 tags: foreignkey posted in db2errors by admin | No Comments SQL0156N The name used for this operation is not a table August 19, 2008 Explanation: The SQL statements ALTER TABLE, DROP TABLE, SET INTEGRITY, CREATE TRIGGER, CREATE INDEX, LOCK TABLE, and RENAME TABLE are applicable only to tables, not to views. The RUNSTATS and LOAD utilities are also applicable only to tables, not to views. Federated system users: Some utilities and statements are not supported in a federated environment. See the Administration Guide for more information. The statement or utility cannot be processed. User Response: Verify that the correct table