Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-25
16
Medium Priority
?
336 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 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

670 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