SAP SQL Connector Query question

[gs_qlik_nl]

Hi All,

Can I do this SUBSELECT query?

It does not give the right amount of data back to Qlik Sense?

Qlik Sense release is September 2019

SAP 6.0

SAP Connector 7.0

Query:

SQL SUBSELECT * FROM BSEG WHERE BUKRS BELNR GJAHR IN (SELECT BUKRS BELNR GJAHR FROM BKPF WHERE BLART IN ('BF', 'BA', 'AB', 'BB', 'SC') AND CPUDT = '20191021');

If I do a Query on BKPF table with CPUDT on 20191021, I receive a whole lotta more rows. If I do a subselect on the BSEG/BKPF like this one above. It only gives back a few hundred rows. I need at lease a few thousand rows?

Any idea? If I missed some explaination please let me know.

[gs_qlik_nl]

I tried a few things:

SAP SQL connector version is 7.0.1

The following statement doesn't work either, it gives the same result:

SQL SUBSELECT * FROM BSEG WHERE BUKRS BELNR GJAHR
IN (SELECT BUKRS BELNR GJAHR FROM BKPF WHERE CPUDT EQ '20191021'
AND (BLART EQ 'BF' OR BLART EQ 'BA' OR BLART EQ 'AB' OR BLART EQ 'BB' OR BLART EQ 'SC'))

[gs_qlik_nl]

Copied the info from the logfile: Perhaps this helps, I have no idea where to look next?

10:19:52,915 [5] - Output parameter 'TRANSPORT_RELEASE' is not available, this indicates that an older transport is in use. Some features will be disabled and performance might not be optimal. Consider upgrading SAP transports!
10:19:52,915 [5] - SAP Transport information: version: E66K900063; date: 2018-05-05; time: 22:45:03; release: 0; SAP basis: 750; ServicePack:
10:19:52,915 [5] - SapConstants.IsFeatureAvailable() - Feature:SAP-543 for transportRelease:0 isAvailable:False
10:19:52,915 [5] - SapConstants.IsFeatureAvailable() - Feature:SAP-721 for transportRelease:0 isAvailable:False
10:19:52,915 [5] - This transport: 'E66K900063' is not the recommended one for the SAP basis version and connector client version being used, you should consider using an appropriate transport for your SAP basis version and connector client version: 'E66K900148'.

[gs_qlik_nl]

Me again,

This does not make sense.

The tables combined give me the following info. The BUKRS of BKPF can be different from the BUKRS of BSEG rows?

[Hakan_Ronningberg]

Hi,

Online help for SUBSELECT:

https://help.qlik.com/en-US/connectors/Subsystems/SAP_Connectors_Help/Content/Connectors_SAP/Clients...

I tried a similar statement in our test system and it worked fine!

SUBSELECT * FROM BSEG
WHERE BUKRS BELNR GJAHR IN
( SELECT BUKRS BELNR GJAHR FROM BKPF
WHERE ( BLART EQ 'SA' OR BLART EQ 'BA' )
AND CPUDT = '20070305' )

You need to have the knowledge about the relations between the fields in the main and sub table, so the fields correspond to each other.

I noticed you are using very old SAP transports. Maybe you should consider to upgrade both the Windows files and the SAP transports to the latest connector release 7.0.2?

Regards,
Håkan

[gs_qlik_nl]

Thank you for the input. 

I've checked all of the help info, validated the query and it seems to work.

However, could it be that there are BELNR's in BKPF that are not in BSEG?

I noticed you are using very old SAP transports. Maybe you should consider to upgrade both the Windows files and the SAP transports to the latest connector release 7.0.2?

Hmm, the SAP transports are purely from the Qlik SAP connector? I've checked and we installed Qlik SAP connector 7.0.1? Do you have some more info about the SAP transports?

[Hakan_Ronningberg]

Hi,

Unfortunately I'm not familiar with the relation between BSEG and BKPF, so I cannot answer that question.

According to your log, the SAP transport used is E66K900063. This is the transport for connector release 6.1.1. You can read in the Installation Guide for release 7.0.2 which SAP transports should be imported to match the 7.0.2 Windows files:

https://community.qlik.com/t5/Qlik-Connectors-Discussions/Qlik-Connector-7-0-2-for-use-with-SAP-NetW...

Regards,
Håkan