Functions in sql query

[Mahamed_Qlik]

Hi All,

Below given query hits my reload time by 3days..

SELECT top 100 TWS_MIS.mis.ConvertCurrency(TWS_MIS.mis.TWS_ItinHotel.bookingid,TWS_MIS.mis.TWS_ItinHotel.rateamount,TWS_MIS.mis.TWS_ItinHotel.ratecurrency,'GBP',TWS_MIS.mis.TWS_Header.bkcreated) RATEAMOUNTGBP

,TWS_MIS.mis.TWS_ItinHotel.*

FROM TWS_MIS.mis.TWS_ItinHotel left join TWS_MIS.mis.TWS_Header

ON (TWS_MIS.mis.TWS_ItinHotel.BookingId=TWS_MIS.mis.TWS_Header.BookingId);

The main thing in the query is I am calling the function "TWS_MIS.mis.ConvertCurrency" which hips up the reload time.

Note: Query works proper in sql server.

Kindly advise for the best solution for this.

Regards,

Mahamed

[vikasmahajan]

hits my reload time by 3days ? where you are loading in qlik ?

if yes then really this is very bad , you can create a cursor into sql load data into qv from cursor it may faster.

Vikas

[avinashelite]

One solution would be : To handle the ConvertCurrency logic in Qlik ..so that the reload time gets reduced and Qlikview had effective ways to handle the conversion scenario

[Mahamed_Qlik]

Hi Vikas,

Tell me the one.

[Mahamed_Qlik]

Hi vikas,

I running the query in initial load app with connectivity to sql.

And let me know how do we can create the cursor?

[avinashelite]

Try to build the currency conversion logic in Qlikview instead of handling the same in SQL query... so that SQL query will run properly

[vikasmahajan]

you do one thing  create logic of currency into qlikview as Avinash said  loading data into qv is always faster without any joins , Load 2 tables and create qvd . load this qvd into qvw and build logic of currency into qlikview itself  rather than apply logic in sql.

HTH

Vikas

[Mahamed_Qlik]

Hi Vikas,

So you mean, It is always better to apply the logic in qlik than the sql query?

Regards,

Mahamed

[Peter_Cammaert]

This is weird. If you copy the exact same query from sql server (management studiio) into QlikView load script, it should work in exactly the same way. On condition that you use the same connection, machine, account, privileges and database. All surrounding factors.

Whether you launch this query from a sql server tool or from QlikView load script, the SELECT (including any calls to stored procedures) is always passed on to and executed by the Database engine, not by QlikView or by the sql server tool.

For example, try to execute the CALL to the function straight from the QlikView load script using SQL EXEC and appropriate parameters. Do you get any results from a single call?

Peter

[Mahamed_Qlik]

Hi Peter,

Can you please let me know more about how to use SQL EXEC and CALL from Qlikvire script?

Regards,

Mahamed