3 Replies Latest reply on Aug 28, 2012 6:23 PM by tomu2010

    can we do update operation on worktable values with event script

    tomu2010
      Hi i am writing the below sql to update the worktable values. can i do that please help

      strSQL2 = "UPDATE" &strWorkTable&_
      "SET" &strWorkTable& ".[UD4]=" &"a" &".[Location_Type]" &" "&_
           "FROM" &strTblLU &"a"&" "&_
           "WHERE" &strWorkTable& ".[entity]=" &"a"&".[Location]"
      actually i want to populate the UD4 dimension in the mapping using above query please help

      Set rs2 = DW.DataAccess.farsFireHose(strSQL2, False) when this sql is triggered will this update the worktable? basically i want to see the updated values in the mapping screen for that dimension.

      i am getting this error when i try to do something with the result set

      ERROR:
      Code............................................. 3704
      Description...................................... Operation is not allowed when the object is closed.
      At Line: 31
      Procedure........................................ clsEventHandler.PobjMultiEng_MultiLoadAction
      Component........................................ upsWObjEventHandlerDM
      Version.......................................... 1112
      Thread........................................... 4816



      Thanks for reading this and spending your valueble time in helping
        • 1. Re: can we do update operation on worktable values with event script
          SH_INT
          Which event script and where are you getting the reference for the variable strWorkTable from?
          1 person found this helpful
          • 2. Re: can we do update operation on worktable values with event script
            tomu2010
            Hi SH,

            i am currently doing it in multiloadaction, event is aftimporttext ( although i tried with different events but it never updates). this code perfectly works fine in importaction startmapprocess event( the values are being updated in table and it can be seen in mapping). this is not happening for multiload action. could you please tell me which event i have to use to make it work for multiload.

            currently i use strWorkTable = "[" &API.State.GstrActiveWorkTableName& "]" this works fine with importaction script. could you please tell me which worktable should be used?

            Actually with the above variable it is able to find the worktable value but the value is not getting displayed after the update. how i can tell this is if i give UD4X in the update query instead of UD4 then this gives me the below error .

            Conversion failed when converting the varchar value 'LT_100' to data type int.
            UPDATE[tWgouma0437646766254]SET[tWgouma0437646766254].[UD4R]=a.[Location_Type] FROM[dbo].[Location_Type_LookUp]a WHERE[tWgouma0437646766254].[entity]=a.[Location]

            so this means the query is updating the value in some table but the value is not pushed to the mapping.

            Thanks
            • 3. Re: can we do update operation on worktable values with event script
              tomu2010
              Hi SH

              The multiload works fine now i had to change the code slightly to update the tdataseg table the value is pushed to the mapping.
              the dimension value gets updated during import.

              Thanks for your time i appreciate it.

              Regards