I have not done this in quite some time. I am having issues doing an inner join on two hold files. I must be forgetting something pretty basic.

I have created something sample in the CAR file to illustrate. I guess the issue is the main hold file has multiple entries. The second hold file should be limiting the rows returned based on the inner join...it does not.

Try running this-it does not make much sense to do something like this..but I think it gets the point across...


-*
-* Hold File - All Cars and their Origins
-*
TABLE FILE CAR
BY LOWEST CAR.ORIGIN.COUNTRY
BY LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_DTL FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END

-*
-* For sake of illustration, create a second hold file with ONE record (Toyota)
-*
TABLE FILE CAR
BY LOWEST CAR.COMP.CAR
WHERE CAR.COMP.CAR EQ 'TOYOTA';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_ONECAR FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END


-*
-* Inner Join Hold file of all Cars/Origins to Hold File w/Toyota
-* Expect to get 1 entry--Get Multiple---not sure why?
-*
JOIN
INNER HOLD_DTL.HOLD_DTL.CAR IN HOLD_DTL TO UNIQUE HOLD_ONECAR.HOLD_ONE.CAR
IN HOLD_ONECAR TAG J0 AS J0
END
TABLE FILE HOLD_DTL
PRINT
HOLD_DTL.HOLD_DTL.COUNTRY
HOLD_DTL.HOLD_DTL.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END

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

1- Use FOCUS file
2- Index your key
3- Reverse your JOIN

TABLE FILE CAR
BY COUNTRY
BY CAR
ON TABLE HOLD AS HOLD_DTL FORMAT FOCUS INDEX CAR
END
-RUN
-*
-* For sake of illustration, create a second hold file with ONE record (Toyota)
-*
TABLE FILE CAR
BY CAR
WHERE CAR EQ 'TOYOTA';
ON TABLE HOLD AS HOLD_ONECAR FORMAT FOCUS
END
-RUN

-* 
-* Inner Join Hold file of all Cars/Origins to Hold File w/Toyota
-* Expect to get 1 entry--Get Multiple---not sure why?
-* 
JOIN
      CAR IN HOLD_ONECAR
   TO CAR IN HOLD_DTL  TAG J0 AS J0
END
TABLE FILE HOLD_ONECAR
PRINT COUNTRY
      CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END

The basic rule for joining hold files is that the data has to be sorted by the same columns.

You have to consider that the join is a effectively a dumb join, as there are no indexes, its just a flat file with the order the rows are stored in. The join will read from the first file and check for records in the second. As soon as the sequence is out it will not join to anything.

You have BY COUNTRY BY CAR in the first and BY CAR in the second.

Change the first to be BY CAR BY COUNTRY, and it should work.

Thank you both...I have either been lucky or just joining master files in the past I guess.

Which way is more efficient? I would think the FOCUS file.

FYI..not much luck with the non indexed solution. On the Indexed solution, why would the order of the tables matter in the join.

RobertF

Are you IT trained? Have you access to someone who can be seated with you to explain indexing and it is play in join?

Kofi

quote:

why would the order of the tables matter in the join

As suggested by Kofi a speak with a DBA or developer may help. And read about JOIN and connector in IB documentation.

But as fast answer it's all a question of parent-child, data hierarchy, connector properties and DB structure.
You normally put the must restrictive file (parent) at first and join to the child but it may depend on the DB structure (star, snowflake)

In your case the HOLD_ONECAR is used as a filtering table since you only want to see HOLD_DTL where CAR is in HOLD_ONECAR.
So, better to have HOLD_ONECAR in first place in the JOIN. The join will only exist for record that meet criteria

That said, you may use some SET or JOIN options to end with a totally different result even with the same code.
It result that way because the defaults SET and JOIN have been used.

This is the beauty of WebFOCUS

From an efficiency point of view, you should think about overheads and time to do the join. Creating a HOLD file takes less time than a FOCUS file. Also as an example creating a HOLD file without sorting takes less time than one with sorting.

Also depends on number of records. The less the records the less impact.

There are many factors