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,

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

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.

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

quote:

NOCLOSE

Thanks a lot!!

It works

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