Skip to main content
G
gigasaurus
Inspiring
September 21, 2011
Answered

parsing a field in CFQUERY WHERE clause

  • September 21, 2011
  • 4 replies
  • 2500 views

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

This topic has been closed for replies.
Correct answer insuractive

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#">

4 replies

G
gigasaurusAuthor
Inspiring
September 23, 2011

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.

I
insuractiveCorrect answer
Inspiring
September 23, 2011

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__-
Inspiring
September 23, 2011

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

I
insuractive
Inspiring
September 23, 2011

Agreed.

M
meensi
Inspiring
September 22, 2011

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, "_")#" />

12Robots
12Robots
Participating Frequently
September 21, 2011

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, "_")#" />

A
Anonymous
September 22, 2011

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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices