topic How to use the below CROSS statement in a FOR loop in Operating System - OpenVMS

How to use the below CROSS statement in a FOR loop

panneer — Thu, 29 Nov 2007 07:59:19 GMT

We cross two files A and B over X(a common field) with TRADE(a field in A) and STATUS(a field in B).From the result,i need to delete the records in A that matches with B.
READY A WRITE
READY B SHARED
FIND ALL A CROSS B OVER X WITH TRADE STARTING WITH "FR" AND STATUS="OK"
ERASE ALL
How can the above code be written in a FOR loop,so that i can erase the required records?

Re: How to use the below CROSS statement in a FOR loop

Hoff — Thu, 29 Nov 2007 09:01:56 GMT

DATATRIEVE? Been a few years since I've coded in that. Good choice for ad-hoc queries, for its time. (InstantSQL (if you can find it; officially ISQL is long gone) was better for ad-hoc tasks, but I digress.)

The DATATRIEVE documentation is available here:

http://h30266.www3.hp.com/odl/i64lp/databases/dtr073/datrieve.htm

If you're programming in DATATRIEVE (as it appears here), the reference materials, as well as available programming examples and such are available there.

As for your request, I'd not expect that to work: "Do not use a FIND statement in a compound statement, such as a BEGIN-END, FOR, REPEAT, or THEN statement. (To establish single record context in compound statements, use the RSE clauses of FOR, PRINT, or MODIFY statements. These RSEs can establish the record streams needed to control the name recognition and single record context inside compound statements.)"

ERASE ALL removes the entirety of the current collection.

FIND establishes a collection.

Without running some tests, I'm not sure what might happen here, nor what you're specifically seeking to have happen. (I usually confirm DATATRIEVE syntax on a test copy of the database.)

Again, I'd suggest starting with a test database and with the manuals and with Yachts and other examples -- and you should get to where you need to be.

Stephen Hoffman
HoffmanLabs LLC

Re: How to use the below CROSS statement in a FOR loop

Hein van den Heuvel — Fri, 30 Nov 2007 04:44:56 GMT

I think you want something like:

FOR ok_b IN B WITH STATUS = "OK" PRINT ALL A
WITH x = ok_b.x AND TRADE STARTING WITH "FR";

If you like the result, then replace the PRINT with ERASE

The problem might be the scale of the number of records.

If B has millions of records with "OK"
and A has just a few records with trade starting with FR then the above is sub-optimal.

But for now... let's see if this handles the problem.

Hein