Oninit cdi / Archive
Talk to an expert →

Run SQL

Posted in 2008

Topics: SQL Development & Query Writing, Platform-Specific Issues, Internationalization & Character Sets

FRANK — — source: IIUG Forums & Mailing Lists

Folks, If you run a SELECT with nonexisted column , it show the following 217 error, select item_int from order where order_int = 563; 217: Column (item_int) not found in any table in the query (or SLV is undefined). But, if you embed it as a subquery(below), the whole query can be run successfully.... select * From lin_file_info where item_int in (select item_int from order where order_int = 563 ); Any comments? Thanks, Frank Program Name: onstat Build Version: 11.10.UC2 Build Number: N158 Build Host: vach Build OS: AIX 5.3 Build Date: Thu Oct 25 22:56:40 CDT 2007 GLS Version: glslib-4.50.UC2

↪ replying to FRANK
Clifton Bean — — source: IIUG Forums & Mailing Lists

"Will I dream?" SAL asks Dr. Chandra in 2010: Space Odyssey Two by Arthur C. Clark. Chandra replies "Of course you will. All intelligent beings dream. Nobody knows why... " Take care. Clifton M. Bean Informix DBA / AIX System Admin Currency Technics & Metrics Main (972) 812-1411 x244 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK Sent: Wednesday, July 23, 2008 11:15 AM To: ids@iiug.org Subject: Run SQL [12874] Folks, If you run a SELECT with nonexisted column , it show the following 217 error, select item_int from order where order_int = 563; 217: Column (item_int) not found in any table in the query (or SLV is undefined). But, if you embed it as a subquery(below), the whole query can be run successfully.... select * From lin_file_info where item_int in (select item_int from order where order_int = 563 ); Any comments? Thanks, Frank Program Name: onstat Build Version: 11.10.UC2 Build Number: N158 Build Host: vach Build OS: AIX 5.3 Build Date: Thu Oct 25 22:56:40 CDT 2007 GLS Version: glslib-4.50.UC2 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Clifton Bean
Stuart McCann — — source: IIUG Forums & Mailing Lists

The reason the query runs is because the item_int column must exists in the lin_file_info table and is then used in the nested select. It's a bit like saying:- select * From lin_file_info a where 1 in (select 1 from order where order_int = 563 ); However, if you were to fully qualify the tables in the query like this, select * From lin_file_info a where a.item_int in (select b.item_int from order b where order_int = 563 ); You will get the -217 error also. Stuart McCann Integrated Spatial Services Unit Information Communication & Technology Department of Lands, Bathurst Phone: (02) 63328285 stuart.mccann@lands.nsw.gov.au -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Clifton Bean Sent: Thursday, 24 July 2008 2:33 AM To: ids@iiug.org Subject: RE: Run SQL [12875] "Will I dream?" SAL asks Dr. Chandra in 2010: Space Odyssey Two by Arthur C. Clark. Chandra replies "Of course you will. All intelligent beings dream. Nobody knows why... " Take care. Clifton M. Bean Informix DBA / AIX System Admin Currency Technics & Metrics Main (972) 812-1411 x244 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK Sent: Wednesday, July 23, 2008 11:15 AM To: ids@iiug.org Subject: Run SQL [12874] Folks, If you run a SELECT with nonexisted column , it show the following 217 error, select item_int from order where order_int = 563; 217: Column (item_int) not found in any table in the query (or SLV is undefined). But, if you embed it as a subquery(below), the whole query can be run successfully.... select * From lin_file_info where item_int in (select item_int from order where order_int = 563 ); Any comments? Thanks, Frank Program Name: onstat Build Version: 11.10.UC2 Build Number: N158 Build Host: vach Build OS: AIX 5.3 Build Date: Thu Oct 25 22:56:40 CDT 2007 GLS Version: glslib-4.50.UC2 ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Department of Lands. This email message has been swept by MIMEsweeper for the presence of computer viruses. *************************************************************** Please consider the environment before printing this email.

Related threads

Glossary