I am trying to read through a DM File using the -REPEAT command to issue -READ commands. The following syntax will not execute. I have tried setting the &STAKEHOLDID to other variables using -SET but that does not work either. By the way the value in &STAKEHOLDERIDcan be a very long signed integer such as -2945354156300304384.

TABLE FILE EIS_PA_OFFICERSTAKEHDRS
PRINT STAKEHOLDERID
WHERE APPLICATIONID = &appid
-*ON TABLE SAVE AS SAVE01
ON TABLE HOLD AS SAVE01 FORMAT BINARY
END
-RUN
-START
-DEFAULTH &STAKEHOLDERID=0
-READ SAVE01, NOCLOSE &STAKEHOLDERID
-REPEAT THE_LOOP WHILE (&IORETURN EQ 0);
SET EMPTYREPORT = ON
SQL SQLORA PREPARE SQLOUT FOR
SELECT NAMEPREFIX,
DECODE(NOOWNERSHIPINTERESTIND, '0', 'No', '1','Yes') AS OwnershipInterestYesNo,
DECODE(NOPOSITIONSIND, '0','No', '1','Yes') AS HoldsPositionsYesNo,
DECODE(NOTCURRENTLYREGISTEREDIND, '0','No', '1','Yes') AS CurrentlyRegisteredYesNo,
APPLICATIONID,
STAKEHOLDERID
FROM EIS_PA_OFFICERSTAKEHDRS
WHERE STAKEHOLDERID = &STAKEHOLDERID
END
DEFINE FILE SQLOUT
DATEOFBIRTH/MDYY=HDATE(DATEOFBIRTH, 'MDYY');
APPLICATIONID/I6=APPLICATIONID;
ADDRESS/A40=ADDRESS;
END
TABLE FILE SQLOUT....

This message has been edited. Last edited by: Kerry,

It isn't apparent why the code isn't working but might I suggest that creating a WHERE IN phrase using DM might be more efficient from the Oracle side as you would only do one connect and bring back all of the data as opposed to bringing back one stakeholder at a time?

Also what happens if you remove the FORMAT BINARY?

Ginny,

I am using the &stakeholderid in 8 reports which are included in the Loop. For example, an organization may have three stakeholders each of which would be reported using the 8 reports and we would then move onto the next stakeholder (subsequent read).

Hope this adds clarification on why I need to bring back one stakeholder at a time.

Thanks

You could use compound reports and break for every stakeholder.

The first thing you need to do is get the loop to work without the 8 reports inside the loop. Once the loop works, add the reports. Why a binary save?

Why a comma after the file name? "A space after filename denotes a fixed-format file, while a comma after filename denotes a free-format file"

quote:

WHERE APPLICATIONID = &appid

should be WHERE APPLICATIONID EQ &appid ; And if the integer can be negative you may want to include it inside parentheses. ( &appid );

Without seeing all the code and the data behind it, we can't easily see why it might not be working. Could be lots of things. What is the error message you're getting?

I would follow Francis' advice and get one report working for one stakeholder. Then get one report working for all stakeholders, then 8 reports for all stakeholders. Baby steps....

I think thast Francis has hit the nail on the head.

If the STAKEHOLDERID is numeric, then using SAVE FORMAT BINARY will create a binary value.

SAVE FORMAT ALPHA would be better.

Have you tried -SET ECHO=ALL; to see what is happening.

This message has been edited. Last edited by: Waz,

quote:

the value in &STAKEHOLDERIDcan be a very long signed integer such as -2945354156300304384.

For Integer format, the number of digits can be a maximum of 10 plus a minus sign for negative value. Go for double precision decimal or packed instead of Integer.

Check the field format for STAKEHOLDERID in the table EIS_PA_OFFICERSTAKEHDRS. Issue this statement

?FF EIS_PA_OFFICERSTAKEHDRS
-EXIT

Why specific in Binary format? More over as Darin mentioned, use EQ in TABLE FILE .. WHERE APPLICATIONID EQ &appid.

You are trying to read from a free-format file.
-READ SAVE01, NOCLOSE &STAKEHOLDERID

quote:

long signed integer

What format is STAKEHOLDERID ?

I assume it is not an integer.

Assuming SAVE01 is not used again, apart from the -READ, change it to SAVE, and the output will tell you the size to read in.