ORA-00923 - Dynamic query in Oracle

[DettCom]

Hello all,

I am developing an app that can work off both MS-SQL and Oracle databases. The
problem has surfaced where I am now testing in Oracle and a specific piece of
code that references a query that is stored in the database is cause the
following error:

ODBC Error Code = S1000 (General error)


[Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected


Here is the section of code where it is breaking:

<cfquery name="variable" datasource="#reports.report_DNS#">
#preserveSingleQuotes(reports.report_var_query)#
<cfif #reports.search_number# neq "">


<cfif #reports.report_var_where# eq "">
WHERE #reports.report_var_where#
<cfelse>
WHERE
</cfif>
#reports.report_join_left# #reports.operator#
'#evaluate("group.#reports.report_join_right#")#'


<cfloop query="get_where">
AND #where_left# #where_operator# <cfif #URL.excel# eq
'YES'>'#evaluate("URL.#where_right#")#'<cfelse>'#e valuate("form.#where_right#")#
'</cfif>
</cfloop>
</cfif>
#preserveSingleQuotes(reports.report_var_group)#

</cfquery>


------------------
#reports.report_var_query# is where the query is plugged in and I have tried
running it in SQLplus, but is still gives me the same error. Here is the value
that is stored in the database:

SELECT poc, cdr_month+' '+cdr_year AS date, hcode, cell, calling_card, ldist,
tfr, atm, fra, dts_mci, dts_spr, dts_ver FROM cdr



It worked just fine in MS-SQL, but in Oracle it doesn't. Would the query above
work in Oracle 9i?

Thanks!!!

[philh]

Is "date" a reserved word in Oracle? If so, it may be pickier than the other DB flavors.

[JMGibson3]

I don't know for sure if +'s work also, but I do know (just the other day in
fact) I had to change my &'s from an access query into double Bars, ||, for
Oracle concatenation. Hate to say it, but you may need to starting putting in
datasource specific logic.

[DettCom]

Thanks guy for the unbelievable response time!!!

both of you hit it, by renaming "date", it triggered another error -
ORA-01722: invalid number. I think that error has something to do with +' '+.

Do you guys know how to do that in an oracle select statement?

Thanks again!

[DettCom]

I'm sorry, I didn't see the "||" reference. That worked perfectly!!!!

[DettCom]

Do you know what the Oracle equivalent would be for sql's ISNULL(variable, '') would be? Thanks

[JMGibson3]

WHERE colname IS NULL,

but you really oughta pick up a manual or Google up a good site because you'll
be running across many more differences. Oracle isn't necessarily that
different from the others but it's sounding like MS-SQL is.

[mxstu]

DettCom - I believe Oracle's version of ISNULL() is NVL(). I'm not 100% sure, but you may be able to use the COALESCE() function, in both databases.