use "ListContains' in a WHERE clause

Forum|Forum|19 years ago
March 23, 2007
5 replies
755 views

I believe I have a syntax problem and could you some help. First, I have a field in an Access DB called 'degree" that conatins a list of numbers. This list can be any combination of the numbers 4 through 32. On my search page I have a selection box in a form to select a value of the degree to search for. The variable passed from the form is called "degreeValue". I have a component that lets me build my WHERE clause using several seach criteria. It functions perfectly prior to adding the search for "degree". I attempted to use "ListContains" in the WHERE clause using "degree" as the name of my list and "degreeValue" as my substring to search for. The error I get says that "variable degree is undefined". I am attaching the full code of the compoanent so you can see it. Again, it works if you take out the subclause build for "degree".

Database access

This topic has been closed for replies.

P

philh

Inspiring

It does in VBA, but that's not reachable through the ODBC driver.

N

Newsgroup_User

Inspiring

if you were using a MySQL database, you could have used
FIND_IN_SET(valuetofind, tablefieldtoserach) > 0...

check if MS Access maybe has a similar function...

--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

G

ghouserAuthor

Inspiring

That gives an error also: "Invalid CFML construct found on line 59 at column 57." Which is right before the first #form.degreeValue#. It may be the use of quotes, but I'm not certain.

What about reading the list into an array then searching the array for the desired value. If found, then set a new var for true and use that in the WHERE clause? Hmmmm... sounds more complicated than it should be. ;-)

K

Kronin555

Participating Frequently

OK, here it is with quotes corrected.

<cfif Form.degreeValue GT "">
<cfset WhereClause = WhereClause & " and (degree like '#form.degreeValue#,%' OR degree like '%,#form.degreeValue,%' OR degree like '%,#form.degreeValue#')" >
</cfif>

I'm warning you, though. LIKE checks are gonna be slower, runtime-wise, than fixing your datamodel (even if the mapping table ends up with 58,000 rows).

G

ghouserAuthor

Inspiring

I understand what you are saying and in other situations have done my tables that way. I didn't this time because this is one of those... "add it later" kind of things. Yes, the field does look like "1,2,3,4,etc.." The inventory a couple thousand items each with a number (catno). As you obviously noticed the inventory table houses most of the data for the items. The "degree" field in the past was only used to populate a series of checkboxes and was not searchable, thus a list was used.

With a possibility of 29 values for "degree" and a possiblility of over 2,000 inventory items you can see a newly created table might become quite large. It would be nice if I could use the current structure.

You are absolutely correct saying the design should be different. Thank you for your quick reply and good information.

K

Kronin555

Participating Frequently

Well, even if every one of the 2,000 inventory items has each degree, that's still only 2,000 * 29 = 58,000 records. In any real database system, a table with 58,000 rows is pretty minimal. Now you did say you were on Access, so....

Anyway. If you don't want to correct the data model, your only other alternative is to check for 3 instances: the number you're searching for is at the beginning of the list, middle of the list, or end of the list. You would do this as follows:
<cfif Form.degreeValue GT "">
<cfset WhereClause = WhereClause & " and (degree like '"#form.degreeValue#,%" OR degree like "%,#form.degreeValue,%" OR degree like "%,#form.degreeValue#")'" >
</cfif>

K

Kronin555

Participating Frequently

"I have a field in an Access DB called 'degree" that conatins a list of numbers."

You mean that your field looks like this?
4,12,20,26

If so, the reason you're having difficulty is because this is bad database design. You need to break that out into another table.

Have one table, named "inventory", that contains all the other fields, including an inventoryID (an immutable key).

Then have a table that has 2 values, an inventoryID which is a foreign key reference to inventory.inventoryID, and a degree.
InventoryToDegree
----------------------------------
InventoryID Degree
---------- ----------
1 12
1 20
1 32

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded