[CLOSED] Reading value from DM file into where clause of SQLOUT query
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,
WebFocus 7.6.8, AIX, PDF
April 07, 2010, 02:25 PM
GinnyJakes
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?
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
WebFocus 7.6.8, AIX, PDF
April 07, 2010, 02:54 PM
Francis Mariani
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"
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 07, 2010, 04:35 PM
Darin Lee
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....
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
April 07, 2010, 06:08 PM
Waz
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,
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
April 07, 2010, 09:47 PM
Ram Prasad E
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