Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Reading from FOCUS file and looping
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Reading from FOCUS file and looping
 Login/Join
 
Gold member
posted
Need some advice on how to code this senario.

I have a focus file with n number (this number is not constant) of datamart names. I need to append all the datamart specified in the focus file.

How can I loop to get all the datamart names and append them together, or is there an easier way to do that??

FOCUS DB
abc
def
.
.
.
Datamartn

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


WF: 7.6.8
OS/Platform: Windows, Unix
 
Posts: 55 | Registered: May 25, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
I would create a flat file (SAVE/HOLD) of datamart names. Then I would use a Dialogue Manager loop to read and process each datamart.

Something like this example:

SET HOLDLIST=PRINTONLY
SET PAGE=NOLEAD

TABLE FILE CAR
PRINT
COUNTRY
BY COUNTRY NOPRINT
ON TABLE SAVE AS S001
END
-RUN

-SET &NBR_COUNTRY = &LINES;

-REPEAT END_REP1 &NBR_COUNTRY TIMES

-READ S001 NOCLOSE &COUNTRY.A10.

TABLE FILE CAR
SUM SALES
BY CAR
BY MODEL

HEADING
"SALES FOR <COUNTRY"
WHERE COUNTRY EQ '&COUNTRY'
END
-RUN

-END_REP1


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks!


This is what am doing right now

TABLE FILE XXX
LIST
COMPUTE DM_NAME_JUST/A30 = LJUST(30,DATAMART_NAME,DM_NAME_JUST) ;
ON TABLE HOLD AS YYY
END
-RUN

-SET &NUMRECS = &RECORDS;


-SET &I = 0;
-STARTLOOP
-SET &I = &I + 1;
-READ YYY &LEVEL.&I.A30.

-SET &JOINER = 'J' || &I ;

JOIN STORE_NUMBER IN AZ_HIERARCHY TO STORE_NUM IN &LEVEL.&I AS &JOINER
-RUN

-IF &I LT &NUMRECS GOTO STARTLOOP;


But right now when I read from YYY, the vaule is corrupted.I get a unix return in front of the value

eg: abcdef.

I tried LJUST, TRUNCATE but its not working. It it something wrong with my flat file. I checked my flat file. It looks good.


WF: 7.6.8
OS/Platform: Windows, Unix
 
Posts: 55 | Registered: May 25, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
A few things wrong:

Don't use LIST, use PRINT or SUM ... BY. List adds an extra field in the output hold file.

Add HOLDLIST=PRINTONLY to make sure only the fields you really need are in the HOLD file. That COMPUTE will cause DATAMART_NAME and DM_NAME_JUST to be in the HOLD file.

Use NOCLOSE with -READ when the Dialogue Manager loop does other things than Dialogue Manager.

Use HOLDFORMAT=ALPHA when you want to -READ to ensure that the field lengths arte what you think they are.

Don't forget to check for end of file with IORETURN.

I prefer using a -REPEAT statement, much cleaner code.

You don't really need the &I counter.

So many lessons in one short program!

-SET &ECHO=ALL;

SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
TABLE FILE CAR
PRINT
COMPUTE DM_NAME_JUST/A30 = LJUST(30,COUNTRY,DM_NAME_JUST) ;
ON TABLE HOLD AS YYY
END
-RUN

?FF YYY

-SET &NUMRECS = &RECORDS;

-SET &I = 0;

-STARTLOOP
-SET &I = &I + 1;
-READ YYY NOCLOSE &LEVEL.&I.A30.

-IF &IORETURN NE 0 GOTO ENDLOOP;

-SET &JOINER = 'J' || &I ;

JOIN STORE_NUMBER IN AZ_HIERARCHY TO STORE_NUM IN &LEVEL.&I AS &JOINER
-RUN

-IF &I LT &NUMRECS GOTO STARTLOOP;

-ENDLOOP


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
quote:
NOCLOSE



Thanks a lot!!

It works Smiler Smiler Smiler


WF: 7.6.8
OS/Platform: Windows, Unix
 
Posts: 55 | Registered: May 25, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Just a thought,

Are all the FOCUS datamarts the same ?

i.e. Are the masters the same.

If so, then you could use USE.

e.g.
USE
FOCFILE1.FOC AS MYFOCFILE
FOCFILE2.FOC AS MYFOCFILE
FOCFILEn.FOC AS MYFOCFILE
END

JOIN STORE_NUMBER IN AZ_HIERARCHY TO STORE_NUM IN MYFOCFILE AS J1


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Reading from FOCUS file and looping

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.