Solved

cfquery with a variable does not work

Posted on 2004-03-24
11
237 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tool to email me when a website changes 29 138
Problem to get function 52 116
UPLOAD FILE TO Web API USING POST 5 132
Intranet and Kerberos Authentication (not sharepoint!) 8 154
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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