Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-25
16
Medium Priority
?
338 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 1000 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
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 1000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
What You Need to Know when Searching for a Webhost Provider
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

963 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