Solved

How do I select a database column that contains a # in the column name?

Posted on 2011-02-25
16
316 Views
Last Modified: 2013-12-24
I am trying to run the following query on a Progress database.  

SELECT item#
FROM "PUB"."item"

However it gives me an error. I think it is because of the # in the column name.

I have tried the following code which I know works if there is an illegal character in the name like a space but it doesn't work either.

SELECT [item#] as itemnumber
FROM "PUB"."item"

I do not have the luxury of renaming the fields in the table so I need to figure out how to structure the query with that column name.

thanks for your help.
0
Comment
Question by:sigkappu
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
ID: 34982999
Try escaping the # ie By using two ## instead of one

<cfquery ...>
SELECT item##
FROM "PUB"."item"
</cfquery
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 34983008
ok. I do not have problem with having # in column name

try exactly like this
 
SELECT item# as itemnumber
FROM PUB.item
0
 

Author Comment

by:sigkappu
ID: 34983103
aqx: when I use the ## like you suggested I see the following error message:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][DataDirect][ODBC OPENEDGE driver][OPENEDGE]Syntax error in SQL statement at or about "# FROM "PUB"."item"" (10713)

erikTsomik:  when I use your code I see the following error message:

invalid CFML construct found on line 11 at column 21.
ColdFusion was looking at the following text:

itemnumber

0
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
LVL 52

Expert Comment

by:_agx_
ID: 34983232
     FROM "PUB"."item"

I assumed that that syntax was worked for you with normal columns. But maybe not. Try instead

SELECT item##
FROM PUB.item

0
 

Author Comment

by:sigkappu
ID: 34983349
aqx: same error message with it that way
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34983400
Does it work if you run it against the progress db directly? Does the same syntax work with a column *without* a # sign in it?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34983532
Using two ## just tells coldFusion it's a literal pound sign, not a variable.  What's actually sent to the db is only one #.  I don't use Progress but if you say this IS valid syntax

      SELECT [item#] as itemnumber
      FROM "PUB"."item"

Then I see no reason this would NOT work too

      SELECT [item##] as itemnumber
      FROM "PUB"."item"

... or some facsimile of that

ie
      SELECT [item##] as itemnumber
      FROM PUB.item
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 34983599
try using  3 # pounds ###
0
 

Author Comment

by:sigkappu
ID: 34983621
using 3# gives the same error as if it only 1#
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34983770
sigkappu - did you see the earlier questions?

0
 

Author Comment

by:sigkappu
ID: 34983830
Does it work if you run it against the progress db directly?   - I don't have access to run it directly against the database today.  I will need to try it on Monday.

Does the same syntax work with a column *without* a # sign in it? - It does work correctly if the column doesn't have a # sign in it.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34983893
(no points)

did you try this version from agx above?  This is the should most likely work...

      SELECT [item##] as itemnumber
      FROM PUB.item

This one has two #s and the alias on the column
0
 

Author Comment

by:sigkappu
ID: 34983936
gdemaria: I did try that one. It didn't work either.  
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
ID: 34984117

Doing some googling, I see this database can take quotes in the select, can you try this..

SELECT "item##"  as itemnumber
 FROM PUB.item


If it doesn't work can you show the full error message, it may not be the #s
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34984140
Are you sure the column name is exactly "item#" ?  Do a SELECT * and dump the column list variable

      <cfdump var="|#yourQueryName.columnList#|">
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34984147
this database can take quotes in the select

Good idea
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question