Solved

cfquery with a variable does not work

Posted on 2004-03-24
11
238 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Suggested Solutions

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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