Skip to main content
S
storserver
Inspiring
May 18, 2009
Answered

How do you use a list in a query so you can sort the list based on a linked table?

  • May 18, 2009
  • 1 reply
  • 672 views

1. My initial query returns phone numbers with inconsistent structures. Example:

          (555)-555-5678

          666-555-5678

          (444)555.5678

          1-888-555-5678

2. I loop through the list and perform a number of cleanup functions so the list looks like this:

         

          555-555-5678

          666-555-5678

          444-555-5678

          888-555-5678

3. Now I need to use this list in a query so I can use the first three digits to look up the area code from an area code table and its linked  and be able to sort by the area code. The area code table looks something like this:

                    

                          ThreeDigitCode          AreaID

                              555                         1

                              666                         2

                              444                         1

                              888                         4

I need to put the phone list in the query so I can do a lookup of its areaID and the comparable information in a table that says AreaID 1 is Eastern and its sort position is 1, etc.

Thanks in advance for your advice.

    This topic has been closed for replies.
    Correct answer mack_

    You can add an empty column to your query when you select the phone

    numbers and the use that column to store the ThreeDigitCode:

    then cleanup the numbers and place the 3 digit code in the additional

    column. And then using Query of Queries join the phone numbers with

    the area codes:

    
  



  SELECT *
  FROM phone_number
    INNER JOIN area_codes ON area_codes.threeDigitCode =
phone_number.threeDigitCode

]]>

    Mack

    1 reply

    M
    mack_Correct answer
    Participating Frequently
    May 18, 2009

    You can add an empty column to your query when you select the phone

    numbers and the use that column to store the ThreeDigitCode:

    then cleanup the numbers and place the 3 digit code in the additional

    column. And then using Query of Queries join the phone numbers with

    the area codes:

    
  



  SELECT *
  FROM phone_number
    INNER JOIN area_codes ON area_codes.threeDigitCode =
phone_number.threeDigitCode

]]>

    Mack

    S
    storserverAuthor
    Inspiring
    May 18, 2009

    I'm excited! I do have a snag, though. When I'm looping through each phone number and cleaning it up, I set a variable called FixedPhone and assign it the cleaned up version. Therefore a query cell called phone_number with a value of "(555)-555-5678" is now set as

         <cfset FixedPhone="555-555-5678">

    How does my FixedPhone relate to  cleanupPhoneNumber(phone_number) in

    <cfset QuerySetCell(phone_number, "threeDigitCode",cleanupPhoneNumber(phone_number), CurrentRow)>

    Is cleanupPhoneNumber(phone_number) a variable name?

    D
    Dan_Bracuk
    Inspiring
    May 18, 2009

    Regarding,

    Is cleanupPhoneNumber(phone_number) a variable name?

    No, it's a function result.

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