Checking for Duplicates, Except
Let me point out some things that I know can be improved so that other answers can focus on my problem.


SELECT *



I shouldn't be selecting all like this. Even if there's not really a performance hit because open query needs to get all of the meta data anyway, this is a bad habit to get into. Selects should be explicit in what we're selecting.


'SELECT  SUBSTR(NAME, 1, 3) AS CATID,



CATID isn't a very good alias. CategoryId would be much better and clearer to someone following me someday.


   WHERE  DBSTATUS IN (1,2)



This needs a comment saying what these DBSTATUSes actually represent. Mr. Maintainer would have to go find the lookup table in order to find out. (And this query gives no hint that the lookup table even exists.)