Question Re: Using WITH SQL Statement in SAP Datapshere in Technology Q&A

Using WITH SQL Statement in SAP Datapshere

DerekJ — Mon, 29 Jan 2024 15:17:45 GMT

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"

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

My thanks in advance for any assistance

Re: Using WITH SQL Statement in SAP Datapshere

XaviPolo — Tue, 30 Jan 2024 08:22:48 GMT

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,

Re: Using WITH SQL Statement in SAP Datapshere

DerekJ — Tue, 30 Jan 2024 09:26:31 GMT

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

Re: Using WITH SQL Statement in SAP Datapshere

DerekJ — Tue, 30 Jan 2024 09:31:51 GMT

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"

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

;