Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Mahamed_Qlik
Specialist
Specialist

Functions in sql query

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

11 Replies
petter
Partner - Champion III
Partner - Champion III

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

          .....

;

Mahamed_Qlik
Specialist
Specialist
Author

Hey Peter,

Thanks for your helpful info.

Regards,

Mahamed