topic Re: Execute SQL Function in Qlikview Script in QlikView

Execute SQL Function in Qlikview Script

Anonymous — Wed, 12 Feb 2014 19:54:33 GMT

Hi Qlikview Community!

Has anyone successfuly used a sql function in qlikview?

I have seen a ton of started conversations, but it seems we all get stuck with a permissions error.

(I can execute the function in sql with the same permissions, so I know that is not the true error.)

Hope we can figure this out together!

Thanks!

Re: Execute SQL Function in Qlikview Script

Wed, 12 Feb 2014 20:01:09 GMT

Hi,

I usually just pull all the fields I need and then in the preload i use qlikview functions.

You can give that a whirl.

Thanks

Re: Execute SQL Function in Qlikview Script

rbecher — Wed, 12 Feb 2014 20:02:50 GMT

What kind of SQL function? Can you post an example..

Re: Execute SQL Function in Qlikview Script

stevedark — Wed, 12 Feb 2014 20:05:12 GMT

Is this a table based function or a scalar based function? Have you tried wrapping the output from the function in a view?

I would try going to the Settings tab on the load script and tick the Open Databases In Read Write Mode tick box and add the text (mode is write) after the connection string (between the ] and the ; .

Hope one of these things helps.

Steve

Re: Execute SQL Function in Qlikview Script

MK_QSL — Wed, 12 Feb 2014 20:06:51 GMT

You can use QlikView Function by preceding load.

OLEDB CONNECT32 TO .....:

LOAD

DepartmentID,

Name,

GroupName,

Date,

Month(Date) as Month,

Year(Date) as Year;

SQL SELECT

DepartmentID,

Name,

GroupName,

Date

FROM TableName;

Re: Execute SQL Function in Qlikview Script

rbecher — Wed, 12 Feb 2014 20:07:17 GMT

Uh.., dangerous write mode.. Usually this shouldn't be needed to execute a function.

Re: Execute SQL Function in Qlikview Script

stevedark — Wed, 12 Feb 2014 20:10:42 GMT

Personally I tend to avoid using write mode - I can only think of one case where I have had to (the only access I was given to the database was to a Stored Procedure that did some inserts before returning any data) but it is worth trying it to see if the function works.

Re: Execute SQL Function in Qlikview Script

Anonymous — Wed, 12 Feb 2014 20:13:24 GMT

Hi Ralf,

I am trying to work with a user defined function. Here is generic example, hope it paints the picture:

LOAD

Field1,

Field2,

Field3;

SQL

SELECT

Field1,

Field2,

Field3

From Database

Where Database.udf_Table (Field1) = 0

Re: Execute SQL Function in Qlikview Script

Anonymous — Wed, 12 Feb 2014 20:14:29 GMT

I haven't tried wrapping - not sure what that entails.

Write mode is not an option for me, as the user base cannot have those rights.

Re: Execute SQL Function in Qlikview Script

rbecher — Wed, 12 Feb 2014 20:16:34 GMT

Maybe you're using a different database user in QlikView connection?

Re: Execute SQL Function in Qlikview Script

Anonymous — Wed, 12 Feb 2014 20:19:18 GMT

That was my first thought too, but the connection string is the correct user.

Re: Execute SQL Function in Qlikview Script

stevedark — Wed, 12 Feb 2014 20:20:41 GMT

By wrapping in a view I simply meant creating a view that includes the function. So to take your example you could have:

CREATE VIEW dbo.MyView

SELECT

Field1,

Field2,

Field3

From Database

Where Database.udf_Table (Field1) = 0

QlikView would then simply load from the view:

LOAD
Field1,

Field2,

Field3

;

SQL SELECT

Field1,

Field2,

Field3

FROM dbo.MyView

;

This way the fact that a function is being called is one step removed from QlikView.

You may find that the user requires rights above what they currently have in order to execute the User Defined Function. Have you tried firing it in Query Analyser whilst logged in as that user?

Steve

Re: Execute SQL Function in Qlikview Script

rbecher — Wed, 12 Feb 2014 20:25:41 GMT

I think this makes it more complicated because now you have two database objects where you need to grant the rights. The original SQL statement should be fine and will not be touched by QlikView. It's just passed thru the ODBC/OLEDB connection. Of course there could be an issue although..

Re: Execute SQL Function in Qlikview Script

Mon, 29 Dec 2014 19:18:20 GMT

Friends, I have a similar situation. Do we have any standard procedure for executing the SQL table function.