Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

CFqueryparam  - list="Yes" not working as intended

Posted on 2009-07-16
9
Medium Priority
?
482 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
Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

 

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 2000 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

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 …
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 …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

636 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