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

[jrefa...@hotmail.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!!

[Jerry Spivey]

Try:

WHERE p.company_seq_no = c.company_seq_no
AND...

May also want to create an index on activity_date.

HTH

Jerry

<jrefa...@hotmail.com> wrote in message
news:1128622230.5...@g44g2000cwa.googlegroups.com...

[Maxim Demenko]

jrefa...@hotmail.com schrieb:

You have to look up on the JOIN syntax in the Oracle documentation, your
updated SQL is wrong on many places i.e.
1) you have to join by tableA JOIN tableB ON (...) JOIN tableC ON (...)
JOIN tableD ON (...)
2)in the line JOIN *p* p_vrf_details vdb the *p* is most likely a typo
3)in the line AND vda.activity_date = MAX(vdb.activity_date) you are not
allowed to use aggregate function.

You would more likely become help regarding the performance if you post
the ddl of your tables as well as explain plan of your original sql. You
should also mention whether the statistics are collected and are actual
Last but not least, the Oracle and OS version should be mentioned as well.

Just guessing - you are on at least 9i ( because trying to use ANSI
syntax ) , and , maybe it is a bad guess because i don't know your
explain plan ,table structure and statistics, you could however try the
following:

WITH t AS (SELECT MAX(activity_date) activity_date,
ref_id,
finance_no,
vrf_type,
vrf_level
FROM p_vrf_details
WHERE pstg_stmt_seq_no IS NOT NULL
GROUP BY ref_id, finance_no, vrf_type, vrf_level)
SELECT p.finance_no, vda.finance_no, vda.vrf_type

FROM p_permit p,
p_company c,

p_vrf_details vda,
t

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=t.activity_date
AND vda.ref_id = t.ref_id
AND vda.finance_no = t.finance_no
AND vda.vrf_type = t.vrf_type
AND vda.vrf_level = t.vrf_level
;

Best regards

Maxim