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
