topic Re: SAP SQL connector: Left join issue in Connectivity & Data Prep

SAP SQL connector: Left join issue

BumbaJuck — Mon, 13 Dec 2021 21:26:18 GMT

I'm having a issue with SAP connector when left joining multiple tables. Here is a simple example that causes the error:

TEST: SQL SELECT GLPCA~GL_SIRID, PRPS~PSPNR, EQUI~EQNUM FROM GLPCA left join PRPS on GLPCA~PS_PSP_PNR = PRPS~PSPNR left join EQUI on PRPS~EQUNR = EQUI~EQUNR;

This is the error I'm receiving:

One strange thing is that if I change any of the "left join" to a "join" the query works.

Looking in the log:

10:18:30,925 [1] - SQLQueryParserService.Parse() - About to parse query:
SELECT
GLPCA~GL_SIRID,
PRPS~PSPNR,
EQUI~EQNUM
FROM GLPCA
left join PRPS
on GLPCA~PS_PSP_PNR = PRPS~PSPNR
left join EQUI
on PRPS~EQUNR = EQUI~EQUNR

So far everything looks OK, but later in the log:

10:18:31,375 [1] - SQL Line(s):
10:18:31,375 [1] - SELECT GLPCA~GL_SIRID PRPS~PSPNR EQUI~EQNUM FROM GLPCA LEFT
10:18:31,375 [1] - JOIN PRPS ON GLPCA~PS_PSP_PNR = PRPS~PSPNR LEFT JOIN EQUI
10:18:31,375 [1] - ON PRPS~EQUNR = EQUI~EQUNR

it looks like all commas have been removed. Which also the error points to "List must be separated with commas".

If that is the case, I don't know why they are removed since SAP recommends using commas "In Open SQL, all operands in lists can now be separated by commas and this is also the recommended way of separating them from Release 7.40, SP05"

This is the SAP version being used:

Any ideas ? Thanks

Re: SAP SQL connector: Left join issue

kaushiknsolanki — Wed, 26 Feb 2020 08:53:51 GMT

Just a preliminary observation.

Change ~ to Dot(.) and try

Re: SAP SQL connector: Left join issue

Hakan_Ronningberg — Wed, 26 Feb 2020 09:49:08 GMT

Hi,

The fields should be separated by space an not by commas. The error message you get is bad. ☹️

The reason for the error is that SAP changed the syntax check to Strict Mode in Release 7.40, SP08. It is not allowed to compare fields from the left side only:
on PRPS~EQUNR = EQUI~EQUNR

SAP writes:

The syntax check is performed in strict mode for SP08 in the following cases:

•Use of the additions LIKE, IN, and NOT plus the operators OR or NOT in an ON condition.
•Outer join without a comparison between columns on the left and right sides.

Regards,
Håkan

Re: SAP SQL connector: Left join issue

BumbaJuck — Wed, 26 Feb 2020 13:40:31 GMT

Hello Håkan,

In SAP ( transaction DB02 / Diagnostics / SQL Editor ) if I use the "exact same Query" (if I'm correct SQL editor uses Native SQL) it works without any problem.

SELECT GLPCA.GL_SIRID, PRPS.PSPNR, EQUI.EQUNR FROM GLPCA left join PRPS on GLPCA.PS_PSP_PNR = PRPS.PSPNR left join EQUI on PRPS.EQUNR = EQUI.EQUNR

Is it possible to switch to Native SQL with the connector ?

Is it possible to manipulate the Open SQL query to somehow achieve the same result as with the Native Query ?

Re: SAP SQL connector: Left join issue

Hakan_Ronningberg — Wed, 26 Feb 2020 14:19:46 GMT

Hi,

Open SQL defines the subset of ABAP statements that enable direct access to data from the central database of the current AS ABAP. The SQL statement has to pass the SAP syntax check, otherwise the ABAP statements used to fetch the data will fail. Unfortunately the bad error message in this case is generated by the SAP syntax check, so until SAP makes a correction in their syntax check, we have to live with it.

It is not possible to change the SQL connector to use Native SQL instead of Open SQL.

Maybe you can split your SQL statement into several statements and merge the result in the script? However I don't have any example of how to do that.

I hope you will find a solution!

Regards,
Håkan

Re: SAP SQL connector: Left join issue

BumbaJuck — Thu, 27 Feb 2020 06:38:35 GMT

Thanks for the support Håkan

Re: SAP SQL connector: Left join issue

stelei — Fri, 13 Jan 2023 06:02:19 GMT

Hello

Does anybody have a solution on this topic yet?
A workaround would be to load the second LEFT JOIN separatly and then LEFT JOIN with QLIK-Syntax.