SQL As statement w/ delimiter

Trying to do some very basic SQL on an Informix 7.x box. What I know works is this for either a telnet session or an ODBC connection. Select size from item Select size as bob from item What doesn't work is Select 0 as size from item Select 0 as 'size' from item Select 0 as "size" from item I didn't create the table using a reserved word as a column name I know size is a reserved word. I'm a contractor in a shop that appears to have very limited knowledge and no desire to share information. I know the putz who created the table is long gone. I am a MS-SQL Server/VB developer so I know enough SQL to be dangerous in an Informix environment. Per the Informix documentation and my experience, I should be able to surround a reserved word with some sort of delimiter to use it as a column name. While I can code around this issue at the client, it would be a heck of a lot easier to get the recordset returned to me with a column named as I need it. I know that there are a ton of people out there a lot smarter than I am. Any thoughts?

Tom wrote: > Trying to do some very basic SQL on an Informix 7.x box. What I > know works is this for either a telnet session or an ODBC > connection. telnet? Using DB-Access to execute the SQL? > Select size from item > Select size as bob from item I'd expect those to work, of course. > What doesn't work is > > Select 0 as size from item > Select 0 as 'size' from item > Select 0 as "size" from item And I'd also expect all of those to work. What is the error you are getting? You should also be able to write 'SELECT 0 size FROM' item and variants on that theme. > I didn't create the table using a reserved word as a column name > I know size is a reserved word. The concept 'reserved word' has little significance in Informix's dialect of SQL. You can write: CREATE TABLE CREATE(null INTEGER NOT NULL, not INTEGER NOT NULL,integer DATE); This won't confuse the server; it will confuse everyone else. > I'm a contractor in a shop that appears to have very limited > knowledge and no desire to share information. > I know the putz who created the table is long gone. Bad luck. > I am a MS-SQL Server/VB developer so I know enough SQL to be > dangerous in an Informix environment. > Per the Informix documentation and my experience, I should be able > to surround a reserved word with some sort of delimiter to use it > as a column name. Yes, you can do that (use a delimited identifier) - by ensuring that the DELIMIDENT environment variable is set - the value doesn't matter (hence export DELIMIDENT=1 or equivalent is fine). Or you set it via SETNET32. Not recommended; usually not necessary either. However, it certainly works and enforces a discipline on you which is, in some respsects, good. Specifically, it forces you to use single quotes around strings and double quotes around delimited identifiers -- Informix normally permits both single and double quotes to surround strings. > While I can code around this issue at the client, it would be a > heck of a lot easier to get the recordset returned to me with a > column named as I need it. I wonder if the problem is in the upper-levels of the client-side code. You mention VB, so you're probably working on Windows. Which Informix driver are you using? [I'm treading on thin ice here; Windows is not my area of expertise.] Could it be that ADO or OLEDB or whatever places restrictions on the names of columns? I'm 99.9% sure that the ESQL/C and server don't care whether you use a column called size or not - but there might be a constraint being enforced higher up the call chain. Could you create a column called size in MS SQL Server? What notation would you need to use? > I know that there are a ton of people out there a lot smarter than I am. > > Any thoughts? Include more precise version information and platform information. Informix 7.x covers nearly a decade of software versions (8 years or more), so you could be using something incredibly archaic or something that was released in the last six months. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

On Mon, 13 Sep 2004 23:58:38 -0400, Tom wrote: As Jonathan suspects, the problem's not the database. I've tried all of your versions on IDS 9.4 and 7.31 and they all work just fine. There's something going on in your front-end. Art S. Kagel > Trying to do some very basic SQL on an Informix 7.x box. What I know works > is this for either a telnet session or an ODBC connection. > > Select size from item > Select size as bob from item> > What doesn't work is > > Select 0 as size from item > Select 0 as 'size' from item > Select 0 as "size" from item> > I didn't create the table using a reserved word as a column name I know size > is a reserved word. > I'm a contractor in a shop that appears to have very limited knowledge and > no desire to share information. > I know the putz who created the table is long gone. I am a MS-SQL Server/VB > developer so I know enough SQL to be dangerous in an Informix environment. > Per the Informix documentation and my experience, I should be able to > surround a reserved word with some sort of delimiter to use it as a column > name. > While I can code around this issue at the client, it would be a heck of a > lot easier to get the recordset returned to me with a column named as I need > it. > I know that there are a ton of people out there a lot smarter than I am. > > Any thoughts?