Solved

CFqueryparam  - list="Yes" not working as intended

Posted on 2009-07-16
9
464 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
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
iPhone looses session info 5 30
Create array from a query and then loop it with coldfusion 12 82
How to do a Left Outer Join sql query in coldfusion 4 109
Database set up 5 79
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…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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