6 Replies Latest reply: May 24, 2012 9:34 AM by Bhabani Ranjan RSS

    Column Header for Select AS kind of query

    Bijal
      Hi,
      I wish to write a csv file by using the odisqlunload function. I have wrote the funtion as :
      OdiSqlUnload "-FILE=#FILE_PATH/#FILE_NAME" "-DRIVER=<%=odiRef.getInfo("SRC_JAVA_DRIVER")%>" "-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>" "-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>" "-PASS=<%=odiRef.getInfo("SRC_ENCODED_PASS")%>" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP=," "-ROW_SEP=\r\n" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1" "-FETCH_SIZE=1000" "-QUERY=SELECT RPAD(CI_FT_GL.GL_ACCT, 31, ' '),LPAD(CASE WHEN CI_FT.CURRENCY_CD != CI_FT_GL_EXT.DIVISION_CURCY_CD THEN sum(CI_FT_GL_EXT.DIVISION_AMT) ELSE sum(CI_FT_GL.AMOUNT) END,17,' '),' ',RPAD(CI_DST_CD_CHAR.CHAR_VAL, 25, ' '),RPAD(CI_DST_CD_CHAR.DST_ID, 8, ' '),'RMB ',' ',RPAD(to_char(sysdate, 'YYYYMMDD'), 8, ' ') FROM CI_DST_CD_CHAR CI_DST_CD_CHAR,CI_FT CI_FT,CI_FT_GL CI_FT_GL,CI_FT_GL_EXT CI_FT_GL_EXT where (CI_FT.FT_ID = CI_FT_GL.FT_ID) AND (CI_FT_GL.FT_ID = CI_FT_GL_EXT.FT_ID(+)) AND CI_FT_GL.GL_SEQ_NBR = CI_FT_GL_EXT.GL_SEQ_NBR(+) AND (CI_FT_GL.DST_ID = CI_DST_CD_CHAR.DST_ID) AND (CI_DST_CD_CHAR.CHAR_TYPE_CD = 'DTLREF1') AND (trim(CI_FT_GL.GL_ACCT) is not null) AND (CI_FT.XFER_TO_GL_DT is null) AND (CI_FT.GL_DISTRIB_STATUS in ('G','M')) AND (trunc(CI_FT.SCHED_DISTRIB_DT) <= trunc(sysdate)) AND CI_FT.CIS_DIVISION = '#DIVISION' GROUP BY CI_FT_GL.GL_ACCT,CI_DST_CD_CHAR.CHAR_VAL,CI_DST_CD_CHAR.DST_ID,CI_FT.CURRENCY_CD,CI_FT_GL_EXT.DIVISION_CURCY_CD"

      Then i used a jython code to generate headers. Bcoz the OdiSqlUnload does not generate headers. But the jython code is not useful when we have sql statements lik 'Select AS' :
      SELECT RPAD(CI_FT_GL.GL_ACCT, 31, ' ') AS GL_ACCT,
      LPAD(CASE WHEN CI_FT.CURRENCY_CD != CI_FT_GL_EXT.DIVISION_CURCY_CD THEN sum(CI_FT_GL_EXT.DIVISION_AMT) ELSE sum(CI_FT_GL.AMOUNT) END,17,' ') AS GL_AMOUNT,

      My Jython code is:


      import string
      import java.sql as sql
      import java.lang as lang
      import re
      sourceConnection = odiRef.getJDBCConnection("SRC")
      output_write=open('d:/Bijal/output1.csv','r+')
      myStmt = sourceConnection.createStatement()
      my_query = "SELECT RPAD(CI_FT_GL.GL_ACCT, 31, ' ') AS GL_ACCT,
      LPAD(CASE WHEN CI_FT.CURRENCY_CD != CI_FT_GL_EXT.DIVISION_CURCY_CD THEN sum(CI_FT_GL_EXT.DIVISION_AMT) ELSE sum(CI_FT_GL.AMOUNT) END,17,' ') AS GL_AMOUNT,
      ' ' AS BLANK1,
      RPAD(CI_DST_CD_CHAR.CHAR_VAL, 25, ' ')AS DESCR,
      RPAD(CI_DST_CD_CHAR.DST_ID, 8, ' ')AS DST_ID,
      'RMB ' Currency_Cd,
      ' ' AS BLANK2,
      RPAD(to_char(sysdate, 'YYYYMMDD'), 8, ' ') AS DT_OF_TXN
      FROM CI_DST_CD_CHAR CI_DST_CD_CHAR,CI_FT CI_FT,CI_FT_GL CI_FT_GL,CI_FT_GL_EXT CI_FT_GL_EXT
      where (CI_FT.FT_ID = CI_FT_GL.FT_ID) AND (CI_FT_GL.FT_ID = CI_FT_GL_EXT.FT_ID(+)) AND
      CI_FT_GL.GL_SEQ_NBR = CI_FT_GL_EXT.GL_SEQ_NBR(+) AND (CI_FT_GL.DST_ID = CI_DST_CD_CHAR.DST_ID)
      AND (CI_DST_CD_CHAR.CHAR_TYPE_CD = 'DTLREF1') AND (trim(CI_FT_GL.GL_ACCT) is not null)
      AND (CI_FT.XFER_TO_GL_DT is null) AND (CI_FT.GL_DISTRIB_STATUS in ('G','M')) AND
      (trunc(CI_FT.SCHED_DISTRIB_DT) <= trunc(sysdate)) AND CI_FT.CIS_DIVISION = 'MF'
      GROUP BY CI_FT_GL.GL_ACCT,
      CI_DST_CD_CHAR.CHAR_VAL,
      CI_DST_CD_CHAR.DST_ID,
      CI_FT.CURRENCY_CD,
      CI_FT_GL_EXT.DIVISION_CURCY_CD"
      my_query=my_query.upper()
      if string.find(my_query, '*') > 0:
      myRs = myStmt.executeQuery(my_query)
      md=myRs.getMetaData()
      collect=[]
      i=1
      while (i <= md.getColumnCount()):
      collect.append(md.getColumnName(i))
      i += 1
           
      header=','.join(map(string.strip, collect))
      elif string.find(my_query,'||') > 0:
      header = my_query[7:string.find(my_query, 'FROM')].replace("||','||",',')
      else:
      header = my_query[7:string.find(my_query, 'FROM')]
           
      print header
      old=output_write.read()
      output_write.seek(0)
      output_write.write (header+'\n'+old)
           
      sourceConnection.close()
      output_write.close()



      But it does not populate for Select As statements and throws error lik:

      'mismatched character \'\\n\' expecting \'"\'', ('<string>', 8, 62, 'my_query = "SELECT RPAD(CI_FT_GL.GL_ACCT, 31, \' \') AS GL_ACCT,\n'))



      Plz help me get headers in my output file :)
        • 1. Re: Column Header for Select AS kind of query
          Sutirtha Roy
          Hi,

          Is it mandatory to use OdiSqlUnlod ?
          If you are ok with ODI interface then you can generate header as well as data using single interface .

          Thanks,
          Sutirtha
          • 2. Re: Column Header for Select AS kind of query
            Bijal
            I am fine with an interface. But have to present the ouput csv file to the client. During execution we need to give them the choice of output location and the filename
            • 3. Re: Column Header for Select AS kind of query
              Sutirtha Roy
              No issue .

              Create a package with the following
              create a variable which holds the filename.
              create another variable which holds the filepath.
              Create an interface which will generate a csv file with header plus data . The file name will be fixed here.
              Then use OdiFileMove , use the varirable create earlier for the path and name of the file .

              Thanks,
              Sutirtha
              • 4. Re: Column Header for Select AS kind of query
                Bhabani Ranjan
                Solution1:
                Still you can achieve this by using sys_connect_by_path ( if you know the use of it) to generate header. below link can help with another approach.
                http://www.business-intelligence-quotient.com/?p=546

                Solution2:
                You need to create one file (header= true )with interface as suggested by sutirtha. Then you can copy (odifilecopy) this file to another directory with dynamic name (inside variable) and dynamic path. In this interface keep the truncate option to true so that everytime data wont be appended to the end of file. After reversing the file change the column name Manually from C1 to your ACTUAL_COL_NAME in file. Because ODI always generate the column names using C1,C2,C3 ....

                The only risk in second approach is that if someone will delete the file then you will get error. For this you need to create a temp table in ODI with all column-name (header in file). then using above jython code or sys_connect_by_path (select * from temp_table), you can generate the header easily. Now this file will be generated dynamically and can be used by the interface. So even if someone will delete the file you can generate it during run time.

                Thanks.
                • 5. Re: Column Header for Select AS kind of query
                  actdi
                  Why are you going via such a complex route ?

                  You should simply use a UNION ALL clause in your sql query to odiUnload.

                  QUERY="select 'COL_NAME1','COL_NAME2', 'COL_NAME3' from dual
                  UNION ALL
                  select COL_VAL1, COL_VAL2, COL_VAL3 from myComplexQuery"
                  • 6. Re: Column Header for Select AS kind of query
                    Bhabani Ranjan
                    Hi ACTDI ,that would be nice instead of making a second step.
                    But at the end we are hardcording the column name like i said in my second approach.
                    If we are not interested in hardcoding the column name then we should go for Jython as suggested by Kshitiz or SYS_CONNECt_BY_PATH approach by Craig Stewart.

                    Thanks.