Outer Join in 'Not Exists' 1

[HezMac]

Hello

I'm trying to run a query checks for existing rows in a 'queue' table (Batch_Reassign). If a row is already in the Batch_Reassign table, don't return it in subsequent queries.

Here's the code, when I run it without any records in the Batch_Reassign table, nothing is returned. When there is a dummy record in the Batch_reassign table, the 'AND NOT EXISTS' works.

I'm not sure why the statement doesn't work when there's nothing in the table, but works when theres a record. I think I need an outer join, but not sure how to set it up.

I'm using PL/SQL Developer. Thanks for any suggestions.

SELECT MAX (FolderRSN) AS FolderRSN, TRUNC(MAX(PaymentDate)) AS LastPmtDate
      FROM (SELECT F.FolderRSN, AP.PaymentDate
              FROM Folder F, AccountPayment AP, ValidStatus VS, ValidSub VSub, Batch_Reassign BR
             WHERE F.FolderType = 'ED'
               AND F.SubCode = VSub.SubCode
               AND F.SubCode = DECODE(-1, -1, VSub.SubCode, -1)
               AND F.StatusCode = VS.StatusCode
               AND F.StatusCode = DECODE(-1, -1, VS.StatusCode, -1)
               AND F.IssueUser = 'MANAGER'
               AND F_CALC_OUTSTANDING_BALANCE(F.FolderRSN) > -99999999
               AND F_CALC_OUTSTANDING_BALANCE(F.FolderRSN) < 99999999
               AND F.Indate > '1-JAN-1900'
               AND F.InDate < '1-JAN-3000'
               AND F.FolderRSN = AP.FolderRSN(+)
               AND AP.VoidFlag (+) != 'Y'
               AND AP.NSFFlag (+) != 'Y'
               AND NOT EXISTS 
                   (SELECT 'x' FROM Batch_Reassign BR
                     WHERE BR.FolderRSN = F.FolderRSN
                       AND BR.ReassignFlag = 'N'
                       AND BR.ProcessedFlag = 'N'))
    WHERE NVL(TRUNC(PaymentDate), '01-JAN-3000') >= '1-JAN-1900'
    GROUP BY FolderRSN;

 

[PHV]

Try to remove Batch_Reassign from the FROM clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HezMac]

That worked!!!
How'd you know that? I've been looking at this for hours...
Thank you so much.