Search code examples
coldfusioncoldfusion-9

Comparing two character values in a Coldfusion cfquery

I have a database field which contains VARCHAR values like

XY23(CX Web)

AND I am writing a Query of Queries like

SELECT qPdfs.filename, qPdfs.code, qObjects.id
FROM qPdfs, qObjects
WHERE qPdfs.code = qObjects.code

OR like

SELECT qPdfs.filename, qPdfs.code, qObjects.id
FROM qPdfs, qObjects
WHERE <cfqueryparam value="#qObjects.code#" cfsqltype="CF_SQL_VARCHAR"> = <cfqueryparam value="#qPdfs.code#" cfsqltype="CF_SQL_VARCHAR">

But I am getting an error message like

XY23(CX Web) must be interpretable as a valid number in the current locale.

Any help?

Thank you

Solution

  • Are both of the database columns type varchar? If so, the QoQ is probably applying some implicit conversion based on the contents of the columns. If some of the codes are all numeric, the QoQ may be attempting to cast them to numbers internally before performing the comparison. Hence the error. cfqueryparam won't help here, because it can only be used on literals, not query columns. If a straight equality comparison ie ColName = ColName causes that error, try casting instead:

    WHERE CAST(qPdfs.code AS VARCHAR) = CAST(qObjects.code AS VARCHAR)

    NB: QoQ string comparisons are case sensitive, so you may want to convert the columns to upper/lower case first.