CTE in Datasphere

[abibello]

Hi,
I have tried to use With Statement in Datasphere with success.

Is it even possible to use CTE in Datasphere?
Has anyone tried it?
What alternative or workaround would you recommend?

[irodin10]

Hi Soburat,

a) If you are using SQLScript then you should define a local table variable instead of using a CTE, for example this would be valid
lt_table = SELECT '1' as "TEST" FROM DUMMY;
SELECT "TEST" FROM lt_table;

b) If you are using regular SQL then you cannot use variables, meaning you need to do everything in subselects, e.g.:

SELECT "TEST" FROM (SELECT '1' as "TEST" FROM DUMMY);

Hope I could help.

Regards,

-Irvin.

[rwbordignon]

hello @ironDin, sqlscript...

does it only work when you use local tables or is it possible to use views?

exemple...
temp_1 = select * from view_1;

return select * ::tem_p1