Query Of Queries runtime error in CFMX 7

[singhpk]

Hi,

I am migrating from CF 5 to MX 7. All the queries are working fine in the old
version. However when I try the same code in MX7 I get errors. An example is
posted below.

Error Occurred While Processing Request
Query Of Queries runtime error.
Comparison Exception: While executing "<>"
Unsupported Type Comparison Exception: Comparator operator "<>" does not
support comparison between following types:
Left hand side expression type = "INTEGER".
Right hand side expression type = "STRING".

[paross1]

You may need to CAST() one or both of the selected columns to the appropriate
matching data type in the original query before attempting to compare them
within a Q-of-Q. CF 5 (and 4.5) seemed to be much more tolerant of implicit
type conversion within queries than is CF 6 or 7. I'm sure that it is the price
we pay for JDBC.

Phil

[singhpk]

I have used CAST operator and now the whole query fails. I have no clue what's happening. It is working perfectly fine in CF 5.

[paross1]

How about supplying a sample of your query and Q-of-Q? Perhaps someone will be able to spot where you may be going wrong.

Phil

[singhpk]

Here is the attachment

The initial query is called getModulelayout and is as follows

SELECT
a.surveyID,
a.moduleName,
a.moduleID,
a.moduleSort,
a.questionID,
a.questionType,
a.questionName,
a.QuestionText,
a.across,
a.gridID,
a.sortby,
a.columnID,
a.cType,
a.cName,
a.cText,
a.cacross,
a.cgridID,
a.csortby,
a.displayModeID,
a.cdisplayModeID,
a.questionTypeName,
a.columnTypeName,
h.helptext,
h2.helptext as chelptext,
o.optionText,
o.optionID,
v.variableTypeId,
vt.shortName as variableTypeName,
vt.decimalPlaces,
v.minVal,
v.maxVal,
v.maxChange,
v.notNull,
vt.isNumeric,
c2.cycleid,

case a.variableid
when -2 then null
else a.variableID
end as variableID,
ISNULL(a.iscalculated,0) as iscalculated,
a.factors,
a.formula,
a.delta,
a.[isnumeric]
FROM
survey_view2 a
INNER JOIN tblCycle c1
ON c1.cycleid = 115
AND c1.clientID = 5
AND a.moduleID = 248
LEFT JOIN tblOption o
ON (a.optionSetID = o.optionSetID OR a.cOptionSetID=o.optionSetID)
AND (a.questionType != 5 OR a.questionType IS NULL)
AND 1 = 1
LEFT JOIN tblCycle c2
ON (c1.cycleSort=c2.cycleSort-a.delta and c2.clientID = 5)
LEFT JOIN tblhelp h ON h.helpid=a.helpid
LEFT JOIN tblhelp h2 ON h2.helpid=a.chelpid
LEFT JOIN tblVariable v
ON a.variableId=v.variableId
left join tblVariableType vt
ON v.variableTypeID = vt.variableTypeID

WHERE isnull(a.displayModeID,1) in (1,2) AND isnull(a.cdisplayModeID,1) in
(1,2)
ORDER BY a.moduleSort, a.moduleID, a.sortBy, a.csortby, o.sortBy, o.optionID,
a.questionName
---------------------------------------------------------------------

Another query called "getCyclesForComparison"

<cfquery name="getCyclesForComparison" datasource="#app_datasource#"
cachedWithin="#twoHours#">
select thisCycle.cycleID, isNull(lastCycle.cycleID, 0) as lastCycleID
from tblCycle thisCycle
left join tblCycle lastCycle
on lastCycle.clientID = #app_clientID#
and lastCycle.cycleSort = (
select max(cycleSort)
from tblCycle
where cycleSort < thisCycle.cycleSort
)
where thisCycle.clientID = #app_clientID#
order by thisCycle.cycleID
</cfquery>

------------------------------------------------------------------------------
Finally I am querying the above two queries using the following query
<cfquery name="getVariables" dbtype="query" cachedWithin="#twoHours#">
select getModuleLayout.variableID as variableID,
cycleID
from getModuleLayout
where variableID <> ''
<cfif getWholeSurvey>
and surveyID = #surveyID#
<cfif isdefined("moduleList") and len(moduleList)>
and moduleID in (#moduleList#)
</cfif>
<cfelse>
and moduleID = #moduleID#
</cfif>
and #cycleID# = #cycleID#

union

select getModuleLayout.variableID as variableID,
getCyclesForComparison.lastCycleID as cycleID
from getModuleLayout, getCyclesForComparison
where getModuleLayout.variableID <> ''
and getModuleLayout.maxChange <> ''
and getCyclesForComparison.cycleID = getModuleLayout.cycleID
<cfif getWholeSurvey>
and getModuleLayout.surveyID = #surveyID#
<cfif isdefined("moduleList") and len(moduleList)>
and getModuleLayout.moduleID in (#moduleList#)
</cfif>
<cfelse>
and getModuleLayout.moduleID = #moduleID#
</cfif>
and #cycleID# = #NumberFormat(cycleID)#

order by cycleID, variableID
</cfquery>

[paross1]

I suspect that you are getting your error on the statements like variableID <>
'' in your Q-of-Q, especially if variableID is numberic. You probably should
write it as variableID IS NOT NULL instead of variableID <> ''.

Try that with any of the lines where you are using var <> '' where var is a
numeric data type in your original query.

Phil

[singhpk]

It worked. Thanks a lot. :gift;