topic Re: how Link & Concatenate tables using oracle data source in QlikView

how Link & Concatenate tables using oracle data source

Mon, 17 Sep 2012 15:37:03 GMT

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

alexpanjhc — Mon, 17 Sep 2012 15:53:21 GMT

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

Anonymous — Mon, 17 Sep 2012 15:56:36 GMT

Same regarding the DB_SESSION_COUNT.

Re: how Link & Concatenate tables using oracle data source

Mon, 17 Sep 2012 16:47:24 GMT

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 — Mon, 17 Sep 2012 17:01:13 GMT

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

alexpanjhc — Mon, 17 Sep 2012 17:01:54 GMT

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

Mon, 17 Sep 2012 17:19:22 GMT

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

Mon, 17 Sep 2012 17:34:21 GMT

Load Script:

// 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

Mon, 17 Sep 2012 18:02:15 GMT

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

MayilVahanan — Mon, 17 Sep 2012 18:21:39 GMT

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 ..

Re: how Link & Concatenate tables using oracle data source

Mon, 17 Sep 2012 18:50:57 GMT

After reload, I did not find LinkTable_Temp in table view.

Re: how Link & Concatenate tables using oracle data source

MayilVahanan — Mon, 17 Sep 2012 18:54:33 GMT

So only that error message appears.. So make sure that.

Re: how Link & Concatenate tables using oracle data source

Mon, 17 Sep 2012 19:00:28 GMT

No error message. it has loaded 3 tables but not LinkTable_Temp at same time it did not rise the error too. The reload scrip has completed successfully.

PDP_COUNT: -- Loaded

PCRF_STATS: --- Loaded

DB_SESSION_COUNT: -- Loaded

LinkTable_Temp:

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",

DATETIME AS DATE,

'GATEWAYDATE' AS DateType

FROM

"CISCO_SAEGW"."PDNGW_GTP_STATS"

WHERE

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

CONCATENATE (LinkTable_Temp) -- Commented

LinkTable: -- Commented

Drop Table LinkTable_Temp; --- Commented

Re: how Link & Concatenate tables using oracle data source

Anonymous — Mon, 17 Sep 2012 19:03:53 GMT

This means that it was auto-concatenated to a previously loaded table with the same set of fields.

Edit: also there is a chance thta something is wrong in the query for this table. I also had this problem in QV11 that when SQL is incorrect, no errors and nothing loaded, although ErrorMode=1. Try to run this qiuery outside of QlikView, in SQL+, or SQL Developer, for example.

Re: how Link & Concatenate tables using oracle data source

Tue, 18 Sep 2012 14:09:50 GMT

I have test these sql's from sql developer and there are working fine. Do you have any other thgouths.

Re: how Link & Concatenate tables using oracle data source

Anonymous — Tue, 18 Sep 2012 14:13:02 GMT

In this case, see my first assuption: "This means that it was auto-concatenated to a previously loaded table with the same set of fields."

Re: how Link & Concatenate tables using oracle data source

alexpanjhc — Tue, 18 Sep 2012 14:30:30 GMT

did you say you connected to Oralce not SQL server?

Re: how Link & Concatenate tables using oracle data source

Tue, 18 Sep 2012 14:44:10 GMT

Please don't confuse with sql. I'm working with Oracle only and use sql developer (oracle developer GUI tool) to run sql's.

Re: how Link & Concatenate tables using oracle data source

Tue, 18 Sep 2012 15:15:25 GMT

I have commentout concatenate statement and ran it by providing diffrent table name and it has create table with data contains only non-common columns. But it was successfully completed and load data. I dont understand why it is throwing error if I turn on concatenate statement.

Re: how Link & Concatenate tables using oracle data source

alexpanjhc — Tue, 18 Sep 2012 16:12:03 GMT

just to amuse me

add

load *;

betwen your table name and sql select and to reload to see what happens