cfquery with a variable does not work

Hi

I have a cfquery-tag and I want to use a variable in the sql-statement.

<CFQUERY NAME = "Parztype" DATASOURCE = ds_t>
   select count(*) number, type, trunc(sum(area),1) area
   from gs_parz
  where section in  #gem#
   group by type
</CFQUERY>

I can output the #gem# and it looks like: ('1', '2', '3A', '5X') incl. the parenthesis. But  the select-statement does not work. In an other part I have a: section = #sec# where #sec# = '3A' and this works.
Does anybody have an idea whats wrong?

Thanks for any help
Ingrid
ingelsAsked:
Who is Participating?
 
MauseConnect With a Mentor Commented:
I don't use oracle but I don't see anything wrong in your query
are you sure  #gem# = ('1', '2', '3A', '5X')

Have you tried to replacae #gem# width ('1', '2', '3A', '5X')
so it will be:

<CFQUERY NAME = "Parztype" DATASOURCE = ds_t>
   select count(*) number, type, trunc(sum(area),1) area
   from gs_parz
  where section in ('1', '2', '3A', '5X')
   group by type
</CFQUERY>

And did that work?
0
 
MauseCommented:
What error message do you get?
What DB do you use?

MAuse
0
 
ingelsAuthor Commented:
Hi
sorry, I forgot to mention it. I use an Oracle DB.
The error message says: missing parenthesis at the line where I have the cfquery-tag.

Ingrid
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
PE_CF_DEVConnect With a Mentor Commented:
try using #preservesinglequotes(gem)# instead of #gem#
0
 
bowlineCommented:
I have had the best luck with cfqueryparam in these cases.  Let ColdFusion prepare the data - also it is a little quicker...

<CFQUERY NAME = "Parztype" DATASOURCE = ds_t>
   select count(*) number, type, trunc(sum(area),1) area
   from gs_parz
  where section in  (<cfqueryparam value="#gem#" cfsqltype="CF_SQL_VARCHAR" list="yes">)
   group by type
</CFQUERY>
0
 
MauseCommented:
I would say

mause & PE_CF_DEV & bowline

we all gave a possible solution
0
 
mrichmonCommented:
Since this question is less than 60 points I can at most split between 2 answers, so given that which 2 of the three answers do you think deserves to be marked as the answers?
0
 
MauseCommented:
Think it would be me and PE_CF_DEV then
because I think only cfqueryparam won't solve the problem
0
All Courses

From novice to tech pro — start learning today.