Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
do you want to create a table PCRF_STATS? if so, use NOCANCACNATE before the table name, otherwise, you do not need that name.
Same regarding the DB_SESSION_COUNT.
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
Hi
Can you provide the loaded tables name alone in which order you loaded in script or provide the script alone?
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.
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;
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;
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.
Hi
Can you reload the the file without use concatenate and check in the table viewer whether the LinkTable_Temp is available or not?
If available, then use concatenate ..