Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    6

    Exclamation Unanswered: SQLSRV to ORA CONVERSION QUESTION

    I am migrating from sql server 2000 to oracle 9i, thought this would be an easy conversion, but cant get it to work, keep on getting "invalid identifier", PLEASE HELP! Thanks.

    SELECT DISTINCT t1.TABLEA FROM ,

    (SELECT TOP 1 TABLEA.cola
    FROM TABLEA
    WHERE TABLEA.thisGUID = t1.thisGUID
    ORDER BY TABLEA.cola DESC) COL2,

    (SELECT TOP 1 TABLEA.colb
    FROM TABLEA
    WHERE TABLEA.thisGUID = t1.thisGUID
    ORDER BY TABLEA.colb DESC) COL3

    FROM TABLEA t1

    This is the working syntax in SQL Server, i need the ORA. Thank you soooo much!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > SELECT TOP 1 TABLEA.colb
    Not valid for Oracle
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2005
    Posts
    6

    Exclamation

    Understand, i converted the top 1 syntax to rownum < 2, but figured i would show sql server syntax. below is the oracle syntax i wrote, ANY ADVICE??? . IE:

    SELECT DISTINCT t1.TABLEA FROM ,

    (SELECT TABLEA.cola
    FROM TABLEA
    WHERE TABLEA.thisGUID = t1.thisGUID
    and ROWNUM < 2
    ORDER BY TABLEA.cola DESC) COL2,

    (SELECT TABLEA.colb
    FROM TABLEA
    WHERE TABLEA.thisGUID = t1.thisGUID
    and ROWNUM < 2
    ORDER BY TABLEA.colb DESC) COL3

    FROM TABLEA t1

  4. #4
    Join Date
    Sep 2005
    Posts
    6

    Exclamation

    Sorry, this is the syntax:

    SELECT DISTINCT t1.TABLEA ,

    (SELECT TABLEA.cola
    FROM TABLEA
    WHERE TABLEA.thisGUID = t1.thisGUID
    and ROWNUM < 2
    ORDER BY TABLEA.cola DESC) COL2,

    (SELECT TABLEA.colb
    FROM TABLEA
    WHERE TABLEA.thisGUID = t1.thisGUID
    and ROWNUM < 2
    ORDER BY TABLEA.colb DESC) COL3

    FROM TABLEA t1

  5. #5
    Join Date
    Mar 2004
    Posts
    370
    Did this syntax work? I doubt! If your first select (alias COL2) and also second select (alias COL3) are inline views, therefor you dont need any order by there.However it is better to provide your wanted result in English (may be with some meaningful example) so we can help you better.
    -Best wishes

  6. #6
    Join Date
    Jun 2005
    Posts
    85
    Try this Oracle equivalent :

    SELECT DISTINCT
    t1.TABLEA,
    (SELECT cola FROM
    (SELECT TABLEA.cola FROM TABLEA WHERE TABLEA.thisGUID =
    t1.thisGUID ORDER BY TABLEA.cola DESC )
    WHERE ROWNUM < 2) COL2,
    (SELECT colb FROM
    (SELECT TABLEA.colb FROM TABLEA WHERE TABLEA.thisGUID =
    t1.thisGUID ORDER BY TABLEA.colb DESC ) WHERE ROWNUM < 2)
    COL3
    FROM TABLEA t1

    I got this equivalent from the below automated conversion tool for SQL Queries :

    http://www.swissql.com/products/sqlo...nsolejava.html

    Thanks,
    MiraJ

Posting Permissions

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