Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have the following SQL script that i need to use with my qlik appliocation. It works with SQL PLUS but unable to work to load data in QLIK VIEW. Could you help me to understand how edit to have it compatible woth Qlik View?
Thank you
SELECT mtm.*,
PF.PRTFL_SHRT_NAME "Portfolio",
cpny.CMPNY_NAME "Company",
CP.CNTPTY_NAME "Counterparty",
VS.VSSL_NAME "Vessel",
MM.MTM_MTD_NAME "MTM Method",
ET.EXPSR_TYPE_VALUE "Exposure Type",
CASE
WHEN MTM.MTM_WHTIF_TCKR=0
THEN INS1.INSTRMNT_NAME
ELSE SIM_INS1.INSTRMNT_NAME
END "Instrument 1",
CASE
WHEN MTM.MTM_WHTIF_TCKR=0
THEN CIC1.CIC_INSTMNT_PCNT
ELSE SIM_CIC1.CIC_INSTMNT_PCNT
END "Instrument 1 %",
CASE
WHEN MTM.MTM_WHTIF_TCKR=0
THEN INS2.INSTRMNT_NAME
ELSE SIM_INS2.INSTRMNT_NAME
END "Instrument 2",
CASE
WHEN MTM.MTM_WHTIF_TCKR=0
THEN CIC2.CIC_INSTMNT_PCNT
ELSE SIM_CIC2.CIC_INSTMNT_PCNT
END "Instrument 2 %",
cs.cntrct_status_value "Contract_status"
FROM mtm
LEFT JOIN portfolio pf
ON mtm.MTM_PRTFL_ID=PF.PRTFL_ID
LEFT JOIN company cpny
ON mtm.MTM_PRTFL_ID =CPNY.CMPNY_PRTFL_ID
AND mtm.MTM_CMPNY_ID=cpny.cmpny_id
LEFT JOIN counterparty cp
ON mtm.MTM_CNTPTY_ID = CP.CNTPTY_ID
LEFT JOIN vessel vs
ON mtm.mtm_vssl_id=vs.VSSL_ID
LEFT JOIN mtm_method mm
ON mtm.mtm_mtd_id=MM.MTM_MTD_ID
LEFT JOIN EXPOSURE_TYPE ET
ON MTM.MTM_CE_TYPE_ID=ET.EXPSR_TYPE_ID
LEFT JOIN contract_status cs
ON cs.cntrct_status_id = mtm.mtm_ce_stts_id
LEFT JOIN CNTCT_INSTMNT_CPSTN CIC1
ON mtm.mtm_cm_id = CIC1.CIC_CM_ID
AND mtm.mtm_ce_id =CIC1.CIC_CE_ID
AND mtm.MTM_CE_TYPE_ID = CIC1.CIC_CE_TYPE_ID
AND CIC1.CIC_SQNC =1
LEFT JOIN SIM_CNTCT_INSTMNT_CPSTN SIM_CIC1
ON mtm.mtm_cm_id = SIM_CIC1.CIC_CM_ID
AND mtm.mtm_ce_id =SIM_CIC1.CIC_CE_ID
AND mtm.MTM_CE_TYPE_ID = SIM_CIC1.CIC_CE_TYPE_ID
AND SIM_CIC1.CIC_SQNC =1
LEFT JOIN instrument ins1
ON CIC1.CIC_INSTMNT_ID=INS1.INSTRMNT_ID
LEFT JOIN instrument sim_ins1
ON sim_CIC1.CIC_INSTMNT_ID=sim_INS1.INSTRMNT_ID
--left join FORWARD_RATE fr1 on CIC1.CIC_INSTMNT_ID=FR1.FR_INSTRMNT_ID and FR1.FR_INPT_DT='24/11/14' and
--201411+FR1.FR_PRD=SUBSTR(MTM.MTM_PERIOD, 0, 6)
LEFT JOIN CNTCT_INSTMNT_CPSTN CIC2
ON mtm.mtm_cm_id = CIC2.CIC_CM_ID
AND mtm.mtm_ce_id =CIC2.CIC_CE_ID
AND MTM.MTM_CE_TYPE_ID = CIC2.CIC_CE_TYPE_ID
AND CIC2.CIC_SQNC =2
LEFT JOIN CNTCT_INSTMNT_CPSTN sim_CIC2
ON mtm.mtm_cm_id = sim_CIC2.CIC_CM_ID
AND mtm.mtm_ce_id =sim_CIC2.CIC_CE_ID
AND MTM.MTM_CE_TYPE_ID = sim_CIC2.CIC_CE_TYPE_ID
AND sim_CIC2.CIC_SQNC =2
LEFT JOIN INSTRUMENT INS2
ON CIC2.CIC_INSTMNT_ID=INS2.INSTRMNT_ID
LEFT JOIN instrument sim_ins2
ON SIM_CIC2.CIC_INSTMNT_ID=SIM_INS2.INSTRMNT_ID
To put what Peter said in other words - everything from the SQL statement until the next ; (semi colon) is passed verbatim to the DBMS and what works in a tool like SQL Plus should also work without changes in QV - providing you are using an account with the correct access rights.
You do have a SQL keyword in your script?
SQL SELECT .....;
And no, you cannot use LOAD that way, QV cannot do anything with the SQL query.
In theory, you don't need to do anything to make it compatible with QlikView because the script engine doesn't bother about SQL SELECT statements.
On the other hand, do you use the same account in QlikView to connect to Oracle as you do in SQL*Plus? After you make your connection to Oracle, use the Select... button in the script editor to see if any table of the ones used in your SELECT statement refuses to displkay any data in the Preview window.
Dear Peter,
many thanks for your reply.
yes I use same account and on the selection tables all tables required in the script are available.
The thing is that the sintax is not recognize in Qlik view (into script editor) and I'm not sure how I have to write it.
On the other and I'm trying to change the script in this way (using a LOAD and usign ifthenelse instead of CASE) but unsure if it is a correct way.
LOAD SELECT *,
PF.PRTFL_SHRT_NAME as "Portfolio",
cpny.CMPNY_NAME as "Company",
CP.CNTPTY_NAME as "Counterparty",
VS.VSSL_NAME as "Vessel",
MM.MTM_MTD_NAME as "MTM Method",
ET.EXPSR_TYPE_VALUE as "Exposure Type",
if (MTM.MTM_WHTIF_TCKR=0, INS1.INSTRMNT_NAME,SIM_INS1.INSTRMNT_NAME) as "Instrument 1",
if (MTM.MTM_WHTIF_TCKR=0, INS2.INSTRMNT_NAME,SIM_INS2.INSTRMNT_NAME) as "Instrument 2"
We use multiple stage loading for a task like this. Rather than trying to run this complicated script in Qlikview, we would make a view in SQL to stage the data. Then you can see in SQL if you are getting the desired compiled data. Then we would load the data to a QVD file and then bring it to a QVW file.
We do not load any data into a QVW file that is not already in a QVD first.
Replace "LOAD" with "SQL".
LOAD starts a QlikView-only LOAD statement. Not to be mixed with a SELECT which is the same statement, only in a different language.and for a different data source
To put what Peter said in other words - everything from the SQL statement until the next ; (semi colon) is passed verbatim to the DBMS and what works in a tool like SQL Plus should also work without changes in QV - providing you are using an account with the correct access rights.
You do have a SQL keyword in your script?
SQL SELECT .....;
And no, you cannot use LOAD that way, QV cannot do anything with the SQL query.
Dear all,
many thanks for your answers. I now understood how it must work!
Thanks a lot.