Trouble with # symbol in native SQL

[pr0ph3t]

I'm trying to write Native SQL on a Report Studio report against a DB2 database that contains a # (hash, sharp, number, pound) symbol. My Query looks like this:

Code Select Expand

select job_# from test.jobs

I'm getting an error:

Code Select Expand


Parsing error before or near position: 28 of: "select job_# from test.jobs"

QE-DEF-0299 Expanding: # from test.jobs from position: 11, to position: 28, text fragment: # from test.jobs.
RSV-SRV-0042 Trace back:
RSReportService.cpp(756): QEExceptionExtended: CCL_CAUGHT: RSReportService::process()
RSReportServiceMethod.cpp(263): QEExceptionExtended: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_Request
RSASyncExecutionThread.cpp(828): QEExceptionExtended: RSASyncExecutionThread::checkException
RSASyncExecutionThread.cpp(277): QEExceptionExtended: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_Request
RSASyncExecutionThread.cpp(884): QEExceptionExtended: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_Request
RSQueryMgr.cpp(1570): QEExceptionExtended: CCL_RETHROW: RSQueryMgr::executeRsapiCommand
QFSSession.cpp(1147): QEExceptionExtended: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSSession.cpp(1145): QEExceptionExtended: CCL_CAUGHT: QFSSession::ProcessDoRequest()
QFSSession.cpp(1102): QEExceptionExtended: CCL_RETHROW: QFSSession::ProcessDoRequest()
QFSConnection.cpp(1971): QEExceptionExtended: CCL_RETHROW: QFSConnection::DescribeDataSourceQuery
QFSQuery.cpp(569): QEExceptionExtended: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery
QFSQuery.cpp(569): QEExceptionExtended: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery
Source/QUYyError.cpp(254): QEExceptionExtended: CCL_THROW: QE


It seems the # symbol is being treated as a the beginning of a cognos macro. I've tried escaping it using:

Code Select Expand


##
\#
[#]


None of these work. Is there any way to escape a # symbol?

[calson33]

I'm not that familiar with MySQL but in Oracle for things like that they need to be quoted.
e.g.
select "job_#" from test.jobs

[pr0ph3t]

Thanks, Calson. I'm not using MySQL, I'm using DB2. Sorry for the confusion in my post. I'll reword that part. I did try double-quoting the column names and I got a different error:

Code Select Expand


UDA-SQL-0043 The underlying database detected an error during processing the SQL request.
[IBM][CLI Driver][DB2] SQL0206N  "job_#" is not valid in the context where it is used.  SQLSTATE=42703


If I double quote any column name, I get the same error. If I use a column that doesn't have a # sign, like this:

Code Select Expand


select job_name from test.jobs


everything works fine.

[calson33]

uh.. Sorry - totally mis read the database.. But I don't have much experience with DB2 either.  :-\

I can think of 2 other possible solutions:
Fully qualify the column, with quotes:

select test.jobs."job_#" from test.jobs

Or, put a view on the table and change the name from job_# to something like job_nbr.

That's all I can come up with..
Maybe some one else here has more experience with DB2 that can offer better answers.

[pr0ph3t]

Thanks, Calson. Tried those things. Fully qualifying the column name works unless I use quotes or a column name with a # in it. I typically have problems with double-quotes in Cognos. And I'm convinced the # symbol is being interpreted as the start of a macro. If there was only some way to disable macros in a Native Query or to escape the # symbol somehow.

I wish I had access to add a view or change column names. Unfortunately, I only have query access to this database.

BTW, all these things (double quotes or columns with #) work directly against against DB2, command-line or GUI.

Any other ideas to try?

[calson33]

From what I understand (I am pretty new to Cognos), double quotes are for escaping reserved characters.

Maybe if you try using pass-through instead of native with the double quotes. That's nothing more than a guess though.

Wish I knew more!

[Lynn]

I have DB2 and there are column names with # in them. I use double quotes with no problem when I'm forced to shame myself and use hand-coded SQL. I don't need to make it pass through.

Did you try "JOB_#" instead of lowercase?

Also, try using that column from the package and then generate the native SQL. That should show you how to reference it.


Sent from my iPhone using Tapatalk

[pr0ph3t]

Thanks, Lynn! Double-Quoting and using uppercase worked.

[Lynn]

Hooray! Drinks all around!!

[MFGF]

Mine's a Defiant Porter! :-)