CODEWITHSUNDEEP
sqlunionaliasderby
Fidel
Fidel

Reputation: 7397

Give column name to values rows

In Apache Derby, I'm generating some values using this statement:

select * from (
    select 10 as DISTANCE_KM from SYSIBM.SYSDUMMY1 union
    select 15 as DISTANCE_KM from SYSIBM.SYSDUMMY1 union
    select 18 as DISTANCE_KM from SYSIBM.SYSDUMMY1 union
    select 21 as DISTANCE_KM from SYSIBM.SYSDUMMY1 union
    select 25
) as DISTANCE

Which returns:

|DISTANCE_KM|
|-----------|
|10         |
|15         |
|18         |
|21         |
|25         |

There is a more concise way to generate similar results:

select * from (values 10, 15, 18, 21, 25) as DISTANCE

which returns:

|1          |  <---- I'd like this column to be called DISTANCE_KM
|-----------|
|10         |
|15         |
|18         |
|21         |
|25         |

Is there a way to alias the returned column as DISTANCE_KM? This is what I've tried, but it didn't work:

select "1" as DISTANCE_KM from (values 10, 15, 18, 21, 25) as DISTANCE

p.s. I'm not permitted to change the schema - so I can't store the values in their own table.

Upvotes: 2

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I don't have db2 on hand, but this might work:

select *
from (values 10, 15, 18, 21, 25) as DISTANCE(DISTANCE_KM);

If not, then this will work:

with distance(DISTANCE_KM) as (
      select *
      from (values 10, 15, 18, 21, 25) as DISTANCE
     )
. . .

Upvotes: 1

Related Questions