I would like to convert three subqueries in SQL to the equivalent QLIK commands. Essentially to reduce the extra charge on the DB2 database
The first query is the first entry of any ticket so we need this to calculate how long the ticket has been open
ROW_NUMBER is 1 this will be the first ticket
WITH DMD_FR AS
(SELECT
DEMANDE_ID
,DEMANDE_CREATION
,ROW_NUMBER() OVER (PARTITION BY DEMANDE_ID ORDER BY DEMANDE_DATE || DEMANDE_TIME DESC) AS ROW_NUMBER
FROM
$(vServer).DEMANDE
WHERE
STATUT='02'
AND DEMANDE_CREATION >= '$(vOLDOPEN)'),
The second query is the latest entry of any unclosed ticket
DMD_LTST AS
(SELECT
DEMANDE_ID
,MAX(DEMANDE_DATE || DEMANDE_TIME) AS DEMANDE_LTST
FROM
$(vServer).DEMANDE DMD_LTST
WHERE
STATUT IN ('02', '06', '07')
AND IDRC = '3340635'
AND DEMANDE_CREATION >= '$(vOLDOPEN)'
AND DEMANDE_MODIF_CODE IN ('0000', '0023', '0024', '8803')
AND NOT EXISTS (SELECT 1 FROM
DB2PROD.DEMANDE DMD_88
WHERE DMD_88.DEMANDE_ID = DMD_LTST.DEMANDE_ID
AND DMD_88.STATUT = '88' )
GROUP BY DEMANDE_ID )
This is the main query, so I am assuming that if I load this first, I could then run two QLIK queries on the loaded table to identify the above two queries
SELECT
DMD.DEMANDE_ID
,DMD.DEMANDE_CREATION
,DMD.DEMANDE_DATE
,DMD.DEMANDE_TIME
,CASE
WHEN DMD_LTST.DEMANDE_ID IS NULL THEN '0'
ELSE '1'
END AS LATEST_ROW
,DMD.CORBEILLE
,DMD.STATUT
,CASE
WHEN DC.DEMANDE_ID IS NULL THEN 'O'
ELSE 'C'
END AS DEMANDE_STATUT
,DMD.DEMANDE_DATA_TYPE
,DSD_FR.STATUS_TICK_DESC AS STATUS_TICK_DESC_FR
,DSD_NL.STATUS_TICK_DESC AS STATUS_TICK_DESC_NL
FROM
DMD_FR
,$(vServer).DEMANDE DMD LEFT JOIN $(vServer).DEMANDE DC ON DMD.DEMANDE_ID = DC.DEMANDE_ID AND DC.STATUT='88'
LEFT JOIN DMD_LTST ON DMD.DEMANDE_ID = DMD_LTST.DEMANDE_ID AND (DMD.DEMANDE_DATE || DMD.DEMANDE_TIME) = DMD_LTST.DEMANDE_LTST
,$(vServer).DEMANDE_STATUS_DESC DSD_FR
,$(vServer).DEMANDE_STATUS_DESC DSD_NL
WHERE
DMD.STATUT IN ('02', '06', '07','88')
AND DMD.IDRC = '3340635'
AND DMD.DEMANDE_CREATION >= '$(vOLDOPEN)'
AND DMD_FR.DEMANDE_ID = DMD.DEMANDE_ID
AND DMD_FR.ROW_NUMBER = 1
AND DSD_FR.STATUS_CD = DMD.STATUT
AND DSD_FR.LING_ROLE_CD = 'FR'
AND DSD_NL.STATUS_CD = DMD.STATUT
AND DSD_NL.LING_ROLE_CD = 'NL'
AND DMD.DEMANDE_MODIF_CODE IN ('0000', '0023', '0024', '8803');
STORE DEMANDE_CALLBACK INTO LIB://QVD/DEMANDE_CALLBACK.qvd;
DROP TABLE DEMANDE_CALLBACK;
I don't expect someone to write the query for me, just a pointer in the right direction would help !!
Thanks in advance
