I use the same technique, but with a different swing. I use a view making the same kind of join for reporting purposes. One bill of materials may be for an order of ten whatever. It looks like I have ten copies of the BOM, but I don't.
Dennis ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Wills, Steve Sent: Wednesday, October 28, 2009 1:27 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Iterating Inserts - Header/Summary => Details? Well, so far, it's working, well, how did I render it, "BEE-U-TIFULLY!" I'm still cleaning up some data and checking the query results (meaning, my failures of logic), but it will do this job exactly as I need it done. Wunnerful! Steve From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Wednesday, October 28, 2009 12:33pm 12:33 To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Iterating Inserts - Header/Summary => Details? Steve, Two (or 3) heads are better than one, that is for sure. I thought about the autonumber later and it is a good idea. If the numbers table does not have enough numbers you just push enough rows in and you are all set. There is (almost) always a more elegant way. Dennis ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Wills, Steve Sent: Wednesday, October 28, 2009 11:52 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Iterating Inserts - Header/Summary => Details? Dennis, Bill suggested something similar, but thanks for fleshing out the `AllNumbers`, especially the CREATE INDEX tip, as I'd let that one slide by. I created this table: Table: AUX_AUTONUM_DUMMY No Lock(s) Descr: Just to hold a set of sequential number-values No. Column Name Attributes --- ---------------------------------------- --------------------------------------------------------------------------- 1 aux_AUTO_INTEGER Type : INTEGER AUTONUMBER 2 auxAnyTextValue Type : TEXT 16 Comment: For INSERT of records Current number of rows: 500 Obviously, it was empty at first. Then I did a SELECT MAX(QTY) ... on my PSEUDO_HEADER table to see what was my largest value for QTY, which was 445. So, I just picked 500. Then I found a table with more than 500 records and ran this statement INSERT INTO AUX_AUTONUM_DUMMY (auxAnyTextValue) + SELECT 'For Detail Lines' FROM TABLE_WITH_ENUF_RECORDS WHERE LIMIT=500 Will go back and CREATE that INDEX, now, too. Thanks so much, Steve From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Wednesday, October 28, 2009 10:05am 10:05 To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Iterating Inserts - Header/Summary => Details? Steve, In order to do this with one SQL command you would need a table of numbers from 1 to whatever you think would be the largest Qty ever entered. Let's call the table AllNumbers It has one column OneNumber INT Use a loop to fill it with numbers from 1 to 1 million. Index the OneNumber Column Now you can say INSERT into TableDetail (HeaderID,RecordID,ItemID,ColorCD) + Select HeaderID,OneNumber,ItemID,ColorCD + >From TableHeader t1, AllNumbers T2 + WHERE T2.OneNumber <= T1.QTY Dennis McGrath ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Wills, Steve Sent: Wednesday, October 28, 2009 9:42 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Iterating Inserts - Header/Summary => Details? I have a table that is more-or-less a header/parent/summary table. It contains a column-value for quantity. I would like to use that quantity to insert that quantity of records into a detail table. For example: MY_PSEUDO_HEADER ================ HeaderID ItemID ColorCD QTY -------- ------ ------- ----- 00410 ABC RD 5 07045 ABC WH 50 94321 DEF BL 100 94322 DEF RD 33 57058 XYZ BK 47 MY_PSEUDO_DETAIL ================ HeaderID RecordID ItemID ColorCD -------- -------- ------ ------- 00410 1 ABC RD 00410 2 ABC RD 00410 3 ABC RD 00410 4 ABC RD 00410 5 ABC RD -- And so on ... Would anyone have any tips on a good way to do this, preferably but not necessarily via "straight" SQL, rather than a WHILE-Loop, Cursor, Etc? Thanks, Steve (Still Around) in Memphis
