Bill, Rachel,
Thanks for your thoughts on this. Bill, I think a Cartesian product may be the way to go on this, as I would then create 2 rather small temp tables for the insert. Thanks again, Brad From: [email protected] [mailto:[email protected]] On Behalf Of Bill Downall Sent: Wednesday, May 12, 2010 9:28 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: SQL Question sorry, entered too soon... Brad, Is there any table in the database that has the code values as distinct values in a column? If so, INSERT INTO temptable SELECT thatcolumn FROM thattable WHERE thatcolumn IN ( &vtype ) would be part of the job. And, presumably, there's another table that has the other kinds of values from .vPN? What you might use is a Cartesian product: Select rows from both tables at once, and do not link the two tables at all. You end up multiplying the rows in one table by the rows in the other table for the insert: INSERT INTO temptable (UnitPart, UnitType) + SELECT p1.UnitPart, t2.UnitType + FROM PartsListTable p1, TypesListTable t2 + WHERE p1.UnitPart IN ( &vPN ) + AND t2.UnitType IN ( &vType ) Cartesian products are dangerous if you have large tables or don't qualify the rows in one table or the other, but they can allow for some very efficient SQL. Bill On Wed, May 12, 2010 at 12:14 PM, Brad Davidson <[email protected]> wrote: I'm Looking for an alternative to a series of SSUB extracts within a WHILE loop. I have 2 variable text strings resulting from a couple of SELECT INTO statements, which lists UnitPart and UnitType, such as: UnitPart: vpn = ('Unit1,Unit2,Unit3') UnitType: vtype = ('F,NDT,SM,C,PL') I want to populate a temporary table/view with unique unit & type combinations, such as: UnitPart UnitType unit1 F unit1 NDT unit1 SM unit1 C unit1 PL unit2 F unit2 NDT unit2 SM unit2 C unit2 PL unit3 . . . . etc... What method would you employ to build this temporary table? The resultant table values will be used to replace via UPDATE or INSERT values in the permanent table... Thank you for your thoughts on this! Brad Davidson
