Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

How to search for a data from A table being present in the list of B table.


Parvez Mohammad

Recommended Posts

Can you clarify please.

In your post subject you are talking about data from table A that exist in B but description you are talking about field from A that exist in B.

And you say that both table have same column.

It's not the same thing.

So I think that you want to retreive data that match from both tables ?

One way is to use the MATCH feature.

In my sample I creates two tables where TMP1 have all records and TMP2 only few MODEL.

I did that just for sample purpose because for sure that I would not perform such a thing to filter MODEL that have more than 4 SEATS.

ibisamp file does not have two tables with same data so, I create my own.

Then I MATCH the two tables using OLD-AND-NEW to include only records from both table that have same MODEL (first BY field in the MATCH)

TABLE FILE CARSUM RETAIL_COST DEALER_COSTBY MODELBY CARBY COUNTRYON TABLE HOLD AS TMP1END-RUN TABLE FILE CARPRINT SEATSBY MODELWHERE SEATS GT 4;ON TABLE HOLD AS TMP2END-RUN MATCH FILE TMP1PRINT RETAIL_COST DEALER_COSTBY MODELBY CARBY COUNTRYRUNFILE TMP2PRINT SEATSBY MODELAFTER MATCH HOLD AS EXTDATA OLD-AND-NEWEND-RUN TABLE FILE EXTDATAPRINT RETAIL_COST DEALER_COST SEATSBY COUNTRYBY CARBY MODELON TABLE SET PAGE-NUM NOLEADEND-RUN

You have several options when using MATCH

Look at chapter 15 : TIBCO WebFOCUS® Creating Reports With TIBCO® WebFOCUS Language Release 9.1.0

Link to comment
Share on other sites

Hi Martin,

Thank you for your message.

My query is:

Both the tables TABLE A and TABLE B contains same column name(PARTS).

TABLE A contains column name PARTS.

Also, TABLE B contains the same column name PARTS.

I will create a new column (Column name - NEW).

I want to print the PARTS data from TABLE A being present in the list of PARTS data from TABLE B.

If the PARTS data is present, I will print YES in the new column else print NO in the new column.

Could you please help me with this query?

Thank You.

Link to comment
Share on other sites

This can be an option but it can have other options

From my sample above the result is

image.png.7ba75f9ec1e94ebcee739f790f7f188b.pngIf I change a little the code by this

TABLE FILE CARSUM RETAIL_COST DEALER_COSTBY MODELBY CARBY COUNTRYON TABLE HOLD AS TMP1END-RUN TABLE FILE CARPRINT SEATSBY MODELWHERE SEATS GT 4;ON TABLE HOLD AS TMP2END-RUN MATCH FILE TMP1PRINT RETAIL_COST DEALER_COSTBY MODELBY CARBY COUNTRYRUNFILE TMP2PRINT SEATSBY MODEL-*-* Changed MATCH option from sample 1AFTER MATCH HOLD AS EXTDATA OLD-OR-NEWEND-RUN TABLE FILE EXTDATAPRINT RETAIL_COST DEALER_COST SEATS-*-* New COMPUTE from sample 1 COMPUTE EXIST /A1 = IF SEATS EQ 0 THEN 'N' ELSE 'Y';BY COUNTRYBY CARBY MODELON TABLE SET PAGE-NUM NOLEADEND-RUN

The result is now that

image.png.7892c3b7f2895b93267fd1926834434b.pngWhere all records are displayed but only those that exist in both table have Y under EXIST column

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...