Solved

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

Posted on 2011-02-25
16
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

624 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