Solved

CFqueryparam  - list="Yes" not working as intended

Posted on 2009-07-16
9
469 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion - check if two values are within a range 4 71
Database set up 5 87
CSS HELP 6 103
dynamic form fields and inserting into database 3 49
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
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…

737 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