Outer Join with Where returning Nulls

Question

Hi I have 2 tables. I want to list

1. all records in table1 which are present in table2
2. all records in table2 which are not present in table1 with a where condition

Null rows will be returned by table1 in second condition but I am unable to get the query working correctly. It is only returning null rows

SELECT 
    A.CLMSRNO,A.CLMPLANO,A.GENCURRCODE,A.CLMNETLOSSAMT,
    A.CLMLOSSAMT,A.CLMCLAIMPRCLLOSSSHARE
FROM 
    PAKRE.CLMCLMENTRY A 
RIGHT OUTER JOIN (
    SELECT 
        B.CLMSRNO,B.UWADVICETYPE,B.UWADVICENO,B.UWADVPREMCURRCODE,
        B.GENSUBBUSICLASS,B.UWADVICENET,B.UWADVICEKIND,B.UWADVYEAR,
        B.UWADVQTR,B.ISMANUAL,B.UWCLMNOREFNO 
    FROM
        PAKRE.UWADVICE B 
    WHERE
        B.ISMANUAL=1
) r
ON a.CLMSRNO=r.CLMSRNO

ORDER BY 
    A.CLMSRNO DESC;

Answer 1

Which OS are you using ?

Table aliases are case sensistive on some platforms, which is why your join condition ON a.CLMSRNO=r.CLMSRNO fails.

Try with A.CLMSRNO=r.CLMSRNO and see if that works

Answer 2

I'm not understanding your first attempt, but here's basically what you need, I think:

SELECT *
FROM TABLE1
INNER JOIN TABLE2
    ON joincondition

UNION ALL

SELECT *
FROM TABLE2
LEFT JOIN TABLE1
    ON joincondition
    AND TABLE1.wherecondition
WHERE TABLE1.somejoincolumn IS NULL

Answer 3

I think you may want to remove the subquery and put its columns into the main query e.g.

SELECT A.CLMSRNO, A.CLMPLANO, A.GENCURRCODE, A.CLMNETLOSSAMT, 
       A.CLMLOSSAMT, A.CLMCLAIMPRCLLOSSSHARE,  
       B.CLMSRNO, B.UWADVICETYPE, B.UWADVICENO, B.UWADVPREMCURRCODE, 
       B.GENSUBBUSICLASS, B.UWADVICENET, B.UWADVICEKIND, B.UWADVYEAR, 
       B.UWADVQTR, B.ISMANUAL, B.UWCLMNOREFNO     
  FROM PAKRE.CLMCLMENTRY A
       RIGHT OUTER JOIN PAKRE.UWADVICE B 
          ON A.CLMSRNO = B.CLMSRNO
 WHERE B.ISMANUAL = 1
 ORDER 
    BY A.CLMSRNO DESC;