CFqueryparam - list="Yes" not working as intended

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

wkolasaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wkolasaAuthor Commented:
the LIKE operator works... is this okay to use?
0
erikTsomikSystem Architect, CF programmer Commented:
can you just do this . Try using in instead of equal
c.class_level in ('E','M','H','S','A')
0
azadisaryevCommented:
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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

wkolasaAuthor Commented:
can't normalized the DB.  I would love to.  I'm working with a system that is in-place and not going anywhere.
0
erikTsomikSystem Architect, CF programmer Commented:
but anyway you still need to use IN , otherwise it only looking for the combination and of course it will never find it
0
azadisaryevCommented:
yeah, that happens sometimes...
brace yourself, then... and good luck!

Azadi
0
wkolasaAuthor Commented:
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
erikTsomikSystem Architect, CF programmer Commented:
we ll just do the comparison this way <cfif ListFindNoCase(session.level, class_level )>
0
gdemariaCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.