Hi all

I have to reports that come from one same DB in ORACLE, I have acomplished to build the two reports I need by using Across Command, now I want these two reports to MERGE or JOIN as if they were the same report.

Say My first reports is like this

AP LS FO SE NR TOTAL
SOCIEDAD
1000 1 2 3 4 5 15
2000 10 20 30 40 50 150
3000 100 200 300 400 500 1500

And the second one is like this

AFECTADO RESPONSABLE SIN VALOR TOTAL
SOCIEDAD
1000 $1,000 $2,000 $3,000 $6,000
2000 $10,000 $20,000 $30,000 $60,000
3000 $100,000 $200,000 $300,000 $600,000

And so I need the finall report to be like this

AP LS FO SE NR TOTAL AFECTADO RESPONSABLE SIN VALOR TOTAL
SOCIEDAD
1000 1 2 3 4 5 15 $1,000 $2,000 $3,000 $6,000
2000 10 20 30 40 50 150 $10,000 $20,000 $30,000 $60,000
3000 100 200 300 400 500 1500 $100,000 $200,000 $300,000 $600,000

The only main issue here is that the second report is always 4 columns width, meaning it will always have the 4 values AFECTADO, RESPONSABLE, SIN VALOR and TOTAL, but the first one may change since the DATA may not have all the values from AP to NR ...

So I need a way to know how many fields I have in the first ACROSS by reading the HOLD FILE and by that I can build a MATCH sintax like this

MATCH FILE HREP1
PRINT
E02
E03
.
.
.
E0N
BY E01
AFTER MATCH HOLD AS HREPFIN FORMAT FOCUS
MORE
FILE HREP2
PRINT
E02
E03
E04
E05
BY E01
END
-RUN

AND THEN SIMPLY printing the results on a final fex.

I wonder if there is a more simple solution, or if someone remember the command to be able to read the HOLD FILE and get how many E0N has the report and then be able to MATCH them on a single HOLD FILE.

I tried using the GUI to build the MATCH command but it seems to not be able to use ACROSS fields on the MATCH because when I issue the MATCH it only SHOWS the SOCIEDAD field on the LIST.

Thanks a lot in advance
BTW I'm a Mexican WF Developer.
Regards.

This message has been edited. Last edited by: <Kathryn Henning>,

Just a quick thought - why not have 2nd hold as host file and do a left outer join with 1st hold on E01

Ola Jose!
Two possible solutions. There are most probably a few more:
1.

 
-SET &ECHO=ALL;
-* File jose01.fex
-DEFAULT &GRP=7
SET ASNAMES=ON, HOLDLIST=PRINTONLY
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
ACROSS SEATS
ON TABLE HOLD AS J1 FORMAT FOCUS INDEX COUNTRY
END
-RUN
TABLE FILE CAR
SUM DEALER_COST
BY COUNTRY
ACROSS MPG IN-GROUPS-OF &GRP
ON TABLE HOLD AS J2 FORMAT FOCUS INDEX COUNTRY
END
-RUN
JOIN COUNTRY IN J2 TO COUNTRY IN J1 AS JJ
TABLE FILE J2
PRINT *
END

2.

  
-SET &ECHO=ALL;
-* File jose02.fex
-DEFAULT &GRP=7
SET ASNAMES=ON, HOLDLIST=PRINTONLY
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
BY SEATS
ON TABLE HOLD AS J1
END
-RUN
TABLE FILE J1
BY SEATS
ON TABLE HOLD AS ASIENTOS
END
-RUN
-SET &ASIENTOS=&LINES;
-RUN
TABLE FILE CAR
SUM DEALER_COST
BY COUNTRY
BY MPG IN-GROUPS-OF &GRP
ON TABLE HOLD AS J2
END
-RUN
TABLE FILE J2
BY MPG
ON TABLE HOLD AS CONSUMO
END
-RUN
-SET &CONSUMO=&LINES;
-RUN
DEFINE FILE J1
-REPEAT #DEF1 FOR &I FROM 1 TO &ASIENTOS;
-READFILE ASIENTOS
RCOST&I/D7=IF SEATS EQ &SEATS THEN RETAIL_COST ELSE 0;
-#DEF1
END
-RUN
-CLOSE ASIENTOS
DEFINE FILE J2
-REPEAT #DEF2 FOR &I FROM 1 TO &CONSUMO;
-READFILE CONSUMO
DCOST&I/D7=IF MPG EQ &MPG THEN DEALER_COST ELSE 0;
-#DEF2
END
-RUN
-CLOSE CONSUMO
-RUN
MATCH FILE J1
SUM
-REPEAT #TAB1 FOR &I FROM 1 TO &ASIENTOS;
-READFILE ASIENTOS
RCOST&I AS 'Seats &SEATS'
-#TAB1
-CLOSE ASIENTOS
BY COUNTRY
RUN
FILE J2
SUM
-REPEAT #TAB2 FOR &I FROM 1 TO &CONSUMO;
-READFILE CONSUMO
DCOST&I AS 'Mpg &MPG'
-#TAB2
-CLOSE CONSUMO
BY  COUNTRY
AFTER MATCH HOLD AS JFINAL OLD-OR-NEW
END
TABLE FILE JFINAL
PRINT *
END

In both solutions, use the parameter &MPG to vary the number of values, a sort of REPRO for your data.

Isn't this a case where a JOIN on E01 would work better than MATCH ?

But even with MATCH the correct syntax is:

MATCH FILE HREP1
PRINT
AP LS FO SE NR TOTAL
BY SOCIEDAD
RUN    -* Note I replaced MORE with RUN
FILE HREP2
PRINT
AFECTADO RESPONSABLE SIN VALOR TOTAL
BY SOCIEDAD
AFTER MATCH HOLD AS HREPFIN FORMAT FOCUS
END

Does MATCH FILE now support HOLD FORMAT FOCUS?

Yes, the syntax for MATCH FILE and AFTER MATCH HOLD [hold option] [AS name] FORMAT FOCUS [INDEX fieldname] has been around for 20 years or more.

Hi all

First of all thanks all for your replies.

I have given a try to the JOIN command before the ACROSS, and it works ... well I mean I have now the 2 reports in one big table file ... but now ... How can I have the headings or descriptions ... because the name of the FIELD or the description is lost when I make the ACROSS, meaning for Example that the value AP is Accidente/Pereció, LS is Lesiones, FO is Falleció, and the new fields is BSY Accidente/Perecio, then BSY Lesiones, and BSY Falleció, so the CODE or the SHORT name is LOST in the ACROSS.

It would be easy if I knew which element will come out from each value, but I don't, becasuse sometimes it will be only AP, and maybe sometimes is AP and FO, or the 3 or more, etc.

So I think I will have to use BY instead of ACROSS but if I do so, I will end up with multiple values, since for just value in AFECTADO I will have vales in AP, FO, LS, and so on ...

I'm gonna try to build up an example with CAR so, maybe, I will be more clear in what I need.

Thanks in advance.

So I have an example with CAR

 DEFINE FILE CAR
CAR_ID/A3=DECODE CAR( 'ALFA ROMEO' AR AUDI AU BMW BM DATSUN DS JAGUAR JG JENSEN JN MASSERATI MS PEUGEUO PG TOYOTA TY TRIUMPH TR ELSE NN);
END

TABLE FILE CAR
SUM 
     SALES
BY  LOWEST COUNTRY
ACROSS LOWEST CAR AS ''
ACROSS LOWEST CAR_ID AS ''
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE HOLD AS REP1 FORMAT FOCUS INDEX 'COUNTRY' 
ON TABLE SET HTMLCSS ON
END

TABLE FILE CAR
SUM 
     SEATS
BY  LOWEST COUNTRY
ACROSS LOWEST MODEL
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE HOLD AS REP2 FORMAT FOCUS INDEX 'COUNTRY' 
END

JOIN COUNTRY IN REP1 TO COUNTRY IN REP2 AS J1
END

TABLE FILE REP1
PRINT
*
END 

I end up with fields like SALALFA ROMEO SALAUDI SALBMW but I need two rows ... the first with AR and above ALFA ROMEO, then AU and above AUDI, then BM and above BMW as in the first report, without HOLDING the FILE ...


If there is a way to paste a PrintScreen from my PC would be great to know how ... I tried but I think with no luck at all.

I need exactly the REP1 TABLE FILE pasted besides the REP2 ... with the 2 lines for the 2 across in the first REP1 and one line in REP2 ...

What I managed to do is put the two reports in a TABLE in HTML and try to put them as together as possible in two columns but since the user has a small screen he can't see the second column unless he sees the below portion of the screen and realices there is a bar and moves it to the right (user right???!!! jajaja).

Thanks in advance.
Best Regards.

Jose,
You changed your requirements:
Now you say that for REP1 you have 2 levels of ACROSS and only 1 level for REP2.
What are your real requirements?

Would this be an answer?

  
-* File jose03.fex
SET ASNAMES = ON
DEFINE FILE CAR
CAR_ID/A2=DECODE CAR( 'ALFA ROMEO' 'IT' AUDI 'DE' BMW 'DE' DATSUN 'JP' JAGUAR 'GB' JENSEN 'GB' MASERATI 'IT' PEUGEUO 'FR' TOYOTA 'JP' TRIUMPH 'GB' ELSE NN);
UNIT/I6=SALES;
SEAT/I6=SEATS;
REP1/A1='1';
REP2/A1='2';
MM/A2=IF MODEL CONTAINS 'DOOR' THEN 'DO' ELSE 
      IF MODEL CONTAINS 'VELOCE' THEN 'VE' ELSE
      IF MODEL CONTAINS 'AUTO' THEN 'AU' ELSE 'MO';
BTYPE/A16=BODYTYPE;
END
TABLE FILE CAR
SUM 
     UNIT
BY COUNTRY
BY REP1 AS REP
BY CAR_ID AS ID
BY CAR AS FLD
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE HOLD AS REP  
END
FILEDEF REP DISK REP.FTM (APPEND
TABLE FILE CAR
SUM 
     SEAT
BY COUNTRY
BY REP2
BY MM
BY BTYPE
ON TABLE SAVB AS REP 
END
TABLE FILE REP
SUM UNIT
BY COUNTRY
ACROSS REP NOPRINT
ACROSS ID AS ''
ACROSS FLD AS ''
END 

Hi Danny

I have copied your code and it only gives me data from the second hold ... I assume the FILEDEF APPEND will cause the information from both reports to be in the final REP HOLD but it only keeps information from the last HOLD meaning I only get

PAGE 1

AU DO MO VE
CONVERTIBLE SEDAN COUPE SEDAN HARDTOP SEDAN COUPE ROADSTER
COUNTRY
ENGLAND 2 5 . . 2 4 . .
FRANCE . . . 5 . . . .
ITALY . . 2 4 . . 2 2
JAPAN . . . 8 . . . .
W GERMANY . . . 34 . . . .

but no information from the first REP ...

I copied and paste the code in my WF DEV STUDIO 8, so I don't know if I'm missing something, to be honest I had never used FILEDEF to append or concatenate two HOLDS, but I assume the APPEND does the trick, which for me is not working.

Thanks a lot for your response, I will try to make this work.
Best Regards.

Jose,

I have WF 8.0.0.7 and it works fine for me.
However, I'm on Windows, not on Linux.
Did you copy my example AS IS?
Check, maybe for Linux the FILEDEF command is a bit different.

Also, notice the "tricks".
I changed the format of BODYTYPE for it to the same as CAR. Same for SALES and SEATS. All this so that the fields saved in the HOLD file REP be consistent.

Suerte!

quote:

Yes, the syntax for MATCH FILE and AFTER MATCH HOLD [hold option] [AS name] FORMAT FOCUS [INDEX fieldname] has been around for 20 years or more.

For TABLE, not for MATCH, if you believe the documentation.

As of 8.1, the syntax is documented (http://documentation.informationbuilders.com/masterindex/html/html_wf_8103/wfcrlang/wfcrlang.pdf, p. 986) as follows, and does not include a FORMAT option:

Syntax: How to Specify Merge Phrases

AFTER MATCH HOLD [AS 'name'] mergetype
. . .
where:

AS 'name'

Specifies the name of the extract data source created by the MATCH command. The default
is HOLD.

mergetype

Specifies how the retrieved records from the files are to be compared.

(with diagrams for the various types: OLD, NEW, OLD-NOT-NEW, NEW-NOT-OLD, OLD-AND-NEW, OLD-OR-NEW, OLD-NOR-NEW)

This message has been edited. Last edited by: j.gross,

Jack & Alan,

Jack is right for the documentation.
Alan is right for the FORMAT of the output.
It goes to show that with WF, it is not enough to RTFM one should always try even when it is not ITFM!

Alan, where did you read that?

An old FOCUS 5 manual. It is also in one of Noreen's articles from the old System Journal.