I am new to WebFOCUS and I am just trying to learn the ropes...

[Peter Vazny]

I am new to WebFOCUS and I am just trying to learn the ropes, but I keep running into limits of JOINs. Especially the lack for cross join. I can think of a couple reasons to not include right joins, or full outer joins, assuming the system relies on a record from left table to always be there. However the lack of cross join is just baffling. I am assuming that there are some historical reason for it, but I just cannot think of any.

I am aware of the hold file workarounds, but that seems extremely inefficient since it cannot be translated to SQL and handled by the data source engine.

[NYCBabak .]

Designer gives you Left Right and Full joins. Admittedly, Ive not run into a situation where I have to have Cross Join. However, you could always use SQL Passthru for complex stuff like that.

[David Beagan]

Heres an example of doing a cross join of the car file with itself to get all combinations of five countries. It uses WebFOCUS conditional join without any where clause, result is 25 rows:

JOIN FILE CAR AT COUNTRY TAG A TO

ALL FILE CAR AT COUNTRY TAG B AS J1

END

TABLE FILE CAR

LIST A.COUNTRY

B.COUNTRY

END

 

Reference Creating Reports With WebFOCUS Language manual for join options, Using a Conditional Join.

[Peter Vazny]

A way to put my foot in my mouth

To my defense, this does not work in InfoAssist. I can make the edit as described and InfoAssist will replace it with

JOIN COUNTRY IN CAR TO MULTIPLE COUNTRY IN CAR TAG J001 AS J001

END

 

I swear I read that conditional join documentation just this morning and somehow I did not get the cross join out of it.

[Peter Vazny]

Good to know that this is available in the latest release.

Cross join is basically what all the MacGyver methods are based on and they all use an extra field with an empty character (space) to achieve cross join.

[NYCBabak .]

Understood. However, InfoAssist is meant for power users and while I agree cross join might be a nice to have, it could get a novice user into trouble.