Solved

CFqueryparam  - list="Yes" not working as intended

Posted on 2009-07-16
9
461 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
COLD FUSION 6 27
Most current Records - Query or Query ColdFusion 5 62
problem with finding cfcomponent on cfselect bind 7 86
ColdFusion 10 Error 2 48
This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now