parsing a field in CFQUERY WHERE clause

[gigasaurus]

I have a fairly large database that I need to run queries on.  I have one field that represents where store items are stocked by aisle, section and bin. This location field is concatenated with underscore delimiters: aisle_section_bin .  An example location might be 3_25_17.

I need to run queries where I parse this concatenated location field and essentially ask:

SELECT *

FROM MyData

WHERE aisle = '3' AND section '25' AND bin = '1'

Could someone please help me with house this would be done?

My database is fairly large, so I need my queries to be fairly efficient so my server does not timeout.

Thankyou

[12Robots]

I am going to guess that the bin= line in your query should be '17' not '1'

You can think of 3_25_17 as an underscore delimited list. So you can use listGetAt() to get each value.

SELECT *

FROM MyData

WHERE aisle = <cfqueryparam value="#listGetAt(fieldValue, 1, "_")#" />

      AND section = <cfqueryparam value="#listGetAt(fieldValue, 2, "_")#" />

      AND bin =  <cfqueryparam value="#listGetAt(fieldValue, 3, "_")#" />

[Guest]

As I understand it, you have a single field in the database called "aisle_section_bin", right?

So your where need to ask for aisle_section_bin = '3_25_17'.

You could easily contruct that string, but it is not a very efficient database design.

If possible, I would move them to seperate fields with the correct datatypes (tinyint if value min = 0 and max = 128, etc.).

This will give you a much faster response, especially if you index the fields.

[meensi]

Hi,

I agree with Claus P; You need to move them to separate fields for a better searching;

I assume that you have those three values separately and in your database those values are concatenated with underscore.

If you sure that your data in the database is only with this format 3_25_17; You could think of using SUBSTR functions in oracle and then use ListGetAt Function in Coldfusion to find it.

Ex: SUBSTR(3_25_17,1) = <cfqueryparam value="#listGetAt(fieldValue, 1, "_")#" />

[insuractive · Accepted solution]

couldn't you just concatenate the values together as a CF string and pass it into your where clause:

WHERE aisle_section_bin = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value=""#aisle#_#section#_#bin#">

[-__cfSearching__-]

Yep, that would work in this specific case. But I agree with the others who have suggested using three separate int fields. Overall that structure provides greater flexibility and generally better performance. 

-Leigh

[insuractive]

Agreed.

[gigasaurus]

Thanks very much. These replies helped me through. Unfortunately I am accessing the client's database and I don't have the option to alter that structure.  But the techniques you guys pointed out worked for me.