Hi,

I have searched the forum and understand that supposedly, the following is correct:

Inner Join:

SET ALL = OFF  

Left Outer Join:

SET ALL = ON  

BUT no matter what I try I get LEFT JOIN in both cases.

So I am trying with some sample data as follows

TABLE1
T1CODE T1DESC
1 ITEM_1_T1
2 ITEM_2_T1
3 ITEM_3_T1

TABLE2
T2CODE T2DESC
3 ITEM_3_T2
4 ITEM_4_T2
5 ITEM_5_T2
9 ITEM_9_T2

EXAMPLE 1:

SET ALL = OFF
JOIN CLEAR *
JOIN T1CODE IN TABLE1 TO T2CODE IN TABLE2 AS J1
END

TABLE FILE TABLE1
PRINT
T1CODE
T1DESC
T2CODE
T2DESC
END  

PRODUCES:

T1CODE T1DESC T2CODE T2DESC
1 ITEM_1_T1 . .
2 ITEM_2_T1 . .
3 ITEM_3_T1 3 ITEM_3_T2

EXAMPLE 2:

SET ALL = ON
JOIN CLEAR *
JOIN T1CODE IN TABLE1 TO T2CODE IN TABLE2 AS J1
END

TABLE FILE TABLE1
PRINT
T1CODE
T1DESC
T2CODE
T2DESC
END  

PRODUCES:

T1CODE T1DESC T2CODE T2DESC
1 ITEM_1_T1 . .
2 ITEM_2_T1 . .
3 ITEM_3_T1 3 ITEM_3_T2

WEIRD EXAMPLE 3 (WITH ALL = OFF AND AN ALL IN THE JOIN:

SET ALL = OFF
JOIN CLEAR *
JOIN T1CODE IN TABLE1 TO ALL T2CODE IN TABLE2 AS J1
END

TABLE FILE TABLE1
PRINT
T1CODE
T1DESC
T2CODE
T2DESC
END  

PRODUCES:

T1CODE T1DESC T2CODE T2DESC
3 ITEM_3_T1 3 ITEM_3_T2


I am totally confused, because surely things should not work this way...from what I have read on this forum it seems very odd.

Maybe someone can explain why this may be?

Thanks!

Jinx.

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

Not sure if this is what you're trying to do



For left outer join

JOIN
LEFT_OUTER F0006.F0006.ADDRESS_NUMBER IN F0006 TO MULTIPLE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG J0 AS J0
END


For inner join

JOIN
INNER F0006.F0006.ADDRESS_NUMBER IN F0006 TO MULTIPLE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG J1 AS J1
END


Sayed

Hi!

Thanks for the reply.

I have used the LEFT_OUTER before, but the issue here is that I am trying to rewrite a complicated report written in FOCUS so that it can be used locally in webFOCUS. This report uses tons of complex joining, I had assumed joining worked a certain way, but cannot seem to replicate the results expected for the report - so needed some clarification on where my logic is faling on how the standard join syntax works.

I didn't know one could specify 'INNER' in the join so did learn something. ;-)

Thanks anyway.

Jinx.

Hello Jinx,
i am assuming that u r working with focus files. in wf Left outer, Inner or other joins doesnt exist. these type of joins exists in RDBMS systems like oracle etc....
when you are trying to create an inner join give all option e.g.
join abc in xyz to all abc in def as jo1
end
else you can write the same thing what you have give as eg and put a where condition where the fields of cross reference table is not blank.
if you are creating a unique join then it will act as left outer even if you set all parameter = off. sometimes this problem occurs if you are trying to join any RDBMS table to a focus table(.foc).
when i have to create a left outer i use SET ALL=PASS and that works for me.
hope this helps.

We should assume you're working with SQL Server 2000 or 2008 tables.

Did you turn SQL tracing on to see the SQL generated by WebFOCUS? If you turn SQL tracing on, you will get some very informational messages about the JOINs...

SQL Traces

O.o I am as thick as a brick, it never occurred to me to do that! Good point!

Thanks Francis for point out the obvious solution to my woes .

I have the SQL trace commands in a fex that I include an ALL my programs. As well, when I start writing a new report, I always SET XRETRIEVAL=OFF so no data is retrieved and the trace is immediately displayed. This makes development a lot more efficient and no amount of pointing-and-clicking in a GUI will replace these two basic ideas.

Cheers,