Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post May 10, 2012 04:04 PM by jvst
May 08, 2012 05:04 PM|LINK
i have an SQL statement which i need to code in vb:
create table r2 (salesno bigint, prodno1 bigint ,prodno2 bigint )
insert into r2
select p.salesNo, p.prodNo, q.prodNo
from salesLog as p, salesLog as q
where q.salesNo = p.salesNo and q.prodNo>p.prodNo
notice the fields prodno1 and prodno2 in the table structure..
this is part of a number of sql statements i need to run and put inside a loop. my problem is i want to automatically use this same code such that on the next loop, this sql statement is going to be:
create table r3 (salesno bigint, prodno1 bigint ,prodno2 bigint, prodno3 bigint)
insert into r3
select p.salesNo, p.prodNo1, p.prodNo2, q.prodNo
from l2 as p, salesLog as q
where q.salesNo = p.salesNo and q.prodNo>p.prodNo2
Note that in this "2nd loop"
- the table name is now r3 from r2 in the first SQL statement
- there are now prodno1, prodno2 and prodno3 instead of just prodno1 and prodno2
- in the WHERE clause, the p.prodNo becomes p.prodNo2
and so on and so forth.. so for the 3rd loop
- there's going to be r4
- there's going to be prodno4
- in the WHERE clause i will use p.prodNo3
what should i do? is there an array or something that i can use here?
thank you in advance.
May 10, 2012 05:22 AM|LINK
Hi, do you want to create SQL statement with VB?
May 10, 2012 05:32 AM|LINK
create a string, it is easy to use a loop to create a string like that, then exec the string.
May 10, 2012 04:04 PM|LINK
thanks for the reply..yes.. this will be implemented using vb. this is actually just part of a series of data manipulation from a sales transaction table.