topic Re: Call a function in SQL LOAD in QlikView

Call a function in SQL LOAD

Tue, 18 Oct 2011 12:34:53 GMT

Hello,

I would call a function in sql for loading my fact table.
For information, here is my example:

LOAD

S_ASSET_ASSET_NUM

S_ASSET_1_ASSET_NUM;

SQL SELECT

"S_ASSET"."ASSET_NUM" as S_ASSET_ASSET_NUM

"S_ASSET_1"."ASSET_NUM" as S_ASSET_1_ASSET_NUM

FROM "siebeldb"."dbo"."S_ASSET" "S_ASSET"

LEFT OUTER JOIN "siebeldb"."dbo"."S_ASSET" "S_ASSET_1" ON "S_ASSET_1".ROOT_ASSET_ID IN ((select dbo.RootAssetId("S_ASSET".ROW_ID) from S_ASSET)) and "S_ASSET_1".ROW_ID = "S_ASSET_1".ROOT_ASSET_ID

My Function dbo.RootAssetIdin SQL

FUNCTION dbo.RootAssetId (@IdAsset VARCHAR(20))

RETURNS VARCHAR(20)

BEGIN

declare @IdParent VARCHAR(20);

declare @nbEnregistrement INTEGER;

SET @nbEnregistrement = 0

WHILE (@nbEnregistrement=0)

BEGIN

SELECT @nbEnregistrement = (SELECT COUNT(*) FROM S_ASSET WHERE (@IdAsset = ROW_ID and ROW_ID = ROOT_ASSET_ID AND PAR_ASSET_ID is null))

SET @IdParent = (SELECT ROOT_ASSET_ID FROM S_ASSET WHERE ROW_ID = @IdAsset)

SET @IdAsset = (SELECT PAR_ASSET_ID FROM S_ASSET WHERE ROW_ID = @IdAsset)

END

RETURN @IdParent

END

My questions are:
- Is this possible?
- Where do I report my position?
- Can I use it as rewriting or in another language. ?

Thank you in advance for your help.
cordially

Re: Call a function in SQL LOAD

jonathandienst — Tue, 18 Oct 2011 12:56:42 GMT

I expect you can use the function in a SQL SELECT statement, not in a LOAD statement. SQL SELECT (or SQL EXECUTE) passes the query to the SQL server.

Just replace LOAD with SQL SELECT.

Hope that helps

Jonathan

Call a function in SQL LOAD

SunilChauhan — Tue, 18 Oct 2011 13:23:41 GMT

you can load afunction from sql or oracle in database like

Load

select

field1,

field2,

fild3,

fieeld4,

tablename.functionname(arg1,agr2.......) as calculationname

from tablename

calculationname name return the output of function

Call a function in SQL LOAD

Wed, 19 Oct 2011 13:52:10 GMT

Hello,

When I execute my call, I have the following message:

SQL##f - SqlState: 42000, ErrorCode: 229, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'RootAssetId', database 'siebeldb', owner 'dbo'.

Can you help me ?

Call a function in SQL LOAD

Wed, 19 Oct 2011 14:56:56 GMT

Request the database administrator to grant execute previlages to the account with which your are connecting. If you have any other account with execute previlages then try with that.

Regards,

Kiran.