Convert SQL query into Qlik Sense

[kasimyc]

Hi guys,
I want to convert the SQL query I mentioned below into a format that qlik sense can understand. Can you help me with this?

Thanks ALL

"

SELECT
ODS.ddd_ARC_TABLE1_EXTERNAL_ID3,
ODS.ddd_ARC_TABLE1_EXTERNAL_ID5,
ODS.ddd_ARC_TABLE1_NAME,
ODS.ddd_ARC_TABLE2BATCH_ID,
CUSTCALL.MAX_CALL_DATE,
CUSTCALL.MIN_CALL_DATE,
ODS.ddd_ARC_TABLE2LIST_ID,
COUNT(ODS.ddd_ARC_TABLE2CUST_KEY),
TRUNC(ODS.ddd_ARC_BATCH_MASTER.IMPORT_START_DATE),
COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN PREDIAL2.RESULT IN ('FLAG 1 NO','FLAG 2 NO','IP VAR,KVKK YOK') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (12) THEN ODS.ddd_ARC_TABLE2CUST_KEY END) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 0 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) AND ODS.ddd_ARC_TABLE2RES_REACHED = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
CASE WHEN COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) = 0 THEN 0
ELSE CAST(COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) AND ODS.ddd_ARC_TABLE2RES_REACHED = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END) AS FLOAT)
/ CAST(COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) AS FLOAT) * 100
END ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_IS_SUCCESSFUL = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END) ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (7) THEN ODS.ddd_ARC_TABLE2CUST_KEY END) ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (9) AND ODS.ddd_ARC_TABLE2LAST_AGENT_ID = 'DIALER' THEN ODS.ddd_ARC_TABLE2CUST_KEY END) ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (8,9) AND ODS.ddd_ARC_TABLE2LAST_AGENT_ID <> 'DIALER' THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_CTI_ID IN (11,12) THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (3) THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 2 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 3 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 4 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2CALL_COUNT = 5 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2CALL_COUNT > 5 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_EXPLANATION = 'CANCEL' THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
TO_DATE(ODS.ddd_ARC_TABLE2EXPIRE_DATE)-TRUNC(CURRENT_DATE),
NVL(BATCH_DETAILS2.IMPORT_EDILEMEYEN,0)
FROM
ODS.ddd_ARC_BATCH_MASTER RIGHT OUTER JOIN ODS.ddd_ARC_CDS ON (ODS.ddd_ARC_TABLE2BATCH_ID=ODS.ddd_ARC_BATCH_MASTER.BATCH_ID)
LEFT OUTER JOIN (
SELECT
ODS.ddd_ARC_LOGDIAL.CUST_KEY ,
ODS.ddd_ARC_LOGDIAL.RESULT
FROM
ODS.ddd_ARC_LOGDIAL
WHERE
ODS.ddd_ARC_LOGDIAL.RESULT <> 'Allowed'
AND
ODS.ddd_ARC_LOGDIAL.ID IN (SELECT MAX(ODS.ddd_ARC_LOGDIAL.ID) FROM ODS.ddd_ARC_LOGDIAL WHERE ODS.ddd_ARC_LOGDIAL.RESULT <> 'Allowed' GROUP BY CUST_KEY)

) PREDIAL ON (PREDIAL.CUST_KEY=ODS.ddd_ARC_TABLE2CUST_KEY)
LEFT OUTER JOIN (
SELECT
ODS.ddd_ARC_CUSTCALL.BATCH_ID,
MIN( ODS.ddd_ARC_CUSTCALL.CALL_DATE) AS MIN_CALL_DATE,
MAX(ODS.ddd_ARC_CUSTCALL.CALL_DATE) AS MAX_CALL_DATE
FROM ODS.ddd_ARC_CUSTCALL
GROUP BY ODS.ddd_ARC_CUSTCALL.BATCH_ID
) CUSTCALL ON (CUSTCALL.BATCH_ID=ODS.ddd_ARC_TABLE2BATCH_ID)
LEFT OUTER JOIN (
SELECT
ODS.ddd_ARC_LOGDIAL.CUST_KEY,
ODS.ddd_ARC_LOGDIAL.RESULT
FROM ODS.ddd_ARC_LOGDIAL
WHERE ODS.ddd_ARC_LOGDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO')
AND ID IN (SELECT MAX(ODS.ddd_ARC_LOGDIAL.ID) FROM ODS.ddd_ARC_LOGDIAL
WHERE ODS.ddd_ARC_LOGDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') GROUP BY ODS.ddd_ARC_LOGDIAL.CUST_KEY)
) PREDIAL2 ON (PREDIAL2.CUST_KEY=ODS.ddd_ARC_TABLE2CUST_KEY)
LEFT OUTER JOIN (
SELECT BATCH_ID, COUNT(*) AS IMPORT_EDILEMEYEN
FROM ODS.ddd_ARC_BATCH_DETAILS
WHERE STATUS = 2
GROUP BY BATCH_ID
) BATCH_DETAILS2 ON (BATCH_DETAILS2.BATCH_ID=ODS.ddd_ARC_TABLE2BATCH_ID)
WHERE
ODS.ddd_ARC_TABLE2ENABLED IN ( 1 )
GROUP BY
ODS.ddd_ARC_TABLE1_EXTERNAL_ID3,
ODS.ddd_ARC_TABLE1_EXTERNAL_ID5,
ODS.ddd_ARC_TABLE1_NAME,
ODS.ddd_ARC_TABLE2BATCH_ID,
CUSTCALL.MAX_CALL_DATE,
CUSTCALL.MIN_CALL_DATE,
ODS.ddd_ARC_TABLE2LIST_ID,
TRUNC(ODS.ddd_ARC_BATCH_MASTER.IMPORT_START_DATE),
TO_DATE(ODS.ddd_ARC_TABLE2EXPIRE_DATE)-TRUNC(CURRENT_DATE),
NVL(BATCH_DETAILS2.IMPORT_EDILEMEYEN,0)   "

[PrashantSangle]

try using this query directly in qlik.

[ashok1203]

try using the same query in qlik, by mentioning SQL at starting position

Ex: SQL query

[kasimyc]

Unfortunately, it didn't work. I got an error for the date format, I also got an error for "invalid number of arguments". In the code snippet, there are terms / uses that qliksense does not accept. For this reason, I could not find exactly where I was hanging out. I would be grateful if you help

[kasimyc]

Hi PrashantSangle, I tried this solution, but it didn't work. I got an error ora-00909 and date format. How can I make all of the code blog that qliksens can interpret? Is there a tool for this?

thank you for the answer

[ArnadoSandoval]

Hi @kasimyc 

Would you be able to create a view out of your query in Oracle?  I looked at  your query yesterday finding too many columns missing aliases, but if this query run in Oracle, then create a view as suggested and consume the view in Qlik.

Hope this helps,

[kasimyc]

Hi @ArnadoSandoval,

Can you tell me which is the field you identified as the missing field? In the query, I have specified the names of some fields as dummy. Have you spotted a problem due to this, I'm asking to be sure?

thanx 

[kasimyc]

After the change I made for the error I received in date format, I now get an error of argument number. If I fix this I don't know what the next error will be 🙂

Thanks for your help. But I haven't solved the problem yet

[ArnadoSandoval]

Hi @kasimyc 

Two days ago I dived into your query, the first thing I tried was to understand it logic and added some indentation (queries this large without indentation are confusing to be mild) anyhow, below I am showing all the SELECT fields up to the FROM statement

SELECT
       ODS.ddd_ARC_TABLE1_EXTERNAL_ID3
     , ODS.ddd_ARC_TABLE1_EXTERNAL_ID5
     , ODS.ddd_ARC_TABLE1_NAME
     , ODS.ddd_ARC_TABLE2BATCH_ID
     , CUSTCALL.MAX_CALL_DATE
     , CUSTCALL.MIN_CALL_DATE
     , ODS.ddd_ARC_TABLE2LIST_ID
     , COUNT(ODS.ddd_ARC_TABLE2CUST_KEY)
     , TRUNC(ODS.ddd_ARC_BATCH_MASTER.IMPORT_START_DATE)
     , COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN PREDIAL2.RESULT IN ('FLAG 1 NO','FLAG 2 NO','IP VAR,KVKK YOK') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (12) THEN ODS.ddd_ARC_TABLE2CUST_KEY END) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 0 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) AND ODS.ddd_ARC_TABLE2RES_REACHED = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , CASE WHEN COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) = 0 THEN 0
ELSE CAST(COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) AND ODS.ddd_ARC_TABLE2RES_REACHED = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END) AS FLOAT) / CAST(COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) AS FLOAT) * 100
END 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_IS_SUCCESSFUL = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END) 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (7) THEN ODS.ddd_ARC_TABLE2CUST_KEY END) 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (9) AND ODS.ddd_ARC_TABLE2LAST_AGENT_ID = 'DIALER' THEN ODS.ddd_ARC_TABLE2CUST_KEY END) 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (8,9) AND ODS.ddd_ARC_TABLE2LAST_AGENT_ID <> 'DIALER' THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_CTI_ID IN (11,12) THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (3) THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 2 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 3 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 4 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2CALL_COUNT = 5 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2CALL_COUNT > 5 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_EXPLANATION = 'CANCEL' THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , TO_DATE(ODS.ddd_ARC_TABLE2EXPIRE_DATE)-TRUNC(CURRENT_DATE)
     , NVL(BATCH_DETAILS2.IMPORT_EDILEMEYEN,0)
FROM
--

• All the COUNT() fields do not have alias, so no column name, e.g. no field name.
• Near the end, you got a Date difference without alias, e.g. no column name.
• Of course, Qlik may generate their column names randomly, but why, it is the developer responsibility to properly identify the information returned by the query.

My suggestion for a view is based on these points:

• The columns will receive a name, for the world to know (the world is your using community consuming your solution)
• Oracle will do a better job resolving the query (it is not that Qlik can't); and the Oracle's team will keep ownership of its logic, so future updates to the schema will propagate to this view; if you don't take this approach, you are adding an extra level of difficulty to you solution.
• We do not implement Qlik dashboards and applications by running database queries to collect the data to be presented by the Qlik UI via a dashboard or application; with Qlik, we prepared a data model, in QVDs who are a direct representation of the backend database, we refresh these data model daily.

Hope this helps,

[kasimyc]

I will also try loading tables on qlik. Thank you