27 Replies Latest reply: Sep 19, 2012 12:27 PM by Purna Cheekati RSS

    how Link & Concatenate tables using oracle data source

      i'm trying to link and concatenate different tables to create master calender for oracle data source and end up with "Table Not Found" error from LinkTable_Temp (show bellow).

       

       

       

       

      LinkTable_Temp:

      //LOAD

      SQL

          SELECT  *,

      DateTIme AS DATE,

      'GATEWAYDATE'    AS DateType

      FROM

          "SAEGW"."GTP_STATS"

      WHERE

          DATETIME >= SYSDATE - 7 AND SUBSTR(PDNGW,11,3) in ('P01', 'P02', 'P03');

         

      PCRF_STATS:                                   ---- TABLE NOT FOUND ERROR

      Concatenate(LinkTable_Temp)

      SQL

          SELECT

          *,

          DATETIME        AS DATE,

          'PCRFSTATSDATE'    AS DateType

      FROM

          "CRF"."ERTICAL_15M"

      WHERE

          DATETIME >= SYSDATE - 7    AND CATEGORY = 'PC'

      GROUP BY

          PCRF, DATETIME,  "COMPONENT_NAME",    "PROCESS_NAME"

      ;

       

      DB_SESSION_COUNT:  ---- TABLE NOT FOUND ERROR

      Concatenate(LinkTable_Temp)

      SQL

           SELECT

           *,

           DATETIME    AS DATE,

           'DBDATE'    AS DateType

      FROM "CRF"."ALLSESSIONCOUNT_DY" A, "CRF"."STALESESSIONCOUNT_DY" B

      WHERE TO_CHAR(B.DATETIMEZONE, 'MM/DD/YYYY HH24:MI') = TO_CHAR(A.DATETIMEZONE, 'MM/DD/YYYY HH24:MI')

      AND A."EMS_NAME" IN ('10_36_66_136', '10_37_19_136', '10_37_166_136','107_246_96_136','155_174_220_136','10_40_16_136','107_246_112_136')

      AND A.DATETIME >= SYSDATE - 7;

       

       

      LinkTable:

      LOAD

          DATE,

          DateType,

          PDNGW &'-'& DATETIME                            AS "KEY_PDP_COUNT",

          PCRF &'-'& DATETIME                                AS "KEY_PCRF_STATS",

          "CRF"."ALLSESSIONCOUNT_DY"."EMS_NAME" &'-'& "CRF"."ALLSESSIONCOUNT_DY".DATETIME                    AS "KEY_DB_SESSION_COUNT"

      RESIDENT LinkTable_Temp;

       

      DROP Table LinkTable_Temp;

        • Re: how Link & Concatenate tables using oracle data source
          Alex Pan

          do you want to create a table PCRF_STATS? if so, use NOCANCACNATE before the table name, otherwise, you do not need that name.

            • Re: how Link & Concatenate tables using oracle data source
              Michael Solomovich

              Same regarding the DB_SESSION_COUNT.

              • Re: how Link & Concatenate tables using oracle data source

                Yes, i'm creating these tables above the LinkTable_Temp statement and concatenate in LinkTable_Temp. I tried with NoConcatenate before the table name ie. PCRF_STATS it didnot work. Getting same "No Table Found" error

                  • Re: how Link & Concatenate tables using oracle data source
                    mayilvahanan ramasamy

                    Hi

                     

                         Can you provide the loaded tables name alone in which order you loaded in script or provide the script alone?

                      • Re: how Link & Concatenate tables using oracle data source

                        Load Script:

                         

                         

                         

                        OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=TESTk;Data Source="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = wnsnet.com)(PORT = 1001))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = REPORT)))";Extended Properties=""] (XPassword is FLfCFTRGELN);

                         

                        // User Defined

                         

                         

                        // PDP COUNT

                        PDP_COUNT:

                        SQL SELECT

                            PDNGW                                            AS GATEWAY,

                        //    TO_CHAR("DATETIME_INS", 'MM/DD/YYYY HH24:MI')    AS DATETIME,

                            DATETIME                                        AS "PDP_DATETIME",

                            PDNGW||'-'||DATETIME                            AS "KEY_PDP_COUNT",

                            CAST(PERIOD AS INT)                              AS "AT_PERIOD",

                            ACTIVEGTPVERSION0PDPS                            AS ACTIVEGTPVERSION0PDPS,

                            ACTIVEGTPVERSION1PDPS                            AS ACTIVEGTPVERSION1PDPS,

                            ACTIVEGTPVERSION0PDPS + ACTIVEGTPVERSION1PDPS    AS "PGW_3G_CONTEXTS",

                            ACTIVEBEARERS                                    AS ACTIVEBEARERS,

                            CAST(ACTIVEGTPVERSION0PDPS+ACTIVEGTPVERSION1PDPS+ACTIVEBEARERS*(2.10526) AS DECIMAL (16,2))                    AS "PGW_TOTAL_SESSIONS"

                        FROM

                            "SAEGW"."GTP_STATS"

                        WHERE

                            DATETIME >= SYSDATE - 3 AND SUBSTR(PDNGW,11,3) in ('P01', 'P02', 'P03');

                         

                         

                        PCRF_STATS:

                        SQL SELECT

                            PCRF                            AS ELEMENTNAME,

                            DATETIME                        AS MEASDATE,

                            PCRF||'-'||DATETIME                AS "KEY_PCRF_STATS",

                            "COMPONENT_NAME"                    AS "AT_COMPONENT_NAME",

                            "PROCESS_NAME"                    AS "AT_PROCESS_NAME",

                            MAX(SUBSTR((DATETIMEZONE - DATETIMEUTC),1,1)  ||

                            SUBSTR((DATETIMEZONE -  DATETIMEUTC),12,5))    AS "AT_TZ",  

                            MAX(PERIOD)                        AS "AT_PERIOD",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'CISCOGXR5.0V8_SERVER_CCRCOUNT'

                                  THEN "NUMERIC_VALUE" ELSE 0 END) AS "CISCOGXR5_0V8_SRV_CCR",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_SPR_LDAP_SEARCH'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "SPR_LDAP_SRCH",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'CISCOGXR5.0V8_SERVER_RARCOUNT'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "CISCOGXR5_0V8_SRV_RAR",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_SY_AA-ANSWER'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "SY_AA_ANSWER",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_SY_SESSION-TERMINATION-ANSWER'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "SY_SES_TERM_ANSWER",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_SY_RE-AUTH-REQUEST'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "SY_RE_AUTH_REQ",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_FAILED_RAA'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS  "FAIL_RAA",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SY_AA-ANSWER'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS  "FAIL_SY_AA_ANSWER",      

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SY_RE-AUTH-REQUEST'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS  "FAIL_SY_RE_AUTH_REQ",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SPR_LDAP_SEARCH'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "FAIL_SPR_LDAP_SRCH",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_SPR_LDAP_SEARCH_PLUGIN'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "SPR_LDAP_SRCH_PLGIN",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'CISCOGXR5.0V8_SERVER_CCRERRORCOUNT'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "CISCOGXR5_0V8_SRV_CCRERR",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SY_SESSION-TERMINATION-ANSWER'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "FAIL_SY_SES_TERM_ANSWER",

                            SUM(CASE

                              WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SPR_LDAP_BIND'

                              THEN "NUMERIC_VALUE" ELSE 0 END) AS "FAIL_SPR_LDAP_BIND",

                            SYSDATE AS UPDATED

                        FROM

                            "CRF"."ERTICAL_15M"

                        WHERE

                            DATETIME >= SYSDATE - 3    AND CATEGORY = 'PC'

                        GROUP BY

                            PCRF, DATETIME,  "COMPONENT_NAME",    "PROCESS_NAME"

                        ;

                         

                         

                         

                        DB_SESSION_COUNT:

                        SQL

                        SELECT     A."EMS_NAME" ,

                                A."DATA_CENTER",

                                A.PERIOD,

                                A."COMPLETE_SESSION_COUNT" AS "ALL_DB_SESSIONS",

                                B."COMPLETE_SESSION_COUNT" AS "STALE_DB_SESSIONS",

                                (A."COMPLETE_SESSION_COUNT" - B."COMPLETE_SESSION_COUNT") AS "ACTIVE_SESSIONS",

                                //TO_CHAR(A.DATETIMEZONE, 'MM/DD/YYYY HH24:MI') AS "DATE_TIME"

                                A.DATETIME    AS "DB_SESSION_DT",

                                A."EMS_NAME"||'-'||A.DATETIME    AS "KEY_DB_SESSION_COUNT"

                        FROM "CRF"."ALLSESSIONCOUNT_DY" A, "CRF"."STALESESSIONCOUNT_DY" B

                        WHERE TO_CHAR(B.DATETIMEZONE, 'MM/DD/YYYY HH24:MI') = TO_CHAR(A.DATETIMEZONE, 'MM/DD/YYYY HH24:MI')

                        AND A."EMS_NAME" IN ('10_36_66_136', '10_37_19_136', '10_37_166_136','107_246_96_136','155_174_220_136','10_40_16_136','107_246_112_136')

                        AND A.DATETIME >= SYSDATE - 3;

                         

                        // Link Table

                        LinkTable_Temp:

                        //LOAD

                        SQL

                            SELECT

                            *,

                            DATETIME        AS DATE,

                            'GATEWAYDATE'    AS DateType

                        FROM

                            "SAEGW"."GTP_STATS"

                        WHERE

                            DATETIME >= SYSDATE - 7 AND SUBSTR(PDNGW,11,3) in ('P01', 'P02', 'P03');

                           

                        // PCRF_STATS:

                        Concatenate(LinkTable_Temp)

                        SQL

                            SELECT

                            *,

                            DATETIME        AS DATE,

                            'PCRFSTATSDATE'    AS DateType

                        FROM

                            "CRF"."ERTICAL_15M"

                        WHERE

                            DATETIME >= SYSDATE - 7    AND CATEGORY = 'PC'

                        GROUP BY

                            PCRF, DATETIME,  "COMPONENT_NAME",    "PROCESS_NAME"

                        ;

                         

                        // DB_SESSION_COUNT:

                        Concatenate(LinkTable_Temp)

                        SQL

                             SELECT

                             *,

                             DATETIME    AS DATE,

                             'DBDATE'    AS DateType

                        FROM "CRF"."ALLSESSIONCOUNT_DY" A, "CRF"."STALESESSIONCOUNT_DY" B

                        WHERE TO_CHAR(B.DATETIMEZONE, 'MM/DD/YYYY HH24:MI') = TO_CHAR(A.DATETIMEZONE, 'MM/DD/YYYY HH24:MI')

                        AND A."EMS_NAME" IN ('10_36_66_136', '10_37_19_136', '10_37_166_136','107_246_96_136','155_174_220_136','10_40_16_136','107_246_112_136')

                        AND A.DATETIME >= SYSDATE - 7;

                         

                         

                        LinkTable:

                        LOAD

                            DATE,

                            DateType,

                            PDNGW &'-'& DATETIME                            AS "KEY_PDP_COUNT",

                            PCRF &'-'& DATETIME                                AS "KEY_PCRF_STATS",

                            "CRF"."ALLSESSIONCOUNT_DY"."EMS_NAME" &'-'& "CRF"."ALLSESSIONCOUNT_DY".DATETIME                    AS "KEY_DB_SESSION_COUNT"

                        RESIDENT LinkTable_Temp;

                         

                        DROP Table LinkTable_Temp;

                         

                               

                        /* PCEF, P-GW, S-GW */

                        PCEF_GW:

                        SQL

                        SELECT    "CHASSIS_NODE_NAME" ,

                                "SLOT_NODE_NAME",

                                "CHASSIS_TYPE",

                                "CHASSIS_NODE_TYPE",

                                SLOT,

                                "SLOT_NODE_TYPE",

                                SYSNAME

                        FROM

                            (SELECT "CHASSIS_NODE_NAME" ,

                                    "SLOT_NODE_NAME",

                                    "CHASSIS_TYPE",

                                    "CHASSIS_NODE_TYPE",

                                    SLOT,

                                    "SLOT_NODE_TYPE",

                                    SYSNAME,

                                    RANK() OVER (PARTITION BY SYSNAME ORDER BY "CHASSIS_NODE_NAME" ,"SLOT_NODE_NAME", "CHASSIS_TYPE","CHASSIS_NODE_TYPE", SLOT,"SLOT_NODE_TYPE") RNK

                            FROM "SAEGW"."CHASIS_INVENTORY"

                            WHERE SYSNAME IN (SELECT DISTINCT(PDNGW) FROM "SAEGW"."GTP_STATS" WHERE SUBSTR(PDNGW,11,3) IN ('P01', 'P02', 'P03'))) A

                        WHERE RNK = 1;

                        • Re: how Link & Concatenate tables using oracle data source

                          Load Script:

                           

                           

                           

                          OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=TESTk;Data Source="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = wnsnet.com)(PORT = 1001))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = REPORT)))";Extended Properties=""] (XPassword is FLfCFTRGELN);

                           

                          // User Defined

                           

                           

                          // PDP COUNT

                          PDP_COUNT:

                          SQL SELECT

                              PDNGW                                            AS GATEWAY,

                          //    TO_CHAR("DATETIME_INS", 'MM/DD/YYYY HH24:MI')    AS DATETIME,

                              DATETIME                                        AS "PDP_DATETIME",

                              PDNGW||'-'||DATETIME                            AS "KEY_PDP_COUNT",

                              CAST(PERIOD AS INT)                              AS "AT_PERIOD",

                              ACTIVEGTPVERSION0PDPS                            AS ACTIVEGTPVERSION0PDPS,

                              ACTIVEGTPVERSION1PDPS                            AS ACTIVEGTPVERSION1PDPS,

                              ACTIVEGTPVERSION0PDPS + ACTIVEGTPVERSION1PDPS    AS "PGW_3G_CONTEXTS",

                              ACTIVEBEARERS                                    AS ACTIVEBEARERS,

                              CAST(ACTIVEGTPVERSION0PDPS+ACTIVEGTPVERSION1PDPS+ACTIVEBEARERS*(2.10526) AS DECIMAL (16,2))                    AS "PGW_TOTAL_SESSIONS"

                          FROM

                              "SAEGW"."GTP_STATS"

                          WHERE

                              DATETIME >= SYSDATE - 3 AND SUBSTR(PDNGW,11,3) in ('P01', 'P02', 'P03');

                           

                           

                          PCRF_STATS:

                          SQL SELECT

                              PCRF                            AS ELEMENTNAME,

                              DATETIME                        AS MEASDATE,

                              PCRF||'-'||DATETIME                AS "KEY_PCRF_STATS",

                              "COMPONENT_NAME"                    AS "AT_COMPONENT_NAME",

                              "PROCESS_NAME"                    AS "AT_PROCESS_NAME",

                              MAX(SUBSTR((DATETIMEZONE - DATETIMEUTC),1,1)  ||

                              SUBSTR((DATETIMEZONE -  DATETIMEUTC),12,5))    AS "AT_TZ",  

                              MAX(PERIOD)                        AS "AT_PERIOD",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'CISCOGXR5.0V8_SERVER_CCRCOUNT'

                                    THEN "NUMERIC_VALUE" ELSE 0 END) AS "CISCOGXR5_0V8_SRV_CCR",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_SPR_LDAP_SEARCH'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "SPR_LDAP_SRCH",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'CISCOGXR5.0V8_SERVER_RARCOUNT'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "CISCOGXR5_0V8_SRV_RAR",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_SY_AA-ANSWER'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "SY_AA_ANSWER",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_SY_SESSION-TERMINATION-ANSWER'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "SY_SES_TERM_ANSWER",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_SY_RE-AUTH-REQUEST'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "SY_RE_AUTH_REQ",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_FAILED_RAA'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS  "FAIL_RAA",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SY_AA-ANSWER'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS  "FAIL_SY_AA_ANSWER",      

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SY_RE-AUTH-REQUEST'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS  "FAIL_SY_RE_AUTH_REQ",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SPR_LDAP_SEARCH'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "FAIL_SPR_LDAP_SRCH",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_SPR_LDAP_SEARCH_PLUGIN'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "SPR_LDAP_SRCH_PLGIN",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'CISCOGXR5.0V8_SERVER_CCRERRORCOUNT'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "CISCOGXR5_0V8_SRV_CCRERR",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SY_SESSION-TERMINATION-ANSWER'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "FAIL_SY_SES_TERM_ANSWER",

                              SUM(CASE

                                WHEN "STATISTIC_NAME" = 'COUNT_FAILED_SPR_LDAP_BIND'

                                THEN "NUMERIC_VALUE" ELSE 0 END) AS "FAIL_SPR_LDAP_BIND",

                              SYSDATE AS UPDATED

                          FROM

                              "CRF"."ERTICAL_15M"

                          WHERE

                              DATETIME >= SYSDATE - 3    AND CATEGORY = 'PC'

                          GROUP BY

                              PCRF, DATETIME,  "COMPONENT_NAME",    "PROCESS_NAME"

                          ;

                           

                           

                           

                          DB_SESSION_COUNT:

                          SQL

                          SELECT     A."EMS_NAME" ,

                                  A."DATA_CENTER",

                                  A.PERIOD,

                                  A."COMPLETE_SESSION_COUNT" AS "ALL_DB_SESSIONS",

                                  B."COMPLETE_SESSION_COUNT" AS "STALE_DB_SESSIONS",

                                  (A."COMPLETE_SESSION_COUNT" - B."COMPLETE_SESSION_COUNT") AS "ACTIVE_SESSIONS",

                                  //TO_CHAR(A.DATETIMEZONE, 'MM/DD/YYYY HH24:MI') AS "DATE_TIME"

                                  A.DATETIME    AS "DB_SESSION_DT",

                                  A."EMS_NAME"||'-'||A.DATETIME    AS "KEY_DB_SESSION_COUNT"

                          FROM "CRF"."ALLSESSIONCOUNT_DY" A, "CRF"."STALESESSIONCOUNT_DY" B

                          WHERE TO_CHAR(B.DATETIMEZONE, 'MM/DD/YYYY HH24:MI') = TO_CHAR(A.DATETIMEZONE, 'MM/DD/YYYY HH24:MI')

                          AND A."EMS_NAME" IN ('10_36_66_136', '10_37_19_136', '10_37_166_136','107_246_96_136','155_174_220_136','10_40_16_136','107_246_112_136')

                          AND A.DATETIME >= SYSDATE - 3;

                           

                          // Link Table

                          LinkTable_Temp:

                          SQL

                              SELECT

                              *,

                              DATETIME        AS DATE,

                              'GATEWAYDATE'    AS DateType

                          FROM

                              "SAEGW"."GTP_STATS"

                          WHERE

                              DATETIME >= SYSDATE - 7 AND SUBSTR(PDNGW,11,3) in ('P01', 'P02', 'P03');

                             

                          //PCRF_STATS:

                          Concatenate(LinkTable_Temp)

                          SQL

                              SELECT

                              *,

                              DATETIME        AS DATE,

                              'PCRFSTATSDATE'    AS DateType

                          FROM

                              "CRF"."ERTICAL_15M"

                          WHERE

                              DATETIME >= SYSDATE - 7    AND CATEGORY = 'PC'

                          GROUP BY

                              PCRF, DATETIME,  "COMPONENT_NAME",    "PROCESS_NAME"

                          ;

                           

                          //DB_SESSION_COUNT:

                          Concatenate(LinkTable_Temp)

                          SQL

                               SELECT

                               *,

                               DATETIME    AS DATE,

                               'DBDATE'    AS DateType

                          FROM "CRF"."ALLSESSIONCOUNT_DY" A, "CRF"."STALESESSIONCOUNT_DY" B

                          WHERE TO_CHAR(B.DATETIMEZONE, 'MM/DD/YYYY HH24:MI') = TO_CHAR(A.DATETIMEZONE, 'MM/DD/YYYY HH24:MI')

                          AND A."EMS_NAME" IN ('10_36_66_136', '10_37_19_136', '10_37_166_136','107_246_96_136','155_174_220_136','10_40_16_136','107_246_112_136')

                          AND A.DATETIME >= SYSDATE - 7;

                           

                           

                          LinkTable:

                          LOAD

                              DATE,

                              DateType,

                              PDNGW &'-'& DATETIME                            AS "KEY_PDP_COUNT",

                              PCRF &'-'& DATETIME                                AS "KEY_PCRF_STATS",

                              "CRF"."ALLSESSIONCOUNT_DY"."EMS_NAME" &'-'& "CRF"."ALLSESSIONCOUNT_DY".DATETIME                    AS "KEY_DB_SESSION_COUNT"

                          RESIDENT LinkTable_Temp;

                           

                          DROP Table LinkTable_Temp;

                           

                                 

                          /* PCEF, P-GW, S-GW */

                          PCEF_GW:

                          SQL

                          SELECT    "CHASSIS_NODE_NAME" ,

                                  "SLOT_NODE_NAME",

                                  "CHASSIS_TYPE",

                                  "CHASSIS_NODE_TYPE",

                                  SLOT,

                                  "SLOT_NODE_TYPE",

                                  SYSNAME

                          FROM

                              (SELECT "CHASSIS_NODE_NAME" ,

                                      "SLOT_NODE_NAME",

                                      "CHASSIS_TYPE",

                                      "CHASSIS_NODE_TYPE",

                                      SLOT,

                                      "SLOT_NODE_TYPE",

                                      SYSNAME,

                                      RANK() OVER (PARTITION BY SYSNAME ORDER BY "CHASSIS_NODE_NAME" ,"SLOT_NODE_NAME", "CHASSIS_TYPE","CHASSIS_NODE_TYPE", SLOT,"SLOT_NODE_TYPE") RNK

                              FROM "SAEGW"."CHASIS_INVENTORY"

                              WHERE SYSNAME IN (SELECT DISTINCT(PDNGW) FROM "SAEGW"."GTP_STATS" WHERE SUBSTR(PDNGW,11,3) IN ('P01', 'P02', 'P03'))) A

                          WHERE RNK = 1;

                          • Re: how Link & Concatenate tables using oracle data source

                            This is the order i'm loading the tables from oracle

                             

                            PDP_COUNT:

                            PCRF_STATS:

                            DB_SESSION_COUNT:

                             

                            LinkTable_Temp:

                              // Concatimating

                              // Same order above

                             

                            LinkTable:

                             

                            Drop Table LinkTable_Temp;

                             

                            Same thing you can find it in my qvd attached above. Please let me know if you need any further information.

                          • Re: how Link & Concatenate tables using oracle data source
                            Alex Pan

                            Can you look at the table viewer after you create all the tables(before you delete them, try to comment eveything after the creation of the tables), do they exist?

                            If not, you did not connect the DB and the tables were not created.

                        • Re: how Link & Concatenate tables using oracle data source
                          Alex Pan

                          i mentioned that on my first reply.

                          but goo to hear.