Results 1 to 9 of 9

Thread: Ora:00933

  1. #1
    Join Date
    Apr 2003
    Posts
    4

    Question Unanswered: Ora:00933

    I am trying to join a table and a result set and keep getting the ORA:00933 error. (SQL command not properly ended). What am I doing wrong?

    Example:

    SELECT Accounts.Row_ID,Accounts.Last_UPD FROM Library1.Accounts
    INNER JOIN (SELECT Accounts.Row_ID,max(Last_UPD)as LastDate FROM Library1.Accounts GROUP BY Accounts.Row_ID)as CurrentPop ON CurrentPop.Row_ID=Accounts.Row_ID and CurrentPop.LASTDTE=Accounts.Last_UPD

  2. #2
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi,
    Before Executing the SQL code, just try to give a command "SET DBMS_OUTPUT ON SIZE 10000" and then execute your code. It might be because it is truncating the code after 1024 bytes and that's why giving this error.

    Hope this helps to you & may solve.

    Regards,
    - KR

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Ora:00933

    Originally posted by schecke
    I am trying to join a table and a result set and keep getting the ORA:00933 error. (SQL command not properly ended). What am I doing wrong?

    Example:

    SELECT Accounts.Row_ID,Accounts.Last_UPD FROM Library1.Accounts
    INNER JOIN (SELECT Accounts.Row_ID,max(Last_UPD)as LastDate FROM Library1.Accounts GROUP BY Accounts.Row_ID)as CurrentPop ON CurrentPop.Row_ID=Accounts.Row_ID and CurrentPop.LASTDTE=Accounts.Last_UPD
    What version of Oracle are you on? The JOIN syntax wasn't supported by Oracle before 9i. If I run your query against an 8i database I get ORA-00933 also. I don't have access to a 9i database right now.

  4. #4
    Join Date
    Apr 2003
    Posts
    4

    Question Re: Ora:00933

    Originally posted by andrewst
    What version of Oracle are you on? The JOIN syntax wasn't supported by Oracle before 9i. If I run your query against an 8i database I get ORA-00933 also. I don't have access to a 9i database right now.
    Oracle 8.

    Would you know what syntax is supported? what i am trying to do is take a table that has several last updated values (a change history) and develop a report that only looks at the most current last updated date.

    So, I figure i could do a query that groups by the rowid and determines the max(last_update). I would then join that result set to my other query and thus only show the most current status of any given record.

    I anyone can make recommendations please jump in

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Schecke,

    Maybe this will help, I haven't applied it to your query or column names but it should give you the idea.

    select *
    from tablewithupdates alldata,
    (
    select primary_key_column,
    max( updatedate ) mdate
    from tablewithupdates
    group by primary_key_column
    ) lastupdates
    where alldata.primary_key_column = lastupdates.primary_key_column and
    alldata.updatedate = lastupdates.mdate

    HTH
    Bill

  6. #6
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Ora:00933

    Originally posted by schecke
    Oracle 8.

    Would you know what syntax is supported? what i am trying to do is take a table that has several last updated values (a change history) and develop a report that only looks at the most current last updated date.

    So, I figure i could do a query that groups by the rowid and determines the max(last_update). I would then join that result set to my other query and thus only show the most current status of any given record.

    I anyone can make recommendations please jump in
    Hi,

    Try this query, It is similar to what bill suggested.

    SELECT accounts.row_id,accounts.last_upd
    FROM library1.accounts
    WHERE accounts.last_upd IN
    (
    SELECT MAX(last_upd) lastdate
    FROM library1.accounts
    GROUP BY accounts.row_id
    )
    nn

  7. #7
    Join Date
    Apr 2003
    Posts
    4
    Originally posted by billm
    Hi Schecke,

    Maybe this will help, I haven't applied it to your query or column names but it should give you the idea.

    select *
    from tablewithupdates alldata,
    (
    select primary_key_column,
    max( updatedate ) mdate
    from tablewithupdates
    group by primary_key_column
    ) lastupdates
    where alldata.primary_key_column = lastupdates.primary_key_column and
    alldata.updatedate = lastupdates.mdate

    HTH
    Bill


    I have tried using this method and have gotten an error that says tells me that my result set is not being recognized as a table. I am wondering if the problem is that i am trying to do this in MS Query- Not a very robust tool that is probably catered toward SQL Server rather than Oracle... thanks for the advice. I agree that your SQL above should work...

  8. #8
    Join Date
    Apr 2003
    Posts
    4

    Re: Ora:00933

    Originally posted by NoviceNo1
    Hi,

    Try this query, It is similar to what bill suggested.

    SELECT accounts.row_id,accounts.last_upd
    FROM library1.accounts
    WHERE accounts.last_upd IN
    (
    SELECT MAX(last_upd) lastdate
    FROM library1.accounts
    GROUP BY accounts.row_id
    )
    I don't think a sub-query would work here. I would need to join on a result set like in Bill's query. Several accounts can have a common last update due to batch jobs etc. Also I didn't think that Group By's can be used in a subquery because they are limited to returning one column?

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Schecke,

    I don't know MS-Query so can't help much there. If your query is trying to be updated it probably would fail, you might try changing the very first line of the query to

    select alldata.*
    from tablewithupdates alldata,
    (

    This will then only return columns from the original table. You might also try removing the table alias from the main table. Another option may be to create an updateable view on the database and select/update on that.

    If that's still no good, maybe try returning the result set as a ref cursor from a procedure or function?

    I've had similar problems with other client side tools - often they query the Oracle dictionary for tables/columns and get completely screwed up by anything that doesn't relate directly to base table structures.

    HTH
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •