Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Call a function in SQL LOAD

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

Tags (2)
4 Replies
MVP
MVP

Re: Call a function in SQL LOAD

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
chauhans85
Esteemed Contributor

Call a function in SQL LOAD

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

Not applicable

Call a function in SQL LOAD

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 ?

Not applicable

Call a function in SQL LOAD

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.

Community Browser