topic Re: SELECT fails in QlikView

SELECT fails

Thu, 07 Jan 2016 10:36:10 GMT

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

Re: SELECT fails

Peter_Cammaert — Thu, 07 Jan 2016 12:25:05 GMT

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.

Re: SELECT fails

Thu, 07 Jan 2016 13:05:33 GMT

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"

Re: SELECT fails

Anonymous — Thu, 07 Jan 2016 13:10:42 GMT

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.

Re: SELECT fails

Peter_Cammaert — Thu, 07 Jan 2016 13:12:14 GMT

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

Re: SELECT fails

jonathandienst — Thu, 07 Jan 2016 13:13:46 GMT

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.

Re: SELECT fails

Thu, 07 Jan 2016 13:21:22 GMT

Dear all,

many thanks for your answers. I now understood how it must work!

Thanks a lot.