Solved

CFqueryparam  - list="Yes" not working as intended

Posted on 2009-07-16
9
471 Views
Last Modified: 2012-06-27
Hi again,

I have a cfqueryparam being used to submit a value to the oracle DB.  It submits a letter, compares it to a field with comma delimited enters (ex.  A,S,E,H,D), and if a positive match happens it returns results.  It's not working though.  Code is below:

((Database Field c.class_level = E,M,H,S,A))
 
<cfset session.level = 'H'>
 
c.class_level in  (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#session.level#" list="Yes">)
 
<!---This returns no results.  It SHOULD be returning 1 result because 'H' is among c.class_level--->

Open in new window

0
Comment
Question by:wkolasa
[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
  • 3
  • 3
  • 2
  • +1
9 Comments
 

Author Comment

by:wkolasa
ID: 24869271
the LIKE operator works... is this okay to use?
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24869399
can you just do this . Try using in instead of equal
c.class_level in ('E','M','H','S','A')
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24869455
storing lists as db values is a bad practice.

the LIKE operator will work in this particular case, but you may have a lot of trouble dealing with your comma-delimited list later when you want to do other things.

normalize your db.

Azadi
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:wkolasa
ID: 24869595
can't normalized the DB.  I would love to.  I'm working with a system that is in-place and not going anywhere.
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24869628
but anyway you still need to use IN , otherwise it only looking for the combination and of course it will never find it
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24869647
yeah, that happens sometimes...
brace yourself, then... and good luck!

Azadi
0
 

Author Comment

by:wkolasa
ID: 24869758
Can't do it like this:  c.class_level in ('E','M','H','S','A')   because this is an exclusion.  If session.level = "E"  and E isn't in the DB field (db field = M,H)  then no results are suppose to return.  So I need to compare the session.level varible to the list in the DB field and see if there's a match.
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24869808
we ll just do the comparison this way <cfif ListFindNoCase(session.level, class_level )>
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 24870217

The problem is that this
 c.class_level in ('E','M','H','S','A')

is backwards

What you really want is

  where 'E' not in (c.class_level)

because class_level has the list  E,M,H,S,A

Hopefully you only have commas delimiting the values and no random spaces.
If so, you can do the following...

where ','+class_level+','   NOT Like  '%,D,%'

You need to add the commas in order to ensure the delimiters are next to the value you are seeking.

If you didn't use the extra commas then E would match DE,FE and not just E


If you are guaranteed that the values are all only ONE character long then you can skip that and do ...


  where class_level  NOT Like  '%D%'

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

726 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