"ORA-00905: missing keyword" when tuning the sql statement using join

From: <jrefactors_at_hotmail.com>
Date: 6 Oct 2005 11:10:30 -0700
Message-ID: <1128622230.576155.107130@g44g2000cwa.googlegroups.com>

The following query yields the correct result, but the performance is extremely poor. When I run the explain plan in TOAD, it costs 167560 and it performs the full table scan on (SELECT MAX ...) statement.

I try to tune the sql statement using join, but I got the error "ORA-00905: missing keyword" on clause "AND p.company_seq_no = c.company_seq_no".

I have no idea what's going on, please advise what is the best approach to tune the original SQL.

ORIGINAL SQL

---

SELECT p.finance_no, vda.finance_no, vrf_type

  FROM p_permit  p,
       p_company  c,
       p_vrf_details  vda
 WHERE p.permit_seq_no = '1001'
   AND p.company_seq_no = c.company_seq_no

   AND c.ref_id = vda.ref_id
   AND p.finance_no = vda.finance_no
   AND vda.vrf_type IN ('M','P')
   AND vda.activity_date = (SELECT MAX(activity_date)

                          FROM p_vrf_details  vdb
                          WHERE ref_id  = vda.ref_id
                           AND finance_no = vda.finance_no
       			   AND vrf_type = vda.vrf_type
			   AND vrf_level = vda.vrf_level
                           AND pstg_stmt_seq_no is not null
                          )

UPDATED SQL

---

SELECT p.finance_no, vda.finance_no, vrf_type

  FROM p_permit  p,
       p_company  c,
       p_vrf_details  vda

  JOIN p p_vrf_details vdb
  ON p.permit_seq_no = '1001'

   AND p.company_seq_no = c.company_seq_no
   AND c.ref_id = vda.ref_id
   AND p.finance_no = vda.finance_no
   AND vda.vrf_type IN ('M','P')
   AND vda.activity_date = MAX(vdb.activity_date)
   AND vda.finance_no = vdb.finance_no

   AND vdb.vrf_level = vda.vrf_level
   AND vdb.pstg_stmt_seq_no is not null    

Please advise. Thanks a lot!! Received on Thu Oct 06 2005 - 13:10:30 CDT