Skip navigation
gigasaurus
gigasaurus
Currently Being Moderated

parsing a field in CFQUERY WHERE clause

Sep 21, 2011 11:30 AM

Tags: #parse #cfquery #delimiter #where

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

857 Views   7 Replies   Latest reply: gigasaurus, Sep 23, 2011 10:11 AM
 
Replies
  • 12Robots
    Currently Being Moderated
    1. 12Robots,
    Sep 21, 2011 1:45 PM   in reply to gigasaurus
    Report

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

     
    |
    Mark as:
  • Claus P
    Currently Being Moderated
    2. Claus P,
    Sep 21, 2011 11:47 PM   in reply to 12Robots
    Report

    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.

     
    |
    Mark as:
  • meensi
    Currently Being Moderated
    3. meensi,
    Sep 22, 2011 4:53 AM   in reply to gigasaurus
    Report

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

     
    |
    Mark as:
  • insuractive
    Currently Being Moderated
    4. insuractive,
    Sep 23, 2011 9:15 AM   in reply to gigasaurus
    Report

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

     
    |
    Mark as:
  • -==cfSearching==-
    Currently Being Moderated
    5. -==cfSearching==-,
    Sep 23, 2011 10:02 AM   in reply to insuractive
    Report

    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

     
    |
    Mark as:
  • insuractive
    Currently Being Moderated
    6. insuractive,
    Sep 23, 2011 10:04 AM   in reply to -==cfSearching==-
    Report

    Agreed.

     
    |
    Mark as:
  • gigasaurus
    Currently Being Moderated
    7. gigasaurus,
    Sep 23, 2011 10:11 AM   in reply to gigasaurus
    Report

    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.

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points