Professional Web Applications Themes

Using a variable as a SELECT field - Coldfusion Database Access

I'm trying to get shipping costs for different weights and zip codes. I query one table to get a zone from the user provided zip code. That works. I then want to use that zone to retreive the shipping cost for a specific weight from another table. I set the zone as a variable and use it as follows: SELECT #Zone# FROM Ground WHERE Wt=#weight# . If I use the actual zone name instead of the variable, everything works right. When I use the variable, however, and try to output the re with the #Zone# variable, I get the original ...

  1. #1

    Default Using a variable as a SELECT field

    I'm trying to get shipping costs for different weights and zip codes. I query
    one table to get a zone from the user provided zip code. That works. I then
    want to use that zone to retreive the shipping cost for a specific weight from
    another table. I set the zone as a variable and use it as follows: SELECT
    #Zone# FROM Ground WHERE Wt=#weight# . If I use the actual zone name instead
    of the variable, everything works right. When I use the variable, however, and
    try to output the re with the #Zone# variable, I get the original name of
    the zone field name, not the rate that is listed under that field name

    Is it proper to use a variable the way I have? If so, how do you access the
    result? If not, is there another way to get a field name for a query from
    another query? Thanks.

    tjIII Guest

  2. #2

    Default Re: Using a variable as a SELECT field

    Normalize your database and you won't have problems like this.
    Dan Guest

  3. #3

    Default Re: Using a variable as a SELECT field

    You can use a variable to dynamically change the select field but this is
    seldom a good idea.
    Here's how you would do it in this case:

    <CFQUERY name="MaintenanceNightmare" datasource="YOUR_DATA_SOURCE">
    SELECT
    #Zone# AS ShippingEstimate
    FROM
    Ground
    WHERE
    Wt=#weight#
    </CFQUERY>

    <CFOUTPUT>
    <h3>Shipping #weight# kilograms, to zone: #Zone#, will cost
    #MaintenanceNightmare.ShippingEstimate# Euros.</h3>
    </CFOUTPUT>


    The suggestion, to normalize your database, was a good one.


    MikerRoo Guest

  4. #4

    Default Re: Using a variable as a SELECT field

    Thanks, that solved the problem. I'm definitely an amature, but I do
    understand normalization to a degree. However, these are tables I download
    from FedEx in Excel format and convert to Access with only a few minor changes.
    These tables change periodically, and I was hoping I could update them with
    only a minimum amount of work.

    tjIII Guest

Similar Threads

  1. Select one field
    By Garry in forum MySQL
    Replies: 1
    Last Post: October 8th, 02:17 AM
  2. How to SELECT DatePart as a new Variable
    By Gahiggidy in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 30th, 06:25 PM
  3. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  4. variable select statement
    By Karzy in forum PHP Development
    Replies: 2
    Last Post: October 4th, 01:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139