4 Replies Latest reply: Oct 19, 2011 10:56 AM by Kiran Rokkam RSS

    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