[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

Need help with SQL IN clause regarding a multiple selection box

Hey All,

    In the middle of an SQL query i have the following line:  

    WHERE location IN ('#form.categories#')    where the form.categories is the list returned from a multiple selection list box.  This query is not behaving properly because that last term gets evaluated as, for instance, the following:           Where location in ('Long Island, New York')   Where what i really need is the following:    Where location in ('Long Island', 'New York')         So you see, the quotes are not in the right place for proper functionality.  Can anyone tell me what I am doing wrong or how to fix it?  thank you.  
0
andrewaiello
Asked:
andrewaiello
  • 4
  • 4
  • 3
  • +2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to put the quotes in your form.categories.
it would eventually be better to use a stored procedure for that, so it makes such complexity transparent to the (coldfusion) code
0
 
andrewaielloAuthor Commented:
Thanks for replying so fast.  

     The listbox is being populated from a query so i don't know how to put the quotes in without generating the error.  The code for the box is as follows where catText would be like New York:  

              <cfselect name="categories"
                  query="getCat"
                  value="catText"
                  display="catText"
                  required="No"
                  multiple="Yes"
            
                  size=4></cfselect>
0
 
andrewaielloAuthor Commented:
(reading up on coldfusion stored procedures btw ;)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SidFishesCommented:
it's due to the way cfselect passes the data


since cf is parsed before sql, a quick way might be to simply

 WHERE location IN ('#replace(form.categories, ",", "','", "all"#')

which basically transforms this


('Long Island, New York')
by replacing  the , with ','

so you get

('Long Island', 'New York') (provided i got all my "'''''s in the right place)


btw...cf doesn't -have- stored procedures...it just uses sp's from your dbms



0
 
SidFishesCommented:
err...i did miss a )

sb

WHERE location IN ('#replace(form.categories, ",", "','", "all")#')
0
 
andrewaielloAuthor Commented:
hmmm, pretty slick!  I'll give that a try.  Thanks for your info on stored procedures btw.  Take care.
0
 
cyrkCommented:
You should also be able to use #QuotedValueList(FORM.Categories)#
0
 
dgrafxCommented:
It's ok guys - I'm here now ...

cyrk - QuotedValueList is for a query column output only!!!

I believe what you're looking for is #ListQualify(FORM.Categories,"'")#
This accomplishes the same as Sid's replace statement, but I feel is slicker.
If the list(FORM.Categories) is "New York,Claire City,Melbourne"
then #ListQualify(FORM.Categories,"'")# would be 'New York','Claire City','Melbourne'
0
 
SidFishesCommented:
oh dg...yer all about the slick ;)

where ya been...working or something??

of course this illustrates what's good and also what's bad about cf there's many ways to do a thing...but it can be confusing to know which one to choose...
0
 
SidFishesCommented:
btw dg...i'd appreciate some input here..

http://www.experts-exchange.com/Database/MySQL/Q_22479194.html (it's not really a mysql q...stupid zone cross post thing goes where ever)
0
 
andrewaielloAuthor Commented:
Wow you guys, thanks alot; it works now.  I used DG's method (sorry sid! ;) )   Btw, where do you guys go to look for these types of functions that don't seem to be in the standard docs.  Thanks
0
 
dgrafxCommented:
andrew ...
I'm glad it worked
most everything is in the docs.
I know listqualify is - I remember when I first found it.
0
 
dgrafxCommented:
Sid - I posted in your other post ...
waiting for your reply ...
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now