topic Re: Functions in sql query in QlikView

Functions in sql query

Mahamed_Qlik — Mon, 25 Apr 2016 10:52:01 GMT

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

Re: Functions in sql query

vikasmahajan — Mon, 25 Apr 2016 11:18:46 GMT

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

Re: Functions in sql query

avinashelite — Mon, 25 Apr 2016 11:33:09 GMT

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

Re: Functions in sql query

Mahamed_Qlik — Mon, 25 Apr 2016 12:29:40 GMT

Hi Vikas,

Tell me the one.

Re: Functions in sql query

Mahamed_Qlik — Mon, 25 Apr 2016 12:30:59 GMT

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?

Re: Functions in sql query

avinashelite — Mon, 25 Apr 2016 12:32:45 GMT

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

Re: Functions in sql query

vikasmahajan — Tue, 26 Apr 2016 05:21:36 GMT

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

Re: Functions in sql query

Mahamed_Qlik — Tue, 26 Apr 2016 06:52:46 GMT

Hi Vikas,

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

Regards,

Mahamed

Re: Functions in sql query

Peter_Cammaert — Tue, 26 Apr 2016 07:03:28 GMT

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

Re: Functions in sql query

Mahamed_Qlik — Tue, 26 Apr 2016 07:37:47 GMT

Hi Peter,

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

Regards,

Mahamed

Re: Functions in sql query

petter — Tue, 26 Apr 2016 07:42:40 GMT

Hi Mahamed,

Most of the time it seems that one can copy and paste SQL code from a SQL query tool and but it between

SQL and the semicolon in a load script.

There is however at least one pitfall. QlikView is field oriented and not table oriented. That is the field has center-stage even though a field participates in one or more tables - it is the field that is the king. Relational databases on the other hand with SQL as the language being used to interface with them are table-oriented and the columns are merely peasants that belong to the table that is the king. Well they are more like serfs in this setting actually.

To be precise:

QlikView needs each and every column coming from a SQL SELECT to be uniquely named. And it must be the column name alone that is unique. It is not sufficient that the combination of table and column name is unique within the SELECT. Sadly - QlikView doesn´t even give a very helpful error message when there are "duplicate" column names coming from the SELECT statement. I cant really imagine why QlikView doesnt just add a sequence number of some sort to duplicated column names when it needs to use that name as a field name getting it into memory... that would be a simple, elegant and smooth handling of this "issue" instead of breaking with a non-sensical error message.

By the way the results from a SELECT statement has no limitation whatsoever when it comes to column names. Because a SELECT statements results does not have the restrictions of a relational database table - even though it might look like a table it is not identical to a TABLE.

My advice to avoid such problems:

If you have a rather unclear SQL statement when it comes to column names - add an alias to each column by modifying your SQL. Then you make sure that each column has a unique column-name in the particular result set you try to load into QlikView.

I have a strong hunch that your problem is in the all inclusive TWS_MIS.mis.TWS_ItinHotel.*.

This probably brings in two columns with the same name. QlikView doesnt care if these are from two

different tables - so the semantics of QlikView IS different from a SQL database.

You can rename the columns in SQL by using AS to give it a new name.

SQL

SELECT

Table1.ColumnA AS Table1_ColumnA

,Table2.ColumnA AS Table2_ColumnA

FROM

.....

;

Re: Functions in sql query

Mahamed_Qlik — Tue, 26 Apr 2016 07:49:55 GMT

Hey Peter,

Thanks for your helpful info.

Regards,

Mahamed