How to use WITH CLAUSE ...INSERT query in SAP HANA?

Bala picture Bala · Feb 27, 2017 · Viewed 8k times · Source

Here I used With AS Clause.if i use SELECT query it is working fine but if i use insert query . it gives syntax error. Can we use WITH ....INSERT in SAP HANA?

Code:

WITH t1 as
(
  Select 
  col1,
 col2,
 col3
  from table1),

t2 as
(
select 
a.col4,
a.col5,
a.col1,
b.col3
from table2 a 
left outer join t1
on a.col1 = b. col1)
insert into table3
select
c.col4,
c.col5,
c.col3
from t2;

Answer

Lars Br. picture Lars Br. · Mar 1, 2017

In addition to Serban's correct answer, a general workaround for lack of CTE functionality is to create views instead. In your case that could be:

create view t1 as
(select 
        col1,
        col2,
        col3
 from 
        table1);

create view t2 as
(select 
      a.col4,
      a.col5,
      a.col1,
      b.col3
 from 
      table2 a 
 left outer join t1
              on a.col1 = b. col1);

insert into table3
select
      c.col4,
      c.col5,
      c.col3
from t2;