0

So I wrote a select statement with an outer join and I am having problems with some of the logic. First off here is the statement:

SELECT DISTINCT ah.ACCOUNT, lr.recall_status, lr.cancel_recall, lr.suit_atty, lb.note_sent, lb.current_atty, 
                  lr.file_date, ah.attorney_id, ah.transaction_date, rle.id_code, ah.rule_id, lr.processed, ah.transaction_code
  FROM legal_bankruptcy lb, legal_recall lr, legal_transaction_review ah, atlas.rlglenty rle
  WHERE ah.ACCOUNT = lb.ACCOUNT
  AND ah.ACCOUNT = lr.ACCOUNT(+)
  AND lb.current_atty = rle.id_code
  AND lr.file_date = (SELECT MAX(file_date) FROM legal_recall WHERE ACCOUNT = ah.ACCOUNT)
  AND ah.rule_id IN (1,2,114,191)
  AND ah.batch_id = p_batch_id

Now how this is supposed to work is, not all accounts are going to be in the legal_recall table especially if their accounts are not being recalled, but I still need to find out if a note was sent to the firm via legal_bankruptcy. I also know that the reason this select statement isnt returning any rows is because of this line:

AND lr.file_date = (SELECT MAX(file_date) FROM legal_recall WHERE ACCOUNT = ah.ACCOUNT)

When I comment that out, I get values returned. The problem I am running into now though is that when an account is in legal_recall I need to make sure I am getting the most recent filing. If I take that line out then I might get the wrong date which would then mess up my output.

I am asking if their is a way around this problem, or if I am stuck in the mud. Thanks in advance.

user2405778
  • 467
  • 6
  • 16
  • 29

2 Answers2

1

I think you can fix your problem by changing this line:

AND lr.file_date = (SELECT MAX(file_date) FROM legal_recall WHERE ACCOUNT = ah.ACCOUNT)

to

AND (lr.file_date is null or lr.file_date = (SELECT MAX(file_date) FROM legal_recall WHERE ACCOUNT = ah.ACCOUNT))

However, I would recommend that you rewrite the query using ANSI standard join syntax.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That seems to have done it, cant believe I didn't see something so simple. Thanks for the help, I will also take your recommendation into consideration. – user2405778 Sep 06 '13 at 16:12
0

Try this?

AND lr.file_date = (SELECT MAX(file_date) FROM legal_recall WHERE ACCOUNT = ah.ACCOUNT)(+)

EDIT:

Okay your sql was hard to read so I reformatted it a bit, (I've never had luck with the '(+)' syntax) but give this a go:

SELECT DISTINCT ah.ACCOUNT, 
   lr.recall_status, 
   lr.cancel_recall, 
   lr.suit_atty, 
   lb.note_sent, 
   lb.current_atty, 
   lr.file_date, 
   ah.attorney_id, 
   ah.transaction_date, 
   rle.id_code, 
   ah.rule_id, 
   lr.processed, 
   ah.transaction_code
FROM legal_bankruptcy lb 
   LEFT OUTER JOIN legal_recall lr
      ON lr.file_date = (SELECT MAX(file_date) 
                         FROM legal_recall 
                         WHERE ACCOUNT = ah.ACCOUNT)
   JOIN legal_transaction_review ah
      ON ah.rule_id IN (1,2,114,191)
     AND lb.ACCOUNT = ah.ACCOUNT
     AND p_batch_id = ah.batch_id
   JOIN atlas.rlglenty rle
     ON lb.current_atty = rle.id_code
CrckrJack
  • 93
  • 5