Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlix-Experts,
Í want to improve some of my load scripts and furthermore understand the possibilities of Qlik and the SAP SQL connector.
Task: Get a list of objects, e.g. from CDHDR. Take the objects and load the CDPOS accordingly.
The script below works, but SAP needs to do the screening of table CDHDR twice.
Is it possible to improve the Subselect in a way, that the already loaded information about QBJECTID in CDHDR can be passed over to the subselect?
Thanks in advance
CDHDR:
LOAD *;
SELECT * FROM CDHDR WHERE OBJECTCLAS = 'BANF' AND UDATE = '$(vLastUpdate)';
CDPOS:
LOAD * ;
SQL SUBSELECT * FROM CDPOS WHERE OBJECTID IN
(SELECT OBJECTID FROM CDHDR WHERE OBJECTCLAS = 'BANF' AND UDATE = '$(vLastUpdate)');
Hi!
To optimize your SAP SQL query and avoid reprocessing the CDHDR table twice, you can refactor the query using a temporary table or Common Table Expressions (CTE) depending on the SQL dialect supported by the SAP SQL connector. Here's an approach based on CTE, which allows you to reference the result of a subquery multiple times without duplicating logic or reprocessing:
Approach using CTE (if supported):
WITH Filtered_CDHDR AS (
SELECT OBJECTID
FROM CDHDR
WHERE OBJECTCLAS = 'BANF'
AND UDATE = '$(vLastUpdate)'
)
SELECT * FROM CDPOS
WHERE OBJECTID IN (SELECT OBJECTID FROM Filtered_CDHDR);
In this approach:
The WITH clause (CTE) creates a temporary result set (Filtered_CDHDR) with only the OBJECTIDs that meet the conditions in CDHDR.
Then, in the main SELECT query, you simply reference that filtered list of OBJECTIDs when querying CDPOS.
Approach using Temporary Table (if CTE is not supported):
If your SAP SQL connector does not support CTEs, you could load the filtered data from CDHDR into a temporary table and then use it in your CDPOS query:
-- Step 1: Load filtered OBJECTIDs into a temporary table
CREATE TEMPORARY TABLE temp_filtered_CDHDR AS
SELECT OBJECTID
FROM CDHDR
WHERE OBJECTCLAS = 'BANF'
AND UDATE = '$(vLastUpdate)';
-- Step 2: Use the temporary table in your CDPOS query
SELECT *
FROM CDPOS
WHERE OBJECTID IN (SELECT OBJECTID FROM temp_filtered_CDHDR);
-- Step 3: (Optional) Drop the temporary table after use
DROP TABLE temp_filtered_CDHDR;
In this scenario:
You first create a temporary table (temp_filtered_CDHDR) to hold the OBJECTIDs from CDHDR.
Then, the CDPOS query can reference the temporary table instead of re-querying CDHDR.
Optionally, you can drop the temporary table when it’s no longer needed.
You should check which of these options (CTE or temporary table) is supported by your SAP SQL connector. CTEs are usually preferred if available because they are cleaner and easier to manage than temporary tables.
Hope it helps
regards,
Diego