Use WITH clause for storing temporary data: What is the equivalent syntax to Oracle, but in SQLite?-CodePudding

The syntax for storing temporary data in the WITH clause looks like this in Oracle:

with data (asset_id, x, y) as (
select 100, 10, 20 from dual union all
select 200, 30, 40 from dual union all
select 300, 50, 50 from dual)
select * from data

  ASSET_ID          X          Y
---------- ---------- ----------
       100         10         20
       200         30         40
       300         50         50

What would be the equivalent syntax in SQLite?

CodePudding user response：

It looks like the syntax is the same as Oracle, except we need to remove from dual:

with data (asset_id, x, y) as (
select 100, 10, 20 union all
select 200, 30, 40 union all
select 300, 50, 50 )
select * from data

  ASSET_ID          X          Y
---------- ---------- ----------
       100         10         20
       200         30         40
       300         50         50

db<>fiddle

CodePudding user response：

In SQLite it is simpler to use VALUES:

with data (asset_id, x, y) as (
  VALUES
  (100, 10, 20),
  (200, 30, 40),
  (300, 50, 50)
)  
select * from data

See the demo.