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

Try:

WHERE p.company_seq_no = c.company_seq_no AND... May also want to create an index on activity_date.

HTH Jerry

<jrefactors_at_hotmail.com> wrote in message news:1128622230.576155.107130_at_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:16:21 CDT