Query of Queries error when I switched from Access to SQL Server
Here's the error below that I'm getting. I'm initially querying a view and then requerying it to get certain counts. Any suggestions?
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 = "TIMESTAMP".
Right hand side expression type = "LONG".
The error occurred in C:\CFusionMX7\wwwroot\WDat
119 : Select Count(*) As DonorCount
120 : From RecencyValues
121 : Where (HPC < 5) and (DaysOut >= #DayValues[OuterLoop][1]#)
122 : </cfquery>
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 = "TIMESTAMP".
Right hand side expression type = "LONG".
The error occurred in C:\CFusionMX7\wwwroot\WDat
119 : Select Count(*) As DonorCount
120 : From RecencyValues
121 : Where (HPC < 5) and (DaysOut >= #DayValues[OuterLoop][1]#)
122 : </cfquery>
what does "DaysOut" represent?
looking at this part of the error code...
Left hand side expression type = "TIMESTAMP".
Right hand side expression type = "LONG".
they have to be the same data type for that type of comparrison...
DaysOut is currently representing a "TIMESTAMP" data type
while #DayValues[OuterLoop][1]# is representing a data type of "LONG"
you have to convert one or the other so the data types match...
Left hand side expression type = "TIMESTAMP".
Right hand side expression type = "LONG".
they have to be the same data type for that type of comparrison...
DaysOut is currently representing a "TIMESTAMP" data type
while #DayValues[OuterLoop][1]# is representing a data type of "LONG"
you have to convert one or the other so the data types match...
You cannot compare timestamp. It simply an always incrementing numerical value.
ASKER
DaysOut is the difference between todays date and the date on each record (MRDate). Here's what it looks like in my original query:
(#CreateODBCDate(Now())#-[
I'm trying to count how many orders there were between 0-30 days, 60-90 days, etc etc. DayValues are just numbers I entered into an array. As I mentioned above, this works fine in Access, but no go when I switched to SQL server.
(#CreateODBCDate(Now())#-[
I'm trying to count how many orders there were between 0-30 days, 60-90 days, etc etc. DayValues are just numbers I entered into an array. As I mentioned above, this works fine in Access, but no go when I switched to SQL server.
ASKER
I fixed it. Had to change (#CreateODBCDate(Now())#-[
to
DATEDIFF(DAY, MRDate, GETDATE()) AS DaysOut
and now it works!
to
DATEDIFF(DAY, MRDate, GETDATE()) AS DaysOut
and now it works!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.