Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

cfquery with a variable does not work

Posted on 2004-03-24
11
Medium Priority
?
243 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
8 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 100 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 6

Assisted Solution

by:PE_CF_DEV
PE_CF_DEV earned 100 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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…
What You Need to Know when Searching for a Webhost Provider
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

876 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