db2 values - how to specify alias

grofaty · #1 (**permalink**) 07-06-09, 07:00

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

stolze · #2 (**permalink**) 07-06-09, 10:12

Code:

SELECT *
FROM TABLE ( VALUES (100, 100), (200, 200), (300, 300) ) AS t(c1, c2)

grofaty · #3 (**permalink**) 07-06-09, 12:59

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

stolze · #4 (**permalink**) 07-06-09, 16:40

What I posted is standard SQL. Whether it works on other DBMS depends on how closely the DBMSs adhere to the SQL standard.

grofaty · #5 (**permalink**) 07-07-09, 06:25

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

tonkuma · #6 (**permalink**) 07-07-09, 07:26

"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.

grofaty · #7 (**permalink**) 07-07-09, 07:57

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

grofaty · #8 (**permalink**) 08-05-09, 05:02

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