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).
-* 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
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
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, $
-* <<< 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,
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 |
quote:John
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
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