6 Replies Latest reply on May 29, 2006 12:44 AM by MikerRoo

    Variable Join

    ShapeShift Level 1
      Good day all, a quick question:

      Im trying to do a dynamic query and wnd need to join to variables to get the query.

      I am running a loop let say 2 times, i have the variable counter that increases by 1 each time the loop runs through.
      I need to reun a query each time that uses another query value,

      how do i do this see example code below:
      wher it says
      I need it to say getdat.code1 or code2 (whatever counter is)
      WHERE Code = 'getdata.codecounter'
      if you need more information just ask, any help would be great
        • 1. Variable Join
          Dan Bracuk Level 5
          • 2. Re: Variable Join
            Dan Bracuk Level 5
            Your question is unclear, at least to me. To clarify,

            What fields are contained in the table named table?

            Also, what should getdata.code1counter be each time you go through your loop?

            By the way, you don't need a where clause in your first query.
            • 3. Re: Variable Join
              ShapeShift Level 1
              The top query gets invoice numbers.

              I must then loop through each invoice to get the item codes of each invoice there can potenialy be 15 on each invoice.

              So therefor the loop query must get

              When i try and join them it read the query as 'getdata.code1' and not as the potential values listed below:

              and getdata.code1 could be 402
              and getdata.code2 could be 999
              and getdata.code3 could be 548

              Does this make any more sence to you.
              Thanks for trying to help!!!
              Their must be an easy way but i cant find it


              • 4. Re: Variable Join
                Dan Bracuk Level 5
                Why would something like this not work

                select somefields
                from table t, inventory i
                where t.code1counter = i.code
                • 5. Re: Variable Join
                  ShapeShift Level 1
                  that does not work for what i need to do.
                  Let try a beter example.

                  I run a query named getdata, which gets invoice numbers.
                  I then need to display the items of each entry on the invoice, code1, through code15,

                  So therefore if code1 is 201 and code2 is 999
                  co when i set counter = 1 i need the display 201 and when counter = 2 i need the display of 999

                  I need this to work but i get code is not defined. so therefore i need to join the variables, is this possible.

                  • 6. Re: Variable Join
                    MikerRoo Level 1
                    Try something like:
                    <cfquery name="getiteminven" datasource="data">
                    SELECT *
                    FROM inventory
                    WHERE Code = '#getdata["code" & counter]#'

                    This assumes that the column names are "code1", "code2", etc.

                    {{edit: deleted stray dot.}}