Variable Join

[Guest]

Good day all, a quick question:

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

Example:
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

[Dan_Bracuk]

deleted

[Dan_Bracuk]

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.

[Guest]

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
getdata.code1
getdata.code2
getdata.code3

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

Regards

[Dan_Bracuk]

Why would something like this not work

select somefields
from table t, inventory i
where t.code1counter = i.code

[Guest]

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.

[MikerRoo]

Try something like:
<cfquery name="getiteminven" datasource="data">
SELECT *
FROM inventory
WHERE Code = '#getdata["code" & counter]#'
</cfquery>

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

{{edit: deleted stray dot.}}