Need additional condition on getting data from join

Question

I need to also bring in another data from referrf and not sure how to do this. I need all rows as well which are from astdta.referrf where RFCAT = '8799' and RFSQ2 = '1' then give us the column RFSLC - if I add this i get no results> AND T02.rfcat = '8799'

CREATE VIEW rklib.CLSPAYTPP AS
    SELECT ALL 
         T01.OTCOM#, T01.OTORD#,                                                                 
         t01.ottRND, T01.OTTRT,                                             
         T01.OTUSRN, T01.OTTRNC, T01.OHPTTC, T01.OHSLR#, 
         t01.OHORDT,                                                           
         T01.OHORDD, T01.OHTTN$, T02.RFCAT, T02.RFSLC, T02.RFSQ2,                                                                     
         t02.RFDTA                                                                  
    FROM ASTDTA.CLSPAYT2 T01 INNER JOIN
         ASTDTA.REFERRF T02
         ON T01.OTTRNC = T02.RFSLC WHERE RFCAT = '5058' AND RFSQ2 = '1'  

Answer 1

I think you could us the IN clause:

CREATE VIEW rklib.CLSPAYTPP AS
    SELECT ALL 
         T01.OTCOM#, T01.OTORD#,                                                                 
         t01.ottRND, T01.OTTRT,                                             
         T01.OTUSRN, T01.OTTRNC, T01.OHPTTC, T01.OHSLR#, 
         t01.OHORDT,                                                           
         T01.OHORDD, T01.OHTTN$, T02.RFCAT, T02.RFSLC, T02.RFSQ2,                                                                     
         t02.RFDTA                                                                  
    FROM ASTDTA.CLSPAYT2 T01 INNER JOIN
         ASTDTA.REFERRF T02
         ON T01.OTTRNC = T02.RFSLC WHERE RFCAT IN ('5058','8799') AND RFSQ2 = '1'