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     [SOLVED] Union on 2 different hold files with unrelated columns

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Union on 2 different hold files with unrelated columns
 Login/Join
 
Member
posted
Hi,

I'm trying to do a union on two different hold files that have no related columns. Is there a simple way to do this? Basically, I just want to merge the CAR and EMPLOYEE table.

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CAR_HOLD
END
-RUN

TABLE FILE EMPLOYEE
PRINT *
ON TABLE HOLD AS EMPLOYEE_HOLD
END
-RUN

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
WebFOCUS 7705
Windows, All Outputs
 
Posts: 19 | Registered: October 28, 2016Report This Post
Virtuoso
posted Hide Post
Are you looking for a Carthesian product?
You can do that by adding a virtual dummy column to both tables and joining on that.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
Basically, you can't merge two tables that have nothing in common.
You could if you have the same number of column (field) and format in both files. To do so, you could use the MORE feature.

Merging CAR and EMPLOYEE files where they don't have the same number of fields and where "position columns matching" (col1 : CAR.COUNTRY vs col1 : EMP.EMP_ID) doesn't have the same format : A10 vs A9, can't be done.
Focus need to have the same column's name, number and format to be able to merge.

You could use MATCH OLD-OR-NEW to have both files "added together", but the column's from EMPLOYEE file will only be added beside the one from CAR file.
And even that, you need a common column (field) to perform the MATCH even if the value don't match (not same value).
To not have two different set of data as below sample, you need that the Matching key have the same value (DUMMY = 1 in both files) but you won't have the same data set : 37 rows using below sample vs 19 rows when DUMMY = 1 in both files.

SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
PRINT *
BY TOTAL COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS XCAR
END
-RUN

TABLE FILE EMPLOYEE
PRINT *
BY TOTAL COMPUTE DUMMY/A1 = '2';
ON TABLE HOLD AS XEMP
END
-RUN

MATCH FILE XCAR
PRINT
    COUNTRY
    CAR
    MODEL
    BODYTYPE
    SEATS
    DEALER_COST
    RETAIL_COST
    SALES
    LENGTH
    WIDTH
    HEIGHT
    WEIGHT
    WHEELBASE
    FUEL_CAP
    BHP
    RPM
    MPG
    ACCEL
BY DUMMY
RUN
FILE XEMP
PRINT
    EMP_ID
    LAST_NAME
    FIRST_NAME
    HIRE_DATE
    DEPARTMENT
    CURR_SAL
    CURR_JOBCODE
    ED_HRS
    BANK_NAME
    BANK_CODE
    BANK_ACCT 
    EFFECT_DATE
    DAT_INC
    PCT_INC
    SALARY
    JOBCODE
BY DUMMY
ON TABLE HOLD AS MRG OLD-OR-NEW
END
-RUN

TABLE FILE MRG
PRINT *
END
-RUN


Or as Wep5622 mentioned, if you want a Cartesian product, use JOIN with a DUMMY field. With this you should have 18 * 19 = 342 records
SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
PRINT *
BY TOTAL COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS XCAR FORMAT FOCUS
END
-RUN

TABLE FILE EMPLOYEE
PRINT *
BY TOTAL COMPUTE DUMMY/A1 = '1';
ON TABLE HOLD AS XEMP FORMAT FOCUS INDEX DUMMY
END
-RUN

JOIN 
              DUMMY IN XCAR
  TO MULTIPLE DUMMY IN XEMP TAG J1 AS J1
END

TABLE FILE XCAR
PRINT *
END
-RUN


Here a sample using MORE where column's name, number and format match in "MOREd" files
SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
PRINT COMPUTE COL1/A30 = FPRINT(COUNTRY, 'A10', 'A30');
      COMPUTE COL2/A20 = FPRINT(RETAIL_COST, 'D7', 'A20');
ON TABLE HOLD AS XCAR FORMAT FOCUS
END
-RUN

TABLE FILE EMPLOYEE
PRINT COMPUTE COL1/A30 = FPRINT(EMP_ID, 'A9', 'A30');
      COMPUTE COL2/A20 = FPRINT(HIRE_DATE, 'I6YMD', 'A20');
ON TABLE HOLD AS XEMP FORMAT FOCUS
END
-RUN

TABLE FILE XCAR
PRINT COL1
      COL2
MORE
FILE XEMP
END
-RUN

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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
Wep, Cartesian Product was actually exactly what I was looking for. I looked up how to do this and came across http://forums.informationbuild...1057331/m/7161077331 which is what I want.

Sorry for the duplicate post.


WebFOCUS 8
WebFOCUS 7705
Windows, All Outputs
 
Posts: 19 | Registered: October 28, 2016Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by MartinY:

JOIN
DUMMY IN XCAR
TO MULTIPLE DUMMY IN XEMP TAG J1 AS J1
END



MartinY,
Join to Multiple Dummy; you have me in tears.

Thanks for the laugh.

Have a great day Smiler


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report 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     [SOLVED] Union on 2 different hold files with unrelated columns

Copyright © 1996-2020 Information Builders