0 Replies Latest reply: Feb 28, 2014 12:38 AM by Raunaq RSS

    Display LKM options

    Raunaq

      Guys ,

      I am on ODI 11g

      I am using the below query to display LKM options defined for each interface

       

      CREATE OR REPLACE

      VIEW ODI_INTERFACE_LKM_OPTIONS_V (

        PROJECT_NAME,

        INTERFACE_NAME,

        LKM_NAME,

        DELETE_TEMPORARY_OBJECTS,

        WORK_TABLE_OPTIONS,

        LOA_DIRECT,

        LOA_DISCARDMAX,

        LOA_ERRORS,

        COMPATIBLE,

        "VALIDATE",

        NLS_DATE_LANGUAGE,

        NLS_NUMERIC_CHARACTERS,

        FEED_FILE_NAME,

        INSERT_MODE,

        RECNUM,

        RECORD_TYPE_INDICATOR_COLUMN,

        READSIZE_LIMIT,

        GATHER_STALE_STATS_ONLY,

        LGL_PARTITION

      ) AS

      SELECT PROJECT_NAME,

                  interface_name,

                  LKM_NAME,

                  CASE DELETE_TEMPORARY_OBJECTS WHEN '0' THEN 'false' ELSE 'true' END

                     AS "DELETE_TEMPORARY_OBJECTS",

                  WORK_TABLE_OPTIONS,

                  LOA_DIRECT,

                  LOA_DISCARDMAX,

                  LOA_ERRORS,

                  COMPATIBLE,

                  CASE "VALIDATE" WHEN '0' THEN 'false' ELSE 'true' END AS "VALIDATE",

                  nls_date_language,

                  NLS_NUMERIC_CHARACTERS,

                  FEED_FILE_NAME,

                  INSERT_MODE,

                  RECNUM,

                  RECORD_TYPE_INDICATOR_COLUMN,

                  READSIZE_LIMIT,

                  CASE GATHER_STALE_STATS_ONLY WHEN '0' THEN 'false' ELSE 'true' END

                     AS "GATHER_STALE_STATS_ONLY",

                  NVL (LGL_PARTITION, 'NONE') AS "LGL_PARTITION"

             FROM (SELECT SP.PROJECT_NAME,

                          POP.POP_NAME INTERFACE_NAME,

                          TRT.TRT_NAME LKM_NAME,

                          SUE.UE_NAME km_option,

                          NVL (USED.SHORT_VALUE, SUE.SHORT_VALUE) option_value

                     FROM SNP_SRC_SET SSS

                          INNER JOIN SNP_DATA_SET SDS

                             ON (SDS.I_DATA_SET = SSS.I_DATA_SET)

                          INNER JOIN SNP_POP POP

                             ON (POP.I_POP = SDS.I_POP)

                          INNER JOIN SNP_TRT TRT

                             ON (TRT.I_TRT = SSS.I_TRT_KLM)

                          INNER JOIN SNP_USER_EXIT SUE

                             ON (TRT.I_TRT = SUE.I_TRT)

                          LEFT OUTER JOIN SNP_UE_USED USED

                             ON (USED.I_USER_EXIT = SUE.I_USER_EXIT

                                 AND USED.I_UE_ORIG = 104)

                          INNER JOIN SNP_PROJECT SP

                             ON (SP.I_PROJECT = TRT.I_PROJECT)) PIVOT (MAX (

                                                                          option_value)

                                                                FOR km_option

                                                                IN  ('DELETE_TEMPORARY_OBJECTS' AS "DELETE_TEMPORARY_OBJECTS",

                                                                    'WORK_TABLE_OPTIONS' AS "WORK_TABLE_OPTIONS",

                                                                    'LOA_DIRECT' AS "LOA_DIRECT",

                                                                    'LOA_DISCARDMAX' AS "LOA_DISCARDMAX",

                                                                    'LOA_ERRORS' AS "LOA_ERRORS",

                                                                    'COMPATIBLE' AS "COMPATIBLE",

                                                                    'VALIDATE' AS "VALIDATE",

                                                                    'NLS_DATE_LANGUAGE' AS "NLS_DATE_LANGUAGE",

                                                                    'NLS_NUMERIC_CHARACTERS' AS "NLS_NUMERIC_CHARACTERS",

                                                                    'FEED_FILE_NAME' AS "FEED_FILE_NAME",

                                                                    'INSERT_MODE' AS "INSERT_MODE",

                                                                    'RECNUM' AS "RECNUM",

                                                                    'RECORD_TYPE_INDICATOR_COLUMN' AS "RECORD_TYPE_INDICATOR_COLUMN",

                                                                    'READSIZE_LIMIT' AS "READSIZE_LIMIT",

                                                                    'GATHER_STALE_STATS_ONLY' AS "GATHER_STALE_STATS_ONLY",

                                                                    'LGL_PARTITION' AS "LGL_PARTITION"))

         ORDER BY interface_name

      /

       

       

      The issue i am facing is that if there is a folder in a project and it has 2 interfaces with same LKM selected , then it displays the same LKM options values

      for different interfaces , even if they have different options defined.

       

      Whats the mistake in this query ?

       

      I am not able to track LKM options?

       

      Help needed