Solved

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

Posted on 2009-07-15
4
594 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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