Solved

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

Posted on 2009-07-15
4
603 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:wkolasa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 36

Accepted Solution

by:
SidFishes earned 500 total points
ID: 24862292
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
 

Author Comment

by:wkolasa
ID: 24862474
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 24862715
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
 

Author Closing Comment

by:wkolasa
ID: 31603843
thank!  youre aces!
0

Featured Post

Percona Monitoring and Management and Grafana

Proactive monitoring is vital to a highly-available environment. We have a quick start guide on Experts Exchange for Grafana users.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question