1 Reply Latest reply on Feb 11, 2016 4:54 PM by WVanSluys1

    Jython : SQL query output to file

    user1659263

      Dears,

       

      Could you provide me guidance on how to extract FDMEE table data to a file? I would like be to trigger the select query from Jython script then write the output to a file on a specific directory on FDMEE server (problem is that we have 3 servers...).

       

      Any sample, link or guidance appreciated. Many thanks.

       

      I prepared this select already :

       

      import java.sql as sql

      sourceConn = sql.DriverManager.getConnection("jdbcUrl", "user", "password");
      selectStmt = "SELECT DISTINCT
             EXTRACT (YEAR FROM TDATASEG.PERIODKEY) YEAR,
             CONCAT('M', EXTRACT (MONTH FROM TDATASEG.PERIODKEY)) PERIOD_QUALIAC,
             CONCAT (
                'M',
                CASE
                   WHEN LENGTH (TO_CHAR (EXTRACT (MONTH FROM TDATASEG.PERIODKEY))) =
      1
                   THEN
                      CONCAT ('0',
                              TO_CHAR (EXTRACT (MONTH FROM TDATASEG.PERIODKEY)))
                   ELSE
                      TO_CHAR (EXTRACT (MONTH FROM TDATASEG.PERIODKEY))
                END)
                CO_CVG_PERIOD,
             CONCAT ('N', SUBSTR (TDATASEG.ACCOUNT, -9, 9)) CODE_NATURE_QUALIAC,
             TDATASEG.ACCOUNTX CO_CVG_ACCOUNT,
             CASE
                WHEN TDATASEG.UD4X = '[None]' THEN 'None'
                ELSE CONCAT ('F', TDATASEG.UD4)
             END
                CODE_FONCTION_QUALIAC,
             TDATASEG.UD4X CO_CVG_FUNCTION,
             REPLACE (TDATASEG.entity, 'L', 'S') CODE_ENTITY_QUALIAC,
             TDATASEG.ENTITYX CO_CVG_ENTITY,
             TDATASEG.ud3x CO_CVG_ACTIVITY,
             CONCAT ('T', TDATASEG.UD7) CODE_TERRITOIRE_QUALIAC,
             TDATASEG.UD7X CO_CVG_TERRITORY,
             TDATASEG.ud5 CODE_PRODUIT_QUALIAC,
             TDATASEG.ud5x CO_CVG_PRODUCT,
             TDATASEG.ud2x CO_CVG_BU
        FROM FDMDBO.TDATASEG
      WHERE     (LENGTH (SUBSTR (TDATASEG.ACCOUNT, -9, 9)) = 9)
             AND (TDATASEG.ACCOUNTX IS NOT NULL)
             AND (TDATASEG.UD4 <> 'None')
             AND (TDATASEG.partitionkey = (SELECT partitionkey
                                             FROM FDMDBO.tpovpartition
                                            WHERE partname = 'GL_FRA_ANA'))
      "
      stmt = sourceConn.prepareStatement(selectStmt)
      stmtRS = stmt.executeQuery()
      while(stmtRS.next()):
        params = [  ]
        fdmAPI.executeDML(insertStmt, params, False)
      fdmAPI.commitTransaction()

      stmtRS.close()
      stmt.close()
      sourceConn.close()