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

[Parvez Mohammad]

Can someone please explain in Webfocus,

There are two tables which contains the same column.

How to search for field from one table which is being present in the list of other table?

If found then Yes else no.

That would be helpful.

Regards

Parvez

[Martin Yergeau]

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

[Parvez Mohammad]

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.

[Martin Yergeau]

This can be an option but it can have other options

From my sample above the result is

If 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

Where all records are displayed but only those that exist in both table have Y under EXIST column