Typed CFQUERY vs passed variable CFQUERY

Hi all,

I have an issue with a query I am trying to create and run. The query is compiled based on various criteria and saved into a variable. That variable is then passed to a CFQUERY like this:

<cfquery datasource="#datasource#" name="GetReportData">
    <cfoutput>#qryFull#</cfoutput>
</cfquery>

When run, I get the error "Incorrect syntax near ','." The funny thing is that the syntax is correct. when I output the variable I get the code included at the bottom of this message. When I copy and past that exactly in Query Analyser and run it against the same MSSQL database ColdFusion is using, it returns the expected results. Even when I copy and paste it into the ColdFusion page in place of the variable, it returns the expected results.

So the issue seems to be in the difference between how ColdFusion reads the text and reads the variable containing the same text.

Any ideas? See output query below:
SELECT patient.patientAccount AS patientpatientAccount, 
	CASE WHEN patient.companyName IS NULL OR LTRIM(RTRIM(patient.companyName)) = '' 
		THEN patient.lastName + ', ' + patient.firstName 
		ELSE patient.companyName 
		END AS patientpayeeName, 
	patient.address AS patientaddress, 
	patient.city AS patientcity, 
	patient.state AS patientstate, 
	patient.zip AS patientzip, 
	patient.phone AS patientphone, 
	transactions.amount AS transactionsamount, 
	transactions.transDate AS transactionstransDate, 
	CASE WHEN patient.companyName IS NULL OR LTRIM(RTRIM(patient.companyName)) = '' 
		THEN patient.lastName + ', ' + patient.firstName 
		ELSE patient.companyName 
		END AS patientpayeeName, 
	patient.dateOfBirth AS patientdateOfBirth, 
	doctor.businessName AS doctorbusinessName, 
	transactions.date_SentToCollections AS transactionsdate_SentToCollections 
FROM transactions WITH (NOLOCK) 
	LEFT JOIN patient WITH (NOLOCK) ON patient.patientID = transactions.patientID 
	LEFT JOIN doctor WITH (NOLOCK) ON doctor.doctorID = transactions.doctorID 
WHERE transactions.collectorID = 46 
	AND transactions.collectorExported = 0 
	AND transactions.active = 0 
ORDER BY transactions.transDate

Open in new window

LVL 4
msfletchAsked:
Who is Participating?
 
SidFishesCommented:
it's the lines

THEN patient.lastName + ', ' + patient.firstName

you can either build the name in cf

<cfset name = q.lastname & ", " & q.firstname>

or do something like

I'm not 100% sure that will work since I don't have tables to test it against...but testing it threw a DB error so I think it cleared the parsing error

<cfsavecontent variable="x">
SELECT patient.patientAccount AS patientpatientAccount, 
        CASE WHEN patient.companyName IS NULL OR LTRIM(RTRIM(patient.companyName)) = '' 
                THEN patient.lastName + '~' + patient.firstName 
                ELSE patient.companyName 
                END AS patientpayeeName, 
        patient.address AS patientaddress, 
        patient.city AS patientcity, 
        patient.state AS patientstate, 
        patient.zip AS patientzip, 
        patient.phone AS patientphone, 
        transactions.amount AS transactionsamount, 
        transactions.transDate AS transactionstransDate, 
        CASE WHEN patient.companyName IS NULL OR LTRIM(RTRIM(patient.companyName)) = '' 
                THEN patient.lastName + '~' + patient.firstName 
                ELSE patient.companyName 
                END AS patientpayeeName, 
        patient.dateOfBirth AS patientdateOfBirth, 
        doctor.businessName AS doctorbusinessName, 
        transactions.date_SentToCollections AS transactionsdate_SentToCollections 
FROM transactions WITH (NOLOCK) 
        LEFT JOIN patient WITH (NOLOCK) ON patient.patientID = transactions.patientID 
        LEFT JOIN doctor WITH (NOLOCK) ON doctor.doctorID = transactions.doctorID 
WHERE transactions.collectorID = 46 
        AND transactions.collectorExported = 0 
        AND transactions.active = 0 
ORDER BY transactions.transDate
</cfsavecontent>
 
 
 <cfquery datasource="kdata" name="GetReportData"> 
   #evaluate(de(replace(x, "'~'", ", ", "ALL")))#
 </cfquery> 

Open in new window

0
 
SidFishesCommented:
try using

#evaluate("#de(qryFull)#")#
0
 
msfletchAuthor Commented:
SidFishes,

Using either:

#evaluate("#de(qryFull)#")#

or

#evaluate(DE(qryFull))#

... returns the same "Incorrect syntax near ','" error.

Any other thoughts?
0
 
msfletchAuthor Commented:
Thanks SidFishes!

Coming through again. That worked. So it seems as though CF has an issue passing single quotation marks from variable directly to a query. This was the same reason I was having trouble passing static variables through the query (see: QID 23462296) because they were also being passed to the cfquery through a variable.

So to recap, the final solution I used was (see attached):
<cfquery datasource="datasource" name="GetReportData">
<cfoutput>
    #evaluate(de(replace(qryFull, "', '", ", ", "ALL")))#
</cfoutput>
</cfquery>

Open in new window

0
 
msfletchAuthor Commented:
Thanks again!
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.

All Courses

From novice to tech pro — start learning today.