Qlik Community

Qlik Connectors Discussions

Discussion Board for collaboration regarding Qlik Connectors.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

SAP SQL connector: Left join issue

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:

Capture.PNG

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:

Capture1.PNG

Any ideas ? Thanks

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: SAP SQL connector: Left join issue

Just a preliminary observation.

Change ~ to Dot(.) and try

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted

Re: SAP SQL connector: Left join issue

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

Highlighted
Contributor
Contributor

Re: SAP SQL connector: Left join issue

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 ?

Highlighted

Re: SAP SQL connector: Left join issue

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

Highlighted
Contributor
Contributor

Re: SAP SQL connector: Left join issue

Thanks for the support Håkan