1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

select from values - problem

Discussion in 'SQL PL/SQL' started by Marco, Dec 4, 2012.

  1. Marco

    Marco Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi everybody,

    I have following sql query and it works fine on PostgreSQL.

    Code (SQL):
    SELECT ('user_'|| n.num) AS "col_name"
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))  AS n (num)
    The result is list of records named user_0, user_1, user_2 etc.
    I'm trying to convert it to Oracle version but I have huge problem with it, can anybody tell me how it should look ?



    Thanks for your help,
    Marco
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is an issue where it depends on which release of Oracle you're using; if you're using 10.2 or later you can do this:

    Code (SQL):
    SQL>  SELECT 'user_'||(rownum -1) AS col_name
      2  FROM dual
      3  CONNECT BY level <=10;

    COL_NAME
    ---------------------------------------------
    user_0
    user_1
    user_2
    user_3
    user_4
    user_5
    user_6
    user_7
    user_8
    user_9
     
    10 ROWS selected.
     
    SQL>
    If you're using an older release of Oracle you'll need to change that query slightly to use an actual table or view with more than one record:

    Code (SQL):
    SQL>  SELECT 'user_'||(rownum -1) AS col_name
      2  FROM all_objects
      3  WHERE rownum <=10;
     
    COL_NAME
    ---------------------------------------------
    user_0
    user_1
    user_2
    user_3
    user_4
    user_5
    user_6
    user_7
    user_8
    user_9
     
    10 ROWS selected.
     
    SQL>
    ROWNUM is a sequential number assigned to the records in a result set thus at least one record must successfully be returned to assign a ROWNUM value of 1. Since we're simply selecting all of the records from the ALL_OBJECTS view in the second query ROWNUM will, indeed, start with 1 and continue until there are no more records in the table or we meet the where condition specified.
     
    Marco likes this.