I have two files File A with 570 records and file B with morethan a 1000 records. In file B I have inactive records and I want to exclude them from the list. I did a left outer join from A to B and I have a where statement filtering the active records only in file B. My result is behaving like I did an inner join. Is there other way to make this work with out creating another hold file?
Here is my Code
JOIN
INNER C6AENB AND C6DCCD AND C6CVNB IN N06
TO UNIQUE SROCON AND SRDCCD
AND SRORD# IN WCSRLP00 AS J37
END
TABLE FILE N06
PRINT
'C6AENB'
'C6DCCD'
'C6CVNB'
'SRSRL#'
'SRLBNO'
WHERE WCSRLP00.WCSRLP00.SRATIV EQ 'A';
ON TABLE PCHOLD FORMAT HTML
END

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

This type of JOIN is new to an old 5.3 coder like me.

Did you turn SQL traces on to see what is passed to SQL? You specify INNER but you're expecting a left outer join?

Hi Francis,
I'm sorry I paste the wrong code. Here is my code that i'm having problem.
JOIN
LEFT_OUTER C6AENB AND C6DCCD AND C6CVNB IN N06
TO UNIQUE SROCON AND SRDCCD
AND SRORD# IN WCSRLP00 AS J37
END
TABLE FILE N06
PRINT
'C6AENB'
'C6DCCD'
'C6CVNB'
'SRSRL#'
'SRLBNO'
WHERE WCSRLP00.WCSRLP00.SRATIV EQ 'A';
ON TABLE PCHOLD FORMAT HTML
END

I might be crazy Noel, but it looks like you coded an inner join.

To do an outer, it should specify that in the join and there should be a SET ALL=ON prior.

Here is an interesting post on the status of joins in WebFOCUS that you might find useful.

https://forums.informationbuilders.com/eve/forums/a/tpc/...561030103#6561030103

Noel,

I am confused. How can you have an outer join when you only select where a value on the second table has a value? Once you test the second table for a value, you get an inner join. Perhaps you can change your code to
'- WHERE WCSRLP00.WCSRLP00.SRATIV EQ 'A' OR
WCSRLP00.WCSRLP00.SRATIV IS MISSING;