sql-serverwith-clause

sql server with clause issue

sql below runs without problem in db2

with mytable(a,b) as  (
    values(
    (select current timestamp from sysibm.sysdummy1), (select current timestamp from sysibm.sysdummy1))
)
select * from mytable

I want to run something similar in sql server, when I give this 

with mytable(a,b) as (
values(
(select current_timestamp), (select current_timestamp))
)
select * from mytable

error below raises:

Error: Incorrect syntax near the keyword 'values'. SQLState: S1000 ErrorCode: 156 Error: Incorrect syntax near ','. SQLState: 42000 ErrorCode: 102 Error: Incorrect syntax near ')'. SQLState: 42000 ErrorCode: 102

any ideas ?

Solution

  • You can leave out values:

    with mytable(a, b) as (
      select current_timestamp, current_timestamp
     )
select *
from mytable;