Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: Please help with Oracle SQL Analyze & JOIN syntax

    Hi,


    I am currently benchmarking an application originally optimized for MSSQL. I use Oracle 9i.

    Simplest query:
    SELECT users.id, usergroup.id FROM users JOIN userGroup ON(users.id = userGroup.userRef)
    produces:
    Error occurred:

    VMQ-00006: Cannot validate SQL. Syntax error:

    XP-21064: Found 'USERGROUP'
    at or near line 1, column 47.
    The same query works just fine in SQL Plus.

    Please help, I cannot think of how to fix that.


    Thank you

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try

    SELECT users.id, usergroup.id
    FROM users,userGroup
    where users.id = userGroup.userRef;

    In a procedure you will need to select those values into variables ...
    similar to SQLServer except you turn the variable and column names
    around ie;

    SQLServer

    SELECT v_user_id = users.id,
    v_groupid = usergroup.id
    ...
    ...

    Oracle

    declare
    v_id number;
    v_groupid number;

    begin
    SELECT users.id, usergroup.id
    INTO v_id, v_groupid
    FROM users,userGroup
    where users.id = userGroup.userRef;

    ...
    ...
    ...

    end;
    /

    HTH
    Gregg

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    gbrabham

    Uhmm... Well, I need OUTER joins too... (+) syntax is not very good because the app sometimes uses multiple joins ( A LEFT OUTER JOIN B ON ... LEFT OUTER JOIN C ON ... ) etc.

    Besides, the whole app is written with JOINs, rewriting tons of DB access code just for Analyze doesn't sound good.

    Why does Analyze reject JOINs??? Not very comfortable to do optimizing in SQL Plus...

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    ex of outer join

    SELECT users.id, usergroup.id
    INTO v_id, v_groupid
    FROM users,userGroup
    where users.id = userGroup.userRef(+);


    HTH
    Gregg

Posting Permissions

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