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