Copy link to clipboard
Copied
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
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#">
Copy link to clipboard
Copied
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, "_")#" />
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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, "_")#" />
Copy link to clipboard
Copied
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#">
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Agreed.
Copy link to clipboard
Copied
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.