Oracle SQL - Accounts with payor pymts greater than 50 days... 3

[LevelThought]

I am interested in displaying all accounts that had the first payor payment greater than 50 days after the discharge date. How should the following sql script be tweaked to correctly display the desired results?
(Note, payor payments are captured in the account payment detail table only under the displayed transaction codes.)

Select apd.account_no, ap.account_id, pe.discharge_date,
apd.payment_amount, apd.payment_date
from account_payment_detail apd, account_payor ap, preview_encounter pe
where account_no in
(select account_no from account_payment_detail
group by account_no
having min(payment_date) - trunc(pe.discharge_date) > 50
and ep.acount_id in ('A34','A35')
and apd.transaction_code in ('7003','7008','7009','7010')
and apd.account_no = ap.account_no
and ap.account_no = pe.account_no

Thanks in advance.

 

[carp]

Try this:

SELECT apd.account_no, ap.account_id, pe.discharge_date, 
apd.payment_amount, apd.payment_date
FROM account_payment_detail apd, 
     account_payor ap,        
     preview_encounter pe
WHERE account_no IN 
 (SELECT account_no 
    FROM account_payment_detail
   GROUP BY account_no
   HAVING MIN(payment_date) - TRUNC(pe.discharge_date) > 50) 
AND ep.acount_id in ('A34','A35')
AND apd.transaction_code in ('7003','7008','7009','7010')
AND apd.account_no = ap.account_no
AND ap.account_no = pe.account_no;

Syntactically, the only problem I saw with your original query was a missing right parenthesis.
As a general rule, if you are receiving an error, it would be good to include the error message. If your results are not what you want, it would be good to include what is wrong with the results you're getting.

Please let us know if this solves your problem, or if you need more assistance.

 

[LevelThought]

Still have not gotten this to work to pull data from an Oracle 8.1.7 database! The error message received is ORA-00918; column ambiguously defined.

I believe that the problem is with the part of the SQL script that contains "trunc" or "min."

What modifications to the SQL script below should be made so that I will receive all accounts that had a payor payment greater than 50 days after the discharge date?


Thanks in advance.


Select apd.account_no, ap.account_id, pe.discharge_date,
apd.payment_amount, apd.payment_date
from account_payment_detail apd, account_payor ap, preview_encounter pe
where account_no in
(select account_no from account_payment_detail
group by account_no
having min(trunc(apd.payment_date)) - trunc(pe.discharge_date) > 50)
and ep.acount_id in ('A34','A35')
and apd.transaction_code in ('7003','7008','7009','7010')
and apd.account_no = ap.account_no
and ap.account_no = pe.account_no

 

[SantaMufasa]

Level,

If there is a column name, which your query references explicitly, and that column name appears in multiple tables that your query accesses, then you must qualify each reference to such (ambiguous) columns by using a table alias (either your explicit table alias if one exists or the table name itself if an explicit alias does not exist) for the table you choose for the query to use, for each reference.

In your case, the column reference with which Oracle is taking issue is "Account_no" (in the WHERE clause).

Therefore, make the following slight adjustment to your code, re-try the code, then let us know if it worked for you:

Select apd.account_no, ap.account_id, pe.discharge_date, 
apd.payment_amount, apd.payment_date
from account_payment_detail apd, account_payor ap, preview_encounter pe
where [b]ap.[/b]account_no in 
 (select account_no from account_payment_detail
  group by account_no
  having min(trunc(apd.payment_date)) - trunc(pe.discharge_date) > 50) 
and ep.acount_id in ('A34','A35')
and apd.transaction_code in ('7003','7008','7009','7010')
and apd.account_no = ap.account_no
and ap.account_no = pe.account_no;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LevelThought]

Still no success! Using the following SQL script, the error message is that I am missing a right parenthesis!

Any idea as to the location that this parenthesis should be inserted?

Select apd.account_no, ap.account_id, pe.discharge_date,
apd.payment_amount, apd.payment_date
from account_payment_detail apd, account_payor ap, preview_encounter pe
where ap.account_no in
(select account_no from account_payment_detail
group by account_no, PymtLag
having min(trunc(apd.payment_date)) - trunc(pe.discharge_date) > 50 as PymtLag)
and ep.acount_id in ('A34','A35')
and apd.transaction_code in ('7003','7008','7009','7010')
and apd.account_no = ap.account_no
and ap.account_no = pe.account_no;

 

[hoinz]

Any idea as to the location that this parenthesis should be inserted?
No, sorry.

But may I suggest you leave away that as PymtLag in this line:
having min(trunc(apd.payment_date)) - trunc(pe.discharge_date) > 50 as PymtLag)

hope this helps

 

[SantaMufasa]

Level,

Hoinz is correct: "as PymtLag" is syntactically disallowed in Oracle... "as <alias" is syntactically available only on expressions appearing in the "SELECT...<expression-list>"...And, I presume, this is causing your current syntax exception.

What was your result of running the code that I posted (before you added in the "asPymtLag" alias)?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

Select 
  apd.account_no, 
  ap.account_id, 
  pe.discharge_date, 
  apd.payment_amount, 
  apd.payment_date
from 
  account_payment_detail apd, 
  account_payor ap, preview_encounter pe
where ap.account_no in (
        select account_no 
        from account_payment_detail
        group by account_no, PymtLag
        having min(trunc(apd.payment_date)) - trunc(pe.discharge_date) > 50) 
  and ep.acount_id in ('A34','A35')
  and apd.transaction_code in ('7003','7008','7009','7010')
  and apd.account_no = ap.account_no
  and ap.account_no = pe.account_no;

I checked this query with a parenthesis matching editor. It gave the expression thumbs up, so parenthesis is definately not your issue. I would agree that it is probably the as PymtLag.