Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     UNIONALL.FEX: Appends HOLD files with matching columns aligned

Read-Only Read-Only Topic
Go
Search
Notify
Tools
UNIONALL.FEX: Appends HOLD files with matching columns aligned
 Login/Join
 
Master
posted
Use of this fex is analogous to the UNION ALL statement in SQL.
Syntax: EXEC UNIONALL <inhold1>, <inhold2> [,<inholdn>...], <outhold> [, ECHO=[OFF|ON|ALL]] [,HOLDOPTS=<holdopttext>] [,APPHOLD=<application>]
        where <holdopttext> is hold options text for the final hold eg FORMAT SQLORA 
              <application> is the application where the final hold and master are to reside eg BASEAPP

UNIONALL takes n input hold files, <inhold1>, <inhold2> ... <inholdn>, aligns their like named columns, and concatenates them into an output hold file <outhold> (<outhold> is the final argument).


Like named columns with different format are adjusted to the best guess common format. eg longest alpha format or greatest length and precision for numeric. The last resort is conversion to a string.

It uses the WebFOCUS MORE command, but carries out the defines and format matching needed automatically.

The code for UNIONALL is posted on the forum as free to use without any warranties and users are invited to post comments, request corrections or add to the code.

Example:

-* test harness for UNIONALL

-* First input HOLD - shows format conversions carried out on your behalf
DEFINE FILE CAR
SEATSFRACTION/P13.4C = SEATS / 10 ;
MODEL/DMYY = IF SEATS LT 4 THEN '10/10/2007' ELSE '01/01/2001' ;
MODEL/YYMD = MODEL ;
RETAIL_COST/P12.2 = SEATSFRACTION ;
END
TABLE FILE CAR
PRINT
-* common to both holds
     COUNTRY
     MODEL
  RETAIL_COST
-* only in SEATS
  SEATS
     SEATSFRACTION
ON TABLE HOLD AS SEATS
END
-RUN
-* Second input HOLD
DEFINE FILE CAR
MODEL/A250 = MODEL ;
END
TABLE FILE CAR
PRINT
-* common to both holds
     RETAIL_COST
     COUNTRY
     MODEL

-* only in COST
     BHP
ON TABLE HOLD AS COST
END
-RUN
-* Combine the 2 NOTE the use of EXEC syntax and not EX
EXEC UNIONALL SEATS, COST, CARCOMB, ECHO=ON
-RUN
-* Examine the output as PDF to see spacings
TABLE FILE CARCOMB
PRINT *
ON TABLE PCHOLD FORMAT PDF
END
-EXIT


This produces the intermediate UNIONB.FEX that contains the following code

DEFINE FILE SEATS
BHP/D6 MISSING ON = ;
MODEL/A250 MISSING ON =  DATETRAN(MODEL,'(YYMD)','(/)','EN',00000250,'A250') ;
RETAIL_COST/D12.2 MISSING ON =RETAIL_COST ;
END
DEFINE FILE COST
RETAIL_COST/D12.2 MISSING ON =RETAIL_COST ;
SEATS/I3 MISSING ON = ;
SEATSFRACTION/P13.4C MISSING ON = ;
END
TABLE FILE SEATS
PRINT
BHP
COUNTRY
MODEL
RETAIL_COST
SEATS
SEATSFRACTION
ON TABLE HOLD AS CARCOMB
MORE
FILE COST
END


The combined output MASTER, CARCOMB, is

FILENAME=CARCOMB , SUFFIX=FIX     , $
 SEGMENT=CARCOMB, SEGTYPE=S0, $
 FIELDNAME=BHP, ALIAS=E01, USAGE=D6, ACTUAL=D08,
 MISSING=ON, $
 FIELDNAME=COUNTRY, ALIAS=E02, USAGE=A10, ACTUAL=A12,
 MISSING=ON, $
 FIELDNAME=MODEL, ALIAS=E03, USAGE=A250, ACTUAL=A252,
 MISSING=ON, $
 FIELDNAME=RETAIL_COST, ALIAS=E04, USAGE=D12.2, ACTUAL=D08,
 MISSING=ON, $
 FIELDNAME=SEATS, ALIAS=E05, USAGE=I3, ACTUAL=I04,
 MISSING=ON, $
 FIELDNAME=SEATSFRACTION, ALIAS=E06, USAGE=P13.4C, ACTUAL=P08,
 MISSING=ON, $


2. Code

-* <<< START COPY 
-****************************************************************************************
-* UNIONALL.FEX This is analogous to the UNION ALL statement in SQL,
-*              EXEC UNIONALL <inhold1>, <inhold2> [,<inholdn>...], <outhold> [, ECHO=[OFF|ON|ALL]] [,HOLDOPTS=<holdopttext>] [,APPHOLD=<application>]
-*              where <holdopttext> is hold options text for the final hold eg FORMAT SQLORA 
-*                    <application> is the application where the final hold and master are to reside eg BASEAPP
-*              UNIONALL takes n input hold files, <inhold1>, <inhold2> ... <inholdn>, aligns their like named columns,
-*              and concatenates them into an output hold file <outhold> (<outhold> is the final argument).
-*              Like named columns with different format are adjusted to the best guess common format. eg longest alpha format or
-*              greatest length and precision for numeric. The last resort is conversion to a string.
-*              It uses the WebFOCUS MORE command, but carries out the defines and format matching needed automatically.
-*
-* Example   EXEC UNIONALL SEATS, COST, CARCOMB
-*
-*           This takes HOLD files SEATS and COST and appends them together matching like named columns into a HOLD file named CARCOMB
-*
-*           EXEC UNIONALL PERIOD1, PERIOD2, PERIOD3, PERIOD4, PERIOD5, PERIOD6, PERIOD7, PERIOD8, PERIOD9, PERIOD10, PERIOD11, PERIOD12, ANNUAL
-*
-*           This takes HOLD files PERIOD1 to PERIOD12 and appends them together matching like named columns into a HOLD file named ANNUAL
-*
-*           EXEC UNIONALL SEATS, COST, COST, CARCOMB_&MRE_User, HOLDOPTS='FORMAT FOCUS INDEX COUNTRY SEATS',APPHOLD=USERTEMP
-*
-*           This takes HOLD files SEATS and COST twice and appends them to create an indexed FOCUS file in the USERTEMP application directory  
-*
-* Notes
-* 1. There are 2 ways you can install UNIONALL.FEX (see https://forums.informationbuilders.com/eve/forums/a/tpc/...057331/m/9041031482)
-* 1a. This code may reside on the Application Server in the APP PATH in file UNIONALL.FEX.
-*     If you are calling this from MRE you must use the EXEC UNIONALL syntax and not the shorter EX
-* 1b. This code may reside in the MRE domain you are calling it from in file UNIONALL.FEX.
-*     If you are calling this from MRE you must use the EX UNIONALL syntax and not the longer EXEC.
-* If you are using it outside the application path or outside the same MRE domain remember to supply the correct prefix
-* 3. Rename columns when you generate your input hold files by using field AS newname
-*    with ON TABLE SET ASNAMES ON.
-* 4. Limit columns in your input hold file with ON TABLE SET HOLDLIST PRINTONLY.
-* 5. Input hold files can be any type eg BINARY, ALPHA, XFOCUS, etc
-* 6. Despite having a fair bit of code this is every bit as fast as doing the job manually in
-*    terms of response time.
-* 7. Known limitations:
-*       DATETIME conversion not implemented yet.
-*       No account of commas and formatting in output size choice
-*       Does not handle I6DMY etc 
-*       Only known to work with wf 714 and 762.
-* 8. The &ECHO setting can be used for debugging or to echo the generated procedure for use independent of UNIONALL
-* Ver   By        Date        Modification
-* 1.01  hammo1j   12/05/2008  Originally written
-* 1.02  hammo1j   14/05/2008  EXEC replaces EX to ensure runs correctly when called from MRE
-* 1.03  hammo1j   16/05/2008  Now has no limit on the number of HOLDs concatenated
-* 1.04  hammo1j   11/07/2008  Retains original order of fields in the HOLDs with first HOLD given priority
-* 1.05  hammo1j   11/07/2008  Now handles all numeric options (S,B,C etc) except Date Masks  
-* 1.10  hammo1j   14/07/2008  Option for type of HOLD file and APPHOLD location  
-*********************************************************************************************************

-DEFAULTS &ECHO='OFF'
-DEFAULTS &CALLED='N'
-DEFAULTS &HOLDOPTS=' ' 
-DEFAULTS &FINALHOLDOPTS=' ' 
-DEFAULTS &APPHOLD=' ' 
-DEFAULTS &FINALAPPHOLD=' ' 
 
-* find number of arguments &1 &2 &3 etc
-SET &NARGS = 0 ;
-SET &NOMAX = 1 ;
-REPEAT UNIONLOOP1 WHILE &NOMAX EQ 1  ;
-SET &NARGS = &NARGS + 1 ;
-SET &ARG = '&' | &NARGS ;
-SET &NOMAX  = &ARG.EVAL.EXIST  ;
-UNIONLOOP1
 
-SET &NARGS = &NARGS - 1 ;
-TYPE UNIONALL CALLED WITH &NARGS ARGUMENTS
 
-IF &NARGS GE 3 THEN GOTO NO_ERROR;
-TYPE MINIMUM OF 3 ARGUMENTS EG EXEC UNIONALL SEATS, COST, CARCOMB NB COMMAS REQUIRED
-EXIT
-NO_ERROR
 
-* OUTHOLD is the last argument
-SET &ARG = '&' | &NARGS ;
-SET &OUTHOLD = &ARG.EVAL ;

-* Pass the options to the last hold if this is a top level call with 3 argumnents
-SET &FINALHOLDOPTS = IF &CALLED EQ 'N' AND &NARGS EQ 3 THEN &HOLDOPTS ELSE &FINALHOLDOPTS ;
-SET &FINALAPPHOLD  = IF &CALLED EQ 'N' AND &NARGS EQ 3 THEN &APPHOLD  ELSE &FINALAPPHOLD ;
 
-TYPE ============================================= UNIONALL utility starting Concat &1 &2 to &OUTHOLD
-SET &LCHOLD1 = &1 ;
-SET &LCHOLD1 = LOCASE(&LCHOLD1.LENGTH,ASIS(&LCHOLD1),&LCHOLD1) ;
 
-SET &LCHOLD2 = &2 ;
-SET &LCHOLD2 = LOCASE(&LCHOLD2.LENGTH,ASIS(&LCHOLD2),&LCHOLD2) ;
 
MATCH FILE SYSCOLUM
SUM USAGE AS USAGE1
    COLNO   AS COLNO1
    COLTYPE AS COLTYPE1
    LENGTH  AS LENGTH1
    SCALE   AS SCALE1
BY NAME
WHERE TBNAME EQ '&LCHOLD1'
RUN
FILE SYSCOLUM
SUM USAGE AS USAGE2
    COLNO   AS COLNO2
    COLTYPE AS COLTYPE2
    LENGTH  AS LENGTH2
    SCALE   AS SCALE2
BY NAME
WHERE TBNAME EQ '&LCHOLD2'
AFTER MATCH HOLD AS UNIONA OLD-OR-NEW
END
-RUN
-* retain the original order of the columns
DEFINE FILE UNIONA
COLSORT1/I9 = IF COLNO1 EQ MISSING THEN 99999 ELSE COLNO1 ;
COLSORT2/I9 = IF COLNO2 EQ MISSING THEN 99999 ELSE COLNO2 ;
END
TABLE FILE UNIONA
PRINT *
BY COLSORT1
BY COLSORT2
ON TABLE HOLD AS UNIONA
END
-*-EXIT
CMD DEL UNIONB.FEX
FILEDEF UNIONB DISK UNIONB.FEX ( APPEND
-RUN
-* define dummy fields in 2 not in 1 or requiring downcast format
-WRITE UNIONB APP HOLDDATA &FINALAPPHOLD
-WRITE UNIONB APP HOLDMETA &FINALAPPHOLD
-WRITE UNIONB DEFINE FILE &1
DEFINE FILE UNIONA
COMMONCOLTYPE/A8 =      IF COLTYPE1 EQ MISSING THEN COLTYPE2
                   ELSE IF COLTYPE2 EQ MISSING THEN COLTYPE1
                   ELSE IF COLTYPE1 NE 'DATE'    AND COLTYPE2 EQ 'DATE'     THEN 'CHAR'
                   ELSE IF COLTYPE1 EQ 'DATE'    AND COLTYPE2 NE 'DATE'     THEN 'CHAR'
                   ELSE IF COLTYPE1 EQ 'DATE'    AND COLTYPE2 EQ 'DATE'     THEN 'DATE'
                   ELSE IF COLTYPE1 EQ 'CHAR'     OR COLTYPE2 EQ 'CHAR'     THEN 'CHAR'
                   ELSE IF COLTYPE1 EQ 'VARCHAR'  OR COLTYPE2 EQ 'VARCHAR'  THEN 'VARCHAR'
                   ELSE IF COLTYPE1 EQ 'DOUBLE'   OR COLTYPE2 EQ 'DOUBLE'   THEN 'DOUBLE'
                   ELSE IF COLTYPE1 EQ 'PACKED'   OR COLTYPE2 EQ 'PACKED'   THEN 'PACKED'
                   ELSE IF COLTYPE1 EQ 'INTEGER'  OR COLTYPE2 EQ 'INTEGER'  THEN 'INTEGER'
       ELSE 'CHAR' ;
COMMONLENGTH/I8 = MAX(LENGTH1,LENGTH2) ;
COMMONSCALE/I8  = MAX(SCALE1,SCALE2) ;
COMMONUSAGE/A8  = IF COMMONCOLTYPE EQ 'DATE' THEN USAGE1
                  ELSE IF COMMONCOLTYPE EQ 'INTEGER' THEN 'I' || LJUST(3,PTOA(COMMONLENGTH,'(P3)','A3'),'A3')
                  ELSE IF COMMONCOLTYPE EQ 'PACKED' AND COMMONSCALE EQ 0   THEN 'P' || LJUST(3,PTOA(COMMONLENGTH,'(P3)','A3'),'A3')
                  ELSE IF COMMONCOLTYPE EQ 'PACKED' AND COMMONSCALE NE 0   THEN 'P' || LJUST(3,PTOA(COMMONLENGTH,'(P3)','A3'),'A3') || '.' ||  LJUST(3,PTOA(COMMONSCALE,'(P3)','A3'),'A3')
                  ELSE IF COMMONCOLTYPE EQ 'DOUBLE' AND COMMONSCALE EQ 0   THEN 'D' || LJUST(3,PTOA(COMMONLENGTH,'(P3)','A3'),'A3')
                  ELSE IF COMMONCOLTYPE EQ 'DOUBLE' AND COMMONSCALE NE 0   THEN 'D' || LJUST(3,PTOA(COMMONLENGTH,'(P3)','A3'),'A3') || '.' ||  LJUST(3,PTOA(COMMONSCALE,'(P3)','A3'),'A3')
                  ELSE IF COMMONCOLTYPE EQ 'VARCHAR' THEN 'A' || LJUST(5,PTOA(COMMONLENGTH,'(P5)','A5'),'A5') || 'V'
                  ELSE IF COMMONCOLTYPE EQ 'CHAR' THEN 'A' || LJUST(5,PTOA(COMMONLENGTH,'(P5)','A5'),'A5')
      ELSE 'UNKNOWN' ;
-*
COMMONOPTIONSA/A1 = IF USAGE1 CONTAINS '%' OR USAGE2 CONTAINS '%' THEN '%' ELSE ' ' ; 
COMMONOPTIONSB/A1 = IF USAGE1 CONTAINS 'B' OR USAGE2 CONTAINS 'B' THEN 'B' 
                    ELSE IF USAGE1 CONTAINS 'R' OR USAGE2 CONTAINS 'R' THEN 'R' 
                    ELSE IF USAGE1 CONTAINS '-' OR USAGE2 CONTAINS '-' THEN '-' 
                    ELSE ' ' ; 
COMMONOPTIONSC/A1 = IF USAGE1 CONTAINS 'C' OR USAGE2 CONTAINS 'C' THEN 'C' 
                    ELSE IF USAGE1 CONTAINS 'c' OR USAGE2 CONTAINS 'c' THEN 'c' 
                    ELSE ' ' ; 
COMMONOPTIONSD/A1 = IF USAGE1 CONTAINS 'S' OR USAGE2 CONTAINS 'S' THEN 'S' ELSE ' ' ; 
COMMONOPTIONSE/A1 = IF USAGE1 CONTAINS 'E' AND USAGE1 OMITS '!E'  
                    OR USAGE2 CONTAINS 'E' AND USAGE2 OMITS '!E' THEN 'E' 
					ELSE ' ' ; 
COMMONOPTIONSF/A1 = IF USAGE1 CONTAINS 'L' AND USAGE1 OMITS '!L'  
                    OR USAGE2 CONTAINS 'L' AND USAGE2 OMITS '!L' THEN 'L' 
					ELSE ' ' ; 
COMMONOPTIONSG/A2 = IF USAGE1 CONTAINS 'M' OR USAGE2 CONTAINS 'M' THEN 'M' 
                    ELSE IF USAGE1 CONTAINS 'N' OR USAGE2 CONTAINS 'N' THEN 'N' 
                    ELSE IF USAGE1 CONTAINS '!d' OR USAGE2 CONTAINS '!d' THEN '!d' 
                    ELSE IF USAGE1 CONTAINS '!D' OR USAGE2 CONTAINS '!D' THEN '!D' 
                    ELSE IF USAGE1 CONTAINS '!e' OR USAGE2 CONTAINS '!e' THEN '!e' 
                    ELSE IF USAGE1 CONTAINS '!E' OR USAGE2 CONTAINS '!E' THEN '!E' 
                    ELSE IF USAGE1 CONTAINS '!l' OR USAGE2 CONTAINS '!l' THEN '!d' 
                    ELSE IF USAGE1 CONTAINS '!L' OR USAGE2 CONTAINS '!L' THEN '!L' 
                    ELSE IF USAGE1 CONTAINS '!y' OR USAGE2 CONTAINS '!y' THEN '!y' 
                    ELSE IF USAGE1 CONTAINS '!Y' OR USAGE2 CONTAINS '!Y' THEN '!Y' 
                    ELSE ' ' ; 
COMMONOPTIONS/A8  = IF COMMONCOLTYPE EQ 'INTEGER' OR 'PACKED' OR 'DOUBLE' THEN 
                      COMMONOPTIONSA || COMMONOPTIONSB || COMMONOPTIONSC ||
					  COMMONOPTIONSD || COMMONOPTIONSE || COMMONOPTIONSF ||
					  COMMONOPTIONSG 
					ELSE ' ' ;
EXTRAFLD1/A300 =
-* two formats are the same so no action required 
   IF USAGE1 EQ USAGE2 THEN ' '
-* missing in 1 so same format as 2
   ELSE IF USAGE1 EQ MISSING THEN NAME || '/' || USAGE2 || ' MISSING ON = ; '
-* convert to string if commoncoltype is CHAR or VARCHAR
   ELSE IF (COMMONCOLTYPE EQ 'CHAR' OR 'VARCHAR') AND ( COLTYPE1 NE 'CHAR' OR 'VARCHAR') THEN
-* convert date to string
      ( IF COLTYPE1 EQ 'DATE' THEN
          NAME || '/' || COMMONUSAGE || ' MISSING ON =  DATETRAN(' || NAME || ',''(' || USAGE1   || ')''' ||  ',''(/)'',''EN'',' || EDIT(COMMONLENGTH) || ',''' || COMMONUSAGE  || ''') ; '
-* convert numeric to string
        ELSE
          NAME || '/' || COMMONUSAGE || ' MISSING ON =  PTOA(' || NAME || ',''(' || EDIT(USAGE1,'P$9999999')   || ')'',''' || COMMONUSAGE  || ''') ; '
       )
-* no need to convert to new format rely on wf assignment
   ELSE  NAME || '/' || COMMONUSAGE || COMMONOPTIONS || ' MISSING ON = ' || NAME || ' ; '  ;
EXTRAFLD2/A300 =
   IF USAGE2 EQ USAGE1 THEN ' '
   ELSE IF USAGE2 EQ MISSING THEN NAME || '/' || USAGE1 || ' MISSING ON = ; '
   ELSE IF (COMMONCOLTYPE EQ 'CHAR' OR 'VARCHAR') AND ( COLTYPE2 NE 'CHAR' OR 'VARCHAR') THEN
      ( IF COLTYPE2 EQ 'DATE' THEN
          NAME || '/' || COMMONUSAGE || ' MISSING ON =  DATETRAN(' || NAME || ',''(' || USAGE2   || ')''' ||  ',''(/)'',''EN'',' || EDIT(COMMONLENGTH) || ',''' || COMMONUSAGE  || ''') ; '
        ELSE
          NAME || '/' || COMMONUSAGE || ' MISSING ON =  PTOA(' || NAME || ',''(' || EDIT(USAGE2,'P$9999999')   || ')'',''' || COMMONUSAGE  || ''') ; '
      )
   ELSE  NAME || '/' || COMMONUSAGE || COMMONOPTIONS || ' MISSING ON = ' || NAME || ' ; '  ;
END
TABLE FILE UNIONA
PRINT EXTRAFLD1
WHERE USAGE1 NE USAGE2
WHERE EXTRAFLD1 NE ' '
ON TABLE SAVE AS UNIONB
END
-RUN
-WRITE UNIONB END
-* define dummy fields in 1 not in 2 or requiring down cast format
-WRITE UNIONB DEFINE FILE &2
TABLE FILE UNIONA
PRINT EXTRAFLD2
WHERE USAGE1 NE USAGE2
WHERE EXTRAFLD2 NE ' '
ON TABLE SAVE AS UNIONB
END
-RUN
-WRITE UNIONB END
-* now concatenate the files using MORE facility
-WRITE UNIONB TABLE FILE &1
-WRITE UNIONB PRINT
TABLE FILE UNIONA
PRINT NAME
ON TABLE SAVE AS UNIONB
END
-RUN
-WRITE UNIONB ON TABLE HOLD AS &OUTHOLD &FINALHOLDOPTS
-WRITE UNIONB MORE
-WRITE UNIONB FILE &2
-WRITE UNIONB END
-WRITE UNIONB -RUN
-WRITE UNIONB APP HOLDDATA 
-WRITE UNIONB APP HOLDMETA 
-CLOSE UNIONB
EXEC UNIONB.FEX ECHO=&ECHO
-RUN
-TYPE ================================================================================ ... UNIONALL utility finished
 
-* now use recursion to call UNIONALL with its basic 3 arguments as we move along the argument list building up OUTHOLD
-SET &NARGSM1 = &NARGS - 1 ;
-REPEAT UNIONLOOP2 FOR &I FROM 3 TO &NARGSM1
-SET &ARG = '&' | &I ;
-* pass the HOLDOPTS through to the final file in sequence 
-SET &FINALHOLDOPTS = IF &I EQ &NARGSM1 THEN &HOLDOPTS ELSE ' ' ;
-SET &FINALAPPHOLD  = IF &I EQ &NARGSM1 THEN &APPHOLD  ELSE ' ' ;
EXEC UNIONALL &OUTHOLD, &ARG.EVAL, &OUTHOLD, ECHO=&ECHO, CALLED='Y', FINALHOLDOPTS=&FINALHOLDOPTS.QUOTEDSTRING, FINALAPPHOLD=&FINALAPPHOLD.QUOTEDSTRING
-RUN
-UNIONLOOP2
-* END COPY >>>

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



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Virtuoso
posted Hide Post
Looks impressive John

I'm going to study and test this at the office.
There is a presentation at the summit on "JOIN".
I have that on my schedule.

Thanks for sharing.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
Thanks for your comments Frank

In the cold light of a day later this looks as if I have made it far too complicated.

1. Select the contents between -* <<< start copy and -* end copy >>>. Copy them to the clipboard and then paste them to a unionall.fex in your application path.

2. Wait until you need to concatenate 2 hold files then try this

EX UNIONALL HOLD1,HOLD2, HOLD3
-RUN

You should see this is a lot quicker than having to think about all the various considerations.



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Member
posted Hide Post
quote:
John


Tried to study and test UNIONALL example rut got a message:

"ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key UNIONB.FEX."
When I debug this error and try to execute an extract from the example I have get following output:

"CMD DEL UNIONB.FEX
FILEDEF UNIONB DISK UNIONB.FEX ( APPEND
? FILEDEF
TABLE FILE UNIONB.FEX
PRINT *
END
Could Not Find \UNIONB.FEX
Lname Device Lrecl Recfm Append Filename
============================================================
UNIONB DISK 0 V APP \UNIONB.FEX
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: UNIONB
BYPASSING TO END OF COMMAND"

Looks like it should be predefined master file for UNIONB.

Please let me know what else I have prepare to run your example


WebFocus 7.1.4
Windows XP
 
Posts: 17 | Registered: May 10, 2007Report This Post
Expert
posted Hide Post
Amsterdam, If that JOIN presentation at Summit is taught by Renee, its an absolutely required course. She rocks it.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Master
posted Hide Post
Walker

Thanks very much for your feedback.

If you're interested the problem is down to the fact that I did not spot this when testing.

The cause of the Problem is subtle difference between EX and EXEC

To retest: change EX UNIONB to EXEC UNIONB in UNIONALL.FEX making sure it is located on the server in an application path that is accessible. NB this does not need to be in your MRE domain.

The test code needs to change so that EX UNIONALL becomes EXEC UNIONALL. The test code can either be in an application directory or you can continue to keep it in MRE.

Alternatively you can cut and paste updated versions (now 1.02 thanks to the bug you spotted) of UNIONALL and the test harness from the first post of this thread.

btw This gives a flavour of issuing code out to the wide world rather than the limited users of your environment.

IB have to do this all the time and you appreciate how much there is to go wrong and how much they get right with each release!



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Member
posted Hide Post
John,

Here are some additional details from my testing experience:

For my test it was more complicated to save UIONALL.FEX on the server in App path because it's easier to make changes keeping it in MRE.
In this case I just left EX call in testing code.
But in the body of UNIONALL.FEX, you absolutely right, to call UNIONB should be used EXEC call because UNIONALL.FEX creates file UNIONB.FEX in App. path on the server.
So, finally, my working combination is EX in testing code and EXEC in UNIONALL.FEX.

Thank you for a great technique!


WebFocus 7.1.4
Windows XP
 
Posts: 17 | Registered: May 10, 2007Report This Post
Master
posted Hide Post
Walker

Thanks for your input. I have got some ideas to improve this.

I would be interested in feedback from someone with a sub 7.1 release as I think I might have to change some of the functions used.

You are always learning and that EX vs EXEC was a new one on me!



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Member
posted Hide Post
I am willing to help you with feedback for 7.1 release and will do my best but it's probably for a limited time because we have plans move to 7.6.


WebFocus 7.1.4
Windows XP
 
Posts: 17 | Registered: May 10, 2007Report This Post
Master
posted Hide Post
Walker

Thanks again for your time.

I have upgraded UNIONALL to v1.03

Now this takes any number of hold files, aligns their like named columns, and concatenates them into a single output hold file with a superset of all fields in the input holds.

Hold files can be repeated in the input.

It is very simple to use and very efficient.

Example: EXEC UNIONALL PERIOD1, PERIOD2, PERIOD3, PERIOD4, PERIOD5, PERIOD6, PERIOD7, PERIOD8, PERIOD9, PERIOD10, PERIOD11, PERIOD12, ANNUAL

This takes HOLD files PERIOD1 to PERIOD12 and appends them together matching like named columns into a HOLD file named ANNUAL

The old 3 files syntax will still work so there will be no change to existing code required.



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Master
posted Hide Post
I have upgraded UNIONALL and the new code is posted in the header.

quote:
1.04 hammo1j 11/07/2008 Retains original order of fields in the HOLDs with first HOLD given priority
1.05 hammo1j 11/07/2008 Now handles all numeric options (S,B,C etc) except Date Masks
1.10 hammo1j 14/07/2008 Option for type of HOLD file and APPHOLD location



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     UNIONALL.FEX: Appends HOLD files with matching columns aligned

Copyright © 1996-2020 Information Builders