f



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

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!!

0
jrefactors
10/6/2005 6:10:30 PM
comp.databases.oracle.misc 8436 articles. 1 followers. Post Follow

2 Replies
323 Views

Similar Articles

[PageSpeed] 54

Try:

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

May also want to create an index on activity_date.

HTH

Jerry

<jrefactors@hotmail.com> wrote in message 
news: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!!
> 


0
Jerry
10/6/2005 6:16:21 PM
jrefactors@hotmail.com schrieb:
> 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!!
> 

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
0
Maxim
10/6/2005 7:29:45 PM
Reply:

Similar Artilces:

When to use "." and when to use "!"
I'm still confused about this and I can't find anywhere that explains it properly. I have the MS book "Access 2003" in front of me and I'm reading Part 5 about VB and so on. It's telling me about how to refer to a specific database and has the example:- Dim dbMyDb As DAO.Database Set dbMyDb = DBEngine.Workspaces(0).Databases(0) but, but, but, but - what do those dots (periods, full stops, call them what you will) mean? (OK, it appears to be the same usage as C/C++/Java when referring to class/structure members, but I wish it would tell me that somewhere) ...

Usage of the database "Oracle" vs. company "Oracle"
Hi, I am writing a document and normally I wouldn't give a second thought to saying such and such will run using a database such as Oracle. It occurred to me that Oracle is the name of the company, and that an Oracle database doesn't differentiate between what you and I think of daily as Oracle (eg. 9i, 10g, or "launched" but not "released" 11g), and Oracle Times Ten or Oracle Database Lite or Oracle Berkeley DB or some other database Oracle provides that I have forgotton about. Does just shorthand "Oracle" still refer to say 10g. I don't want to write Oracle 10g because then I am specifying a version which opens a can of worms elsewhere. Ideas anyone. Thank you Barry Barry Bulsara wrote: > Hi, I am writing a document and normally I wouldn't give a second > thought to saying such and such will run using a database such as > Oracle. > > It occurred to me that Oracle is the name of the company, and that an > Oracle database doesn't differentiate between what you and I think of > daily as Oracle (eg. 9i, 10g, or "launched" but not "released" 11g), > and Oracle Times Ten or Oracle Database Lite or Oracle Berkeley DB or > some other database Oracle provides that I have forgotton about. > > Does just shorthand "Oracle" still refer to say 10g. I don't want to > write Oracle 10g because then I am specifying a version which op...

""""""""""""""""""""""ADD ME""""""""""""""""""""
Hi , Hope you are doing great. Please let me take this opportunity to introduce myself, Iam Karthik working with BhanInfo Inc, a NY based company. We have consultants on our bench on various technologies, my request is to add me to your distribution list and kindly do send me the requirements. i have the below list available 1. Mainframe 2. Java 3.. Financial Analyst 4. Data Architect If there is any vendor ship agreement which has to be signed then I would like to take an opportunity to represent my company and expect your cooperation... We look forward to build a ve...

When to use "document" and when to use "this"
Hello all, Can anyone explain when one should use the "document" object and when one should use the "this" object? Also, is the "self" object the same as the "document" or "this" object? ...

Choosing "a SQL" or "an SQL"
When I say SQL outloud, I vary how I pronounce it depending on the audience and my mood. I think that SQL Server and Oracle folks say "sequel" while MySQL folks and those using the term related to industry standards or separate from any particular tool are more apt to pronounce it "es-q-el." Is this accurate? How do DB2 folks say it? What other groups pronounce it which way? I'm starting to write and am trying to figure out whether to write "a SQL statement" or "an SQL statement." I get 1.96 million google hits on an-sql and 2.3 milli...

"""""""""ADD ME""""""""""
Hi , Hope you are doing great. Please let me take this opportunity to introduce myself, Iam Karthik working with BhanInfoi Inc, a NY based company. We have consultants on our bench on various technologies, my request is to add me to your distribution list and kindly do send me the requirements. i have the below list available 1. Mainframe 2. Java 3.. Financial Analyst 4. Data Architect If there is any vendor ship agreement which has to be signed then I would like to take an opportunity to represent my company and expect your cooperation... ...

when to use "conv2", when to use "fft"->"multiplication"->"ifft"?
Hi all, I am doing 2D image filtering with sometimes filter size larger than image size. I am wondering which of the following scheme is the fastest, using Matlab: 1) conv2, 2D convolution directly; 2) conv2, but decompose the filter into two separable filters and then conv2 will first convolve along X axis and then convolve along Y axis; 3) fft2, multiplication, ifft2, all in 2D domain; 4) fft, multiplication, ifft, all in 1D case and work on convolutions of separable filters along X axis and Y axis; (similar to 2, but do 1D convolution all in frequency domain). Images and filters are...

Use of "conditional" vs "imperative" statements
As a sort-of follow-up to both the END-PERFORM and EVALUATE threads, I just thought that I would mention exactly HOW limited the use of "conditional" statements are in Standard-conforming (ANSI/ISO - any year) COBOL source code is: A conditional statement may be used: 1) at the end of a series of statements within a sentence. (It may be preceded by 0-n imperative statements, but MUST be followed by a period/full-stop). 2) Within either the IF or ELSE phrases of an IF statement. 3) (sort-of) When actually considered as an imperative statement because it is terminated by its ...

converting from "cooked" to "raw" oracle database
i'm working on a database which consists of datafiles in filesystems (referred to as cooked in some technical documents). i want to look at the performance difference with all the files raw i.e. using logical volumes to store the data. i know its possible to backup raw files using the dd command. what i'm interested in finding out/understanding is how to perform the opposite process i.e. convert from a 'cooked' to 'raw' storage system for an existing instance containing data. can i use a dd command to copy data from file to logical volume? is an export/import the only o...

Urgent Requirement in """""""""""""NEW YORK""""""""""""""""
Hello Partners, Please find the requirement below. Please send the updated resume along with rate and contact no. REQ#1: Title : Java Developer ( Rating Project) Duration : 6 months Rate : open Location : NY strong java, WebLogic 9.2, Web Services, Oracle REQ#2: Title : Java Developer Duration : 4 months Rate : open Location : NY Strong java, SQL REQ#3: Title : VB.Net Consultant Location : NY Duration : 4 months Rate : open Primarily looking at someone who has Excel, VB.net a...

Is there a "break" "continue" equivalent in the "for" or "while" statement in SKILL?
Thank you! madhero wrote, on 07/03/08 09:42: > Thank you! Not directly, but see: http://tinyurl.com/69wte8 http://tinyurl.com/6ycdbn for two related threads in this group. Regards, Andrew. ...

we have a "this" keyword, but what if... we also had "component" keyword??
All the talk of singletons, contexts, instances etc... gave me an idea, its more of a wish list item as would be a compiler feature but thought I'd post it out there and maybe someone with the right influence might pick up on it. In OO langages we have the this or Me or whatever pointer that is implicitly pushed on the stack when ever any instance method is invoked. We can easily mimic this behavior in functional languages by using function pointers and passing a pointer to a structure with all the data as a parameter to every "object" method. The nice thing is OO languages do...

"out" and "in out"
Hi i found the following explaination: In Ada, "in" parameters are similar to C++ const parameters. They are effectively read-only within the scope of the called subprogram. Ada "in out" parameters have a reliable initial value (that passed in from the calling subprogram) and may be modified within the scope of the called procedure. Ada "out" parameters have no reliable initial value, but are expected to be assigned a value within the called procedure. What does "have no reliable initial value" mean when considering the "out" parameter? By c...

"/a" is not "/a" ?
Hi everybody, while testing a module today I stumbled on something that I can work around but I don't quite understand. >>> a = "a" >>> b = "a" >>> a == b True >>> a is b True >>> c = "/a" >>> d = "/a" >>> c == d True # all good so far >>> c is d False # eeeeek! Why c and d point to two different objects with an identical string content rather than the same object? Manu Emanuele D'Arrigo wrote: >>>> c = "/a" >>>&...

When to use "document" and when to use "this" #2
Hello all, Can anyone explain when one should use the "document" object and when one should use the "this" object? Also, is the "self" object the same as the "document" or "this" object? Bryan wrote on 23 sep 2006 in comp.lang.javascript: > Can anyone explain when one should use the "document" object and when > one should use the "this" object? IMHO: "window" is the default and top element of the DOM tree. So: document defaults to window.document. [However parent.document could mean the framse page.] &q...

"If then; if then;" and "If then; if;"
I have a raw data set which is a hierarchical file: H 321 s. main st P Mary E 21 F P william m 23 M P Susan K 3 F H 324 S. Main St I use the folowing code to read the data to creat one observation per detail(P) record including hearder record(H): data test; infile 'C:\Documents and Settings\retain.txt'; retain Address; input type $1. @; if type='H' then input @3 Address $12.; if type='P' then input @3 Name $10. @13 Age 3. @16 Gender $1.; run; but the output is not what I want: 1 321 s. main H 2 321 s. main P Mary E 21 F 3 321 s...

"or" and "and"
Hi, I'm just getting to discover ruby, but I find it very nice programming language. I just still don't understand how the "or" and "and" in ruby... I was playing with ruby and for example made a def to print Stem and Leaf plot (for those who didn't have a statistics course or slept on it, e.g. http://cnx.org/content/m10157/latest/) Here is the Beta version of it: class Array def n ; self.size ; end def stem_and_leaf(st = 1) # if st != (2 or 5 or 10) then ; st = 1 ; end k = Hash.new(0) self.each {|x| k[x.to_f] += 1 } k = k.sort{|a, b| a[0].to_f <=&g...

about "++" and "--"
why this program snippet display "8,7,7,8,-7,-8" the program is: main() { int i=8; printf("%d\n%d\n%d\n%d\n%d\n%d\n",++i,--i,i++,i--,-i++,-i--); } > why this program snippet display "8,7,7,8,-7,-8" Ask your compiler-vendor because this result is IMHO implementation-defined. Check this out: http://www.parashift.com/c++-faq-lite/misc-technical-issues.html#faq-39.15 http://www.parashift.com/c++-faq-lite/misc-technical-issues.html#faq-39.16 Regards, Irina Marudina fxc123@gmail.com wrote: > why this program snippet display "8,7,7,8,-7,-8&q...

why "::", not "."
Why does the method of modules use a dot, and the constants a double colon? e.g. Math::PI and Math.cos -- Posted via http://www.ruby-forum.com/. On Oct 26, 2010, at 01:48 , Oleg Igor wrote: > Why does the method of modules use a dot, and the constants a double > colon? > e.g. > Math::PI and Math.cos For the same reason why inner-classes/modules use double colon, because = they're constants and that's how you look up via constant namespace. Math::PI and ActiveRecord::Base are the same type of lookup... it is = just that Base is a module and PI is a float....

"my" and "our"
Hi, while testing a program, I erroneously declared the same variable twice within a block, the first time with "my", the second time with "our": { my $fz = 'VTX_Link'; .... ( around 200 lines of code, all in the same block) our $fz = 'VTX_Linkset'; ... } So the initial contents of the $fz declared with "my" is lost, because "our" creates a lexical alias for the global $fz, thus overwriting the previous "my" declaration. It was my error, no question. But I wonder why Perl doesn't mention this - even with "use s...

Cannot use keyword "use"
Hello, I am getting a runtime error when I use the word use. I have use CGI; in my code and I am getting an error failure (19064): for host dodnic-dc.nic.mil trying to GET asn.cgi, cgi_scan_headers reports: HTTP4044: the CGI program asn.cgi did not produce a valid header (name without value: got line "begin failed--compilation aborted at asn.cgi line 1461.") Could you please let me know if there is a substitute for use or the following code: use CGI; $q = new CGI; Any help is appreciated. Balu -- PLEASE NOTE: comp.infosystems.www.authoring.cgi is a SELF-...

Urgent Requirement for """""""""""""""INFORMATICA DEVELOPER"""""""""""""
Hello Partners, How are you ? Please find the requirements below. Title: Database/ETL Developer Duration: 6 months Location: NY Exp: 7+ Locals preferred Database/ETL requirements (Mandatory) Candidate must have worked with financial instruments, preferably Mutual Funds but, Equities are also ok. PL/SQL - packages, Stored procs, Functions, Aggregate functions, Pipelined Functions Informatica 8.6 - especially complex mappings, complex maplets, complex workflows, transformations Oracle 10g/11g Unix/Linux shell scripting ...

How widely used are the JavaScript "import" and "export" statements?
I was reviewing an introductory JavaScript manuscript today, and I noticed a reference to the "import" and "export" statements, which I had never even noticed before. I see that they are not supported in IE, and not in ECMAScript, either. Has anyone ever used these statements for anything useful? David Karr wrote: > I was reviewing an introductory JavaScript manuscript today, and I > noticed a reference to the "import" and "export" statements, which I > had never even noticed before. I see that they are not supported in > IE, and not in E...

Urgent need """""""""""INFORMATICA DEVELOPER"""""""""""""
Hello Partners, How are you ? Please find the requirements below. Title: Database/ETL Developer Duration: 6 months Location: NY Exp: 7+ Locals preferred Database/ETL requirements (Mandatory) Candidate must have worked with financial instruments, preferably Mutual Funds but, Equities are also ok. PL/SQL - packages, Stored procs, Functions, Aggregate functions, Pipelined Functions Informatica 8.6 - especially complex mappings, complex maplets, complex workflows, transformations Oracle 10g/11g Unix/Linux shell scripting Database/ETL requirements (Optional) Data warehousing experience Threading and job concepts in 10g/11g Cost based Optimizer concepts in 10g/11g Must : Experience with XML files and partitioning concepts in Oracle, Collections, Material Views Note : No phone calls please. : send Resumes to karthik@bhaninfo.com Thanks & Regards Karthik BhanInfo karthik@bhaninfo.com ...

Web resources about - "ORA-00905: missing keyword" when tuning the sql statement using join - comp.databases.oracle.misc

Resources last updated: 2/20/2016 9:07:16 AM