3 Replies Latest reply on Sep 26, 2016 3:09 PM by 2837320

    Creating a View using Before Import Script

    2837320

      Hi Team,

       

      I wanted to execute an SQL statement which will create a view with required data from Underlying table. Can we execute it in the event script. I am not able to do that with ExecuteQuery API.

       

      import java.math.BigDecimal as BigDecimal

      import java.sql as sql

      import java.sql.DriverManager.getConnection

      import com.hyperion.aif.scripting.API as API

       

      'Script runs only if the rule name is DLR_PBCS'

      if fdmContext["RULENAME"] == "DLR_PBCS":

          fdmAPI.logInfo("Executing event script for Creation of View")

          fdmAPI.logInfo(fdmContext["PERIODNAME"])

          CreateView = "CREATE OR REPLACE VIEW EA_HFM_V AS select PARTITIONKEY,DIMNAME,TARGKEY,DATAKEY from TEMPTABLE"

          fdmAPI.logInfo(CreateView)

          fdmAPI.executeQuery(CreateView)

       

      Please find the code above. Not sure if an doing it in Right way. Request your kind help in this.

       

      Regards,

      Roshin

        • 1. Re: Creating a View using Before Import Script
          SH_INT

          fdmAPI.executeQuery is for returning data from a SELECT satatement it will not perform DML operations. The command you will need is fdmAPI.executeDML. However, that said I'm not sure why you are crating a view in the event script, why not just run the SELECT statement against the original table. Also you do not want to be creating a view on every executiontion of your data rule

          • 2. Re: Creating a View using Before Import Script
            ThinkFDM

            My Suggestion:

            • Work with your DBA to create the view on the data.
            • Use open interface adapter to temporarily stage the data from your view.
            • Use Jython code in the BefImport event script to populate the open interface table and eventually pull data into data load workbench.

             

            Note: If you're on FDMEE 11.1.2.4.100, you can pull data directly from the view using the universal data adapter instead of using the open interface adapter.

             

            FDMEE Admin Guide 11.1.2.4.200 has info on both the open interface adapter and the universal data adapter.

             

            Good luck.

            • 3. Re: Creating a View using Before Import Script
              2837320

              Thank you it helped me . I will use open interface table.

               

              Thanks SH_INT for your help

               

              Regards,

              Roshin