SAP SQL connector: Left join issue

[BumbaJuck]

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

[kaushiknsolanki]

Just a preliminary observation.

Change ~ to Dot(.) and try

[Hakan_Ronningberg]

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

[BumbaJuck]

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 ?

[Hakan_Ronningberg]

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

[BumbaJuck]

Thanks for the support Håkan

[stelei]

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.