Using WITH SQL Statement in SAP Datapshere

[DerekJ]

I'm trying to use a WITH statement in SAP Datasphere SQL View. It's coming up with error of 

"Mismatched WITH, expecting ‘(’, SELECT"

My script works outside SAP Datasphere, so it's some synatx difference I'm not spotting. The psuedo code I am using is..

WITH

"Table1" AS 

(SELECT

"Field1",

"Field2",

Count(*) AS "Count"

FROM

"TABLE1"

WHERE "ActiveFlag"='X'

GROUP BY

"Field1",

"Field2"),

"Table2" AS

(SELECT

"Field1",

"Measure1"

FROM

"TABLE2" 

WHERE "ActiveFlag"='X')

SELECT 

"Table1"."Field1",

"Table1"."Field2",

"Table2"."Measure1"

FROM "Table1"

LEFT JOIN "Table2" 

ON 

"Table1"."Field1"="Table2"."Field1"

My thanks in advance for any assistance

[XaviPolo · Accepted solution]

Have you defined the SQL View as a "SQL Script"?

You can always use variables as in:

Table1 = SELECT "Field1", "Field2", Count(*) AS "Count" 
FROM "TABLE1"
WHERE "ActiveFlag"='X'
GROUP BY "Field1", "Field2";
 
Table2 = SELECT "Field1","Measure1" 
FROM "TABLE2" 
WHERE "ActiveFlag"='X';
 
return SELECT 
"Table1"."Field1", "Table1"."Field2", "Table2"."Measure1"
FROM :Table1 as "Table1" 
LEFT JOIN :Table2 as "Table2" 
ON "Table1"."Field1"="Table2"."Field1"
;

Regards,

[DerekJ · Accepted solution]

Thanks, that's worked, I didn't think to set as variables. FYI I also managed to fix WITH issue, I will post in main chat for anyone using WITH that needs answer

[DerekJ]

FYI, aswell as XaviPolo's alternative solution. For anyone using WITH and wanting to know issue, it was actually that the name of the WITH table and then in the SELECT were the same, normal SQL editor is OK with this, but in Datasphere it does not like the same name. I had to change to SQLScript, and change the SELECT so table is named different. 

return

WITH

"Table1" AS 

(SELECT

"Field1",

"Field2",

Count(*) AS "Count"

FROM

"TABLE1"

WHERE "ActiveFlag"='X'

GROUP BY

"Field1",

"Field2"),

"Table2" AS

(SELECT

"Field1",

"Measure1"

FROM

"TABLE2" 

WHERE "ActiveFlag"='X')

SELECT 

"Tab1"."Field1",

"Tab1"."Field2",

"Tab2"."Measure1"

FROM "Table1" AS "Tab1"

LEFT JOIN "Table2" AS "Tab2"

ON 

"Tab1"."Field1"="Tab2"."Field1"

;