Multi-record insert/append using sub-query list - and Binary Links

[Sims, John (CDC/CCHP/NCBDDD) (CTR)]

Hi Ruslan and Barry,

One word of caution for this solution.  I believe it would fail in a
multi-user environment.  If 2 users add records at the same time, you
don't know what you are going to get linked to what.

Perhaps we should re-open my request for SQLExecute to return an array
of affected RecIDs ;-)
Then, perhaps you (Ruslan) could add to Valentina a
LinkRecords(PrimaryRecID as Ulong, RecordsToLink() as Ulong) option.
Then to link the records, you pass your returned array of affected
RecIDs along with the Record you want to link to.

Something like that???

-John

> -----Original Message-----
> From: valentina-bounces at lists.macserve.net 
> [mailto:valentina-bounces at lists.macserve.net] On Behalf Of 
> Ruslan Zasukhin
> Sent: Thursday, July 19, 2007 3:48 AM
> To: valentina at lists.macserve.net
> Subject: Re: Multi-record insert/append using sub-query list 
> - and Binary Links
> 
> On 19/7/07 8:05 AM, "Barry G. Sumpter" 
> <BarrySum at BigPond.Net.AU> wrote:
> 
> Hi Barry,
> 
> Right. I think you can have in table OldOrders one more 
> utility Boolean field. And use it to mark new 100 records as TRUE.
> 
> Then you will be able use this
> 
>  LINK RECORD(1) OF Shops
>  WITH RECORDS
>  (SELECT OldOrders.RecID on Orders_1_M_OldOrders where 
> fldIsNew = TRUE)
> 
> Then you need cleanup this flag
> 
>     UPDATE OldOrders
>         SET   fldIsNew = FALSE
>         WHERE fldIsNew = TRUE
> 
> 
> > Just had a flash.
> > 
> > If I could somehow identify the records just inserted into 
> OldOrders 
> > as NOT having a Binary Link and then Updateing each of those with
> > 
> > LINK RECORD(1) OF Shops
> > WITH RECORDS
> > (SELECT OldOrders.RecID on Orders_1_M_OldOrders where 
> OldOrders.RecID 
> > = 0)
> 
> > ??????
> > 
> > 
> > 
> > -----Original Message-----
> > From: valentina-bounces at lists.macserve.net
> > [mailto:valentina-bounces at lists.macserve.net] On Behalf Of Barry G. 
> > Sumpter
> > Sent: Thursday, 19 July 2007 2:38 PM
> > To: 'Valentina Developers'
> > Subject: Multi-record insert/append using sub-query list - 
> and Binary 
> > Links
> > 
> > Hi all,
> > 
> > 
> > 
> > INSERT INTO OldOrders ( order_num, order_date, amount ) SELECT 
> > order_num, order_date, amount FROM orders WHERE order_date < 
> > '01-JAN-90'
> > 
> > Lets say that the sub query returned 100 orders records.
> > Lets also say that the OldOrders table has a binary link of 1 to M 
> > with a shop table.
> > i.e. each shop has many many OldOrders
> > 
> > is there a way to update the binary link with the same Insert?
> > 
> > If not, any suggestions?
> > 
> > I'd rather not have to execute a single Link Record command 
> for each 
> > of the 100 order records.
> 
> 
> --
> Best regards,
> 
> Ruslan Zasukhin
> VP Engineering and New Technology
> Paradigma Software, Inc
> 
> Valentina - Joining Worlds of Information http://www.paradigmasoft.com
> 
> [I feel the need: the need for speed]
> 
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
> 
>