Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2004
    Posts
    26

    Unanswered: Equivalent Oracle query for the given Sybase query.

    i use the following query in Sybase and i got stuck up when migrating the following query to Oracle.

    Sybase query:
    ===========

    SELECT *
    FROM tab1e1, table2
    WHERE table1.col1 =* ( SELECT max(table2.sno) FROM table2 )

    What is the above query's equivalent in Oracle?.
    Last edited by gopidba; 03-08-05 at 11:13.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sorry, I don't know much (actually, anything) about Sybase ... What is =* ? And what do you need? All columns from both tables, or just from table1? If we disregard *, you could try

    SELECT *
    FROM table1 t1, table2 t2
    WHERE t1.col1 = (SELECT MAX(t22.sno) FROM table2 t22);

    or, perhaps

    SELECT *
    FROM table1 t1
    WHERE t1.col1 = (SELECT MAX(t2.sno) FROM table2);

    Does any of these queries work?

  3. #3
    Join Date
    Nov 2004
    Posts
    26
    Hi,

    "=*" means it is a RIGHT JOIN query. i want to have a equivalent to the above query in THETA JOIN or ANSI JOIN.

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    outer join try this:

    SELECT *
    FROM tab1e1, table2
    WHERE table1.col1 =+ ( SELECT max(table2.sno) FROM table2 );

    depending on your version you may try right outer join as well

  5. #5
    Join Date
    Nov 2004
    Posts
    26
    Quote Originally Posted by osy45
    outer join try this:

    SELECT *
    FROM tab1e1, table2
    WHERE table1.col1 =+ ( SELECT max(table2.sno) FROM table2 );

    depending on your version you may try right outer join as well

    Do you mean to say replace "=*" by "=+"?.

    "=(+)" is not a valid syntax.

    Usage of such syntax gives an error saying "Subqueries not allowed in the where column".

  6. #6
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    An outer join in Oracle looks like this:

    SELECT *
    FROM tab1e1, table2
    WHERE table1.col1 =( SELECT max(table2.sno) FROM table2 ) (+);
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  7. #7
    Join Date
    Nov 2004
    Posts
    26
    Quote Originally Posted by Ruudboy
    An outer join in Oracle looks like this:

    SELECT *
    FROM tab1e1, table2
    WHERE table1.col1 =( SELECT max(table2.sno) FROM table2 ) (+);

    Exactly the same query i tried. BUt Oracle(Both 8 & 9) doesn't accept sub queries with the JOIN condition.

  8. #8
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Then put the subquery in the FROM clause instead of the where clause.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  9. #9
    Join Date
    Nov 2004
    Posts
    26
    Quote Originally Posted by Ruudboy
    Then put the subquery in the FROM clause instead of the where clause.

    If i include the SUB QUERY in the FROM column, then the resultset will also include what is given in the FROM clause(Here the sub query).

    i.e, if i do give the query as,
    SELECT * FROM test, test1, (SELECT max(test.col1)) WHERE....

    then,

    The resultset will have all value of the tables, also the max value of the test.col1 as an additional column.

    How that can be avoided?.


    -gopidba

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by gopidba
    If i include the SUB QUERY in the FROM column, then the resultset will also include what is given in the FROM clause(Here the sub query).

    i.e, if i do give the query as,
    SELECT * FROM test, test1, (SELECT max(test.col1)) WHERE....

    then,

    The resultset will have all value of the tables, also the max value of the test.col1 as an additional column.

    How that can be avoided?.


    -gopidba
    You would also have a cartesian join. Maybe that is what you are trying to do, but you should have a join condition between test and test1
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Nov 2004
    Posts
    26

    Exclamation

    Actual query in which i am getting the subquery results also in the ResultSet was,

    SELECT * FROM test, test1, (SELECT col1 as FROM test1) alias1 WHERE test.col1 = alias1.col1(+);

    ResultSet is like...

    |================================================= |
    | test.col1 | test.col2 | test1.col1 | test1.col2 | col1 |
    |================================================= |


    This last column is an extra addition if i bring the sub query to the FROM clause.

    So what can be done to avoid the extra column in the result OR is there any other way to use a SUB QUERY in the WHERE column JOINs?.


    -gopidba

  12. #12
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    This last column is an extra addition if i bring the sub query to the FROM clause.
    This would be avoided if you did not use "SELECT * FROM..." but "SELECT test.col1, test.col2, test1.col1, test1.col2 FROM...". SELECT * is a bad habit .

    Besides, I agree with beilstwh, I think you need a join on test and test1. And I don't quite understand what you want to do with an outer join on a single record (the max)...

    Would you care to explain in english what result you exactly want to get ? It would be much easier for us to help you I think .

    Regards,

    RBARAER

  13. #13
    Join Date
    Nov 2004
    Posts
    26
    Hi RBARAER,

    See my first post in this thread, where i have explained what i need.

    In simple, i have a sybase query... i need an oracle equivalent for it.

    Sybase Query is,

    SELECT *
    FROM tab1e1, table2
    WHERE table1.col1 =* ( SELECT max(table2.sno) FROM table2 )

    -gopidba

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Godipba, you'd better explain what your Sybase query does exactly - with a simple example. Outer joining the result of the subquery to the main query doesn't achieve anything, even if it were legal Oracle syntax, since an outer join by definition doesn't restrict rows from the other table(s). It seems to be saying something like:
    Code:
    select * from table1, table2
    where (  table1.col1 = ( SELECT max(table2.sno) FROM table2 )
          or table1.col1 != ( SELECT max(table2.sno) FROM table2 )
          or table1.col1 is null
          );
    ... which is equivalent to:
    Code:
    select * from table1, table2;

Posting Permissions

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