Solved

cfquery with a variable does not work

Posted on 2004-03-24
11
239 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:ingels
[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
11 Comments
 
LVL 10

Expert Comment

by:Mause
ID: 10667709
What error message do you get?
What DB do you use?

MAuse
0
 

Author Comment

by:ingels
ID: 10667756
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
 
LVL 10

Accepted Solution

by:
Mause earned 25 total points
ID: 10668098
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
Optimum High-Definition Video Viewing and Control

The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.

 
LVL 6

Assisted Solution

by:PE_CF_DEV
PE_CF_DEV earned 25 total points
ID: 10677827
try using #preservesinglequotes(gem)# instead of #gem#
0
 
LVL 2

Expert Comment

by:bowline
ID: 10682062
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
 
LVL 10

Expert Comment

by:Mause
ID: 11056785
I would say

mause & PE_CF_DEV & bowline

we all gave a possible solution
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11060006
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
 
LVL 10

Expert Comment

by:Mause
ID: 11061383
Think it would be me and PE_CF_DEV then
because I think only cfqueryparam won't solve the problem
0

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

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

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…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

615 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