Using 'IN' operator in ColdFusion Query of Queries throwing error

Hi,
I have the following query which is throwing an error:

<cfquery dbtype="query" name="getAtt">
select *, upper(lname) as ulname, upper(fname) as ufname from getAttendees
where breakout_id = '#trim(url.ids)#'
and id_register_status IN (1,3)
and id_rate_type NOT IN (1,2)   (this is the line that's breaking it.. causing error)
order by date_signup
</cfquery>

The datatype for id_rate_type is VCHARAR2 - Oracle DB.

Here's the error message:

Query Of Queries runtime error.  
Comparison exception while executing IN.
Unsupported Type Comparison Exception: The IN operator does not support comparison between the following types:
Left hand side expression type = "STRING".
Right hand side expression type = "LONG".


Any help resolving this will be greatly greatly appreciated.
Thank you  ~ Wendi
wkolasaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SidFishesConnect With a Mentor Commented:
as the error says, you can't compare a string and an int

but this should work

and id_rate_type NOT IN ('1','2')

0
 
wkolasaAuthor Commented:
Thank you Fishes...  I tried  NOT IN ("1","2")   didn't work... tried  NOT IN ('1','2') and it did work.  What's the difference?   ((by the way, thank you for your answer.  and thank you for pointing out the obviousness too (funny))
0
 
SidFishesCommented:
afaik QoQ's need '

it may be they were copying t-sql which uses ' (not sure about oracle)
maybe it looks at (" as the start of a list
maybe CF  just doesn't -like- "

not really sure
0
 
wkolasaAuthor Commented:
thank!  youre aces!
0
All Courses

From novice to tech pro — start learning today.