• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

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

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
sigkappu
Asked:
sigkappu
  • 7
  • 5
  • 2
  • +1
2 Solutions
 
_agx_Commented:
Try escaping the # ie By using two ## instead of one

<cfquery ...>
SELECT item##
FROM "PUB"."item"
</cfquery
0
 
erikTsomikSystem Architect, CF programmer Commented:
ok. I do not have problem with having # in column name

try exactly like this
 
SELECT item# as itemnumber
FROM PUB.item
0
 
sigkappuAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
_agx_Commented:
     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
 
sigkappuAuthor Commented:
aqx: same error message with it that way
0
 
_agx_Commented:
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
 
_agx_Commented:
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
 
erikTsomikSystem Architect, CF programmer Commented:
try using  3 # pounds ###
0
 
sigkappuAuthor Commented:
using 3# gives the same error as if it only 1#
0
 
_agx_Commented:
sigkappu - did you see the earlier questions?

0
 
sigkappuAuthor Commented:
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
 
gdemariaCommented:
(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
 
sigkappuAuthor Commented:
gdemaria: I did try that one. It didn't work either.  
0
 
gdemariaCommented:

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
 
_agx_Commented:
Are you sure the column name is exactly "item#" ?  Do a SELECT * and dump the column list variable

      <cfdump var="|#yourQueryName.columnList#|">
0
 
_agx_Commented:
this database can take quotes in the select

Good idea
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now