Left join with where clause querying the destination table

[#1]

Hi Everyone,

I have looked in the forum and found some info that said I can't reference the target table in the where clause of a join. I am trying to do this and it is 'obviously' not working. I'm hoping someone can help me solve the issue with a QV way of doing things. Here is my code:

REGISTRATION_FLAGS_2B:
LOAD RPT9_ACCOUNT
 ,RPT9_CAT2
RESIDENT REGISTRATION_FLAGS;

LEFT JOIN (REGISTRATION_FLAGS_2B)
LOAD RPT9_ACCOUNT
 ,RPT9_CAT2_TMP
RESIDENT REGISTRATION_FLAGS_2A
WHERE RPT9_CAT2 <> RPT9_CAT2_TMP;

So basically I want the table to be only Account #'s when the instances of CAT2 and CAT2_TMP are NOT the same.

Any help = Greatly appreciated!

Thanks

Gareth

[#2]

I would just do the join like you have there and then add another table like:

NewTableName:

NOCONCATENATE LOAD

[RPT9_ACCOUNT],

[RPT9_CAT],

[RPT9_CAT2_TMP]

RESIDENT REGISTRATION_FLAGS_2B

WHERE [RTP9_CAT2]<>[RTP9_CAT2_TMP];

DROP TABLES REGISTRATION_FLAGS_2B;