Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: db2 values - how to specify alias

    Hi,
    on db2 v9.5 linux I would like to write SQL with constant values:

    db2 "values (100,100), (200,200), (300,300)"

    The output is:
    Code:
    1           2
    ----------- -----------
            100         100
            200         200
            300         300
    Is there anyway I can specify alias of column names instead of "1" and "2"?
    Regards

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Code:
    SELECT *
    FROM TABLE ( VALUES (100, 100), (200, 200), (300, 300) ) AS t(c1, c2)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    stolze,
    thank a lot, it works fine...

    I was just very annoyed to write SQLs like:
    Code:
    select 100 as c1, 100 as c2 from sysibm.sysdummy1
    union all
    select 200 as c1, 200 as c2 from sysibm.sysdummy1
    union all
    select 300 as c1, 300 as c2 from sysibm.sysdummy1
    order by 1, 2
    the above SQL can get very long and confusing and it will only work on DB2. So I like Stolze's suggestion very much. Thanks.

    One more question: Is Stolze's solution compatible with SQL standard? Will this solution work on other databases beside DB2? I would like to get as standard SQL as possible, to reduce complexity with different databases (like Oracle).
    Regards

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What I posted is standard SQL. Whether it works on other DBMS depends on how closely the DBMSs adhere to the SQL standard.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Stolze, thanks for help.

    One more simple question: What is difference between bellow first and second SQL, second is without "t"? Both SQLs are working fine - why is "t" needed, what is its purpose?
    Code:
    SELECT *
    FROM TABLE ( VALUES (100, 100), (200, 200), (300, 300) ) AS t(c1, c2)
    and
    Code:
    SELECT *
    FROM TABLE ( VALUES (100, 100), (200, 200), (300, 300) ) AS (c1, c2)
    Regards

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    "t" in first query is correlation-name. It is used to qualify column names.
    "AS" is optional.
    So, "AS" in second query must be interpreted as correlation-name.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    tonkuma, thanks a lot now I understand. I can write SQLs (joins) like this:

    Code:
    SELECT
       t.c1,
       t.c2,
       a.c1
    FROM
       TABLE ( VALUES (100, 100), (200, 200), (300, 300) ) t (c1, c2)
    INNER JOIN
       admin.tab a
       on t.c1=a.c1
    admin table:
    Code:
    create table admin.tab (c1 int)
    insert into admin.tab values (100)
    insert into admin.tab values (400)
    Thanks a lot for clearing this.
    Regards

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by stolze
    What I posted is standard SQL. Whether it works on other DBMS depends on how closely the DBMSs adhere to the SQL standard.
    Hi,
    I have tried the following SQL, that is working fine on DB2:
    Code:
    SELECT TEMP.C1, TEMP.C2 FROM TABLE
     ( VALUES (100, 100), (200, 200), (300, 300) ) AS TEMP (C1, C2)
    but getting errors on Oracle and MySQL that "values" is not valid.

    Interesting, is this really a standard if nobody follows it.
    I have found web page that was updated yesterday with SQL-2008 standard and see there is no real support for such a command:
    http://troels.arvin.dk/db/rdbms/#other-dummy_table DB2 sample would be: SELECT TEMP.C1 FROM TABLE ( VALUES (1 + 1)) AS TEMP (C1)

    Regards
    Last edited by grofaty; 08-05-09 at 08:00.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In SQL-2003, this is in Subclause 7.3, "table value constructor"
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by grofaty
    Interesting, is this really a standard if nobody follows it.
    that's the nice thing about standards -- there are so many to choose from
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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