Oracle View Returns Wrong Results The First Time It Is Run

[ospyros]

I have the following view:

CREATE OR REPLACE VIEW V_CLC_PRF_IND24_DNM ( TRNCMPL,
TRNSTR, C_INV_PK, S_RGN_PK, S_RGN_DESCR
) AS SELECT SUM(V_RC_TR_PRG.PRG_END_MEN_CNT) + SUM(V_RC_TR_PRG.PRG_END_WMEN_CNT) AS TRNCMPL,
SUM(V_RC_TR_PRG.PRG_START_MEN_CNT) + SUM(V_RC_TR_PRG.PRG_START_WMEN_CNT) AS TRNSTR,
V_RC_TR_PRG.C_INV_PK, S_REGION.S_RGN_PK, S_REGION.S_RGN_DESCR
FROM V_RC_TR_PRG , S_REGION , S_COUNTY , V_CMPL
WHERE ( (S_COUNTY.S_CNT_S_RGN_PK = S_REGION.S_RGN_PK) AND
(V_RC_TR_PRG.S_RGN_PK = S_REGION.S_RGN_PK) AND
(V_RC_TR_PRG.S_CNT_PK = S_COUNTY.S_CNT_PK) AND
(V_RC_TR_PRG.C_KEK_ID = V_CMPL.C_KEK_ID) AND
(V_RC_TR_PRG.C_INV_PK = V_CMPL.C_INV_PK) AND
(V_RC_TR_PRG.VALID_TO = TO_TIMESTAMP('1/1/3000','DD/MM/YYYY')) AND
(V_CMPL.VALID_TO = TO_TIMESTAMP('1/1/3000','DD/MM/YYYY')) AND
(V_CMPL.V_LKP_CMPLT_STATE_ID = 1) AND (V_RC_TR_PRG.V_LKP_TR_PRG_STATE_ID = 2)
AND (V_RC_TR_PRG.V_LKP_TR_PRG_IMPL_AREA_ID = 1) AND (V_RC_TR_PRG.V_LKP_TR_PRG_TRG_GRP_ID = 1) )
GROUP BY V_RC_TR_PRG.C_INV_PK , S_REGION.S_RGN_PK, S_REGION.S_RGN_DESCR

which accesses the respective (temporal) tables.

Now I run the following from SQL scratchpad:

select * from V_CLC_PRF_IND24_DNM

The first time I do that (after the server has been started) I get the following results:

733 - 771 - 19
10123 - 10239 - 20
etc.

If I just press execute again, and for any number of times, I get:

733 - 771 - 19
4498 - 4831 - 20 etc.

which is the correct result...

Does anyone have any Idea of what is going on?

Thanks in advance

[jkoopmann]

sounds like you need to investigate more for us.
1. are the base tables that this view relies upon being changed.
2. try and issue the select statement that this view is built upon and see if you get the same results.
3. you may want to think about isolating the result set for only one group or even one row.

[ospyros]

1. Of course not :-)
2. Yes I did, and the result is the same...
3. I did it for the second row, which caused the problem, and here are the results:

[ospyros]

1. Of course not :-)
2. Yes I did, and the result is the same...
3. I did it for the second row, which caused the problem, and here are the results:

The first time it returns something around 30000

The second time (and no matter how many times after I run it) I get the 10123 - 10239 wrong results

Now If I just run the query with select * from view I get the list of all rows with the CORRECT results

Putting back the criteria gets me the correct results:

4498 - 4831

!!!!!!!!!!!!!!!!!

[jkoopmann]

hey, gota ask sometimes
is this view based on tables or views?
the names almost suggest views.
can you post them and maybe we can see something in them.

[ospyros]

CREATE TABLE S_COUNTY (
S_CNT_PK NUMBER (11) DEFAULT 0 NOT NULL,
S_CNT_DESCR VARCHAR2 (50) NOT NULL,
S_CNT_STAT_CODE VARCHAR2 (2),
S_CNT_S_RGN_PK NUMBER (11) DEFAULT 0,
CONSTRAINT S_CNT_PK_PK
PRIMARY KEY ( S_CNT_PK ) )



CREATE TABLE S_REGION (
S_RGN_PK NUMBER (11) DEFAULT 0 NOT NULL,
S_RGN_DESCR VARCHAR2 (50),
S_RGN_STAT_CODE VARCHAR2 (2),
CONSTRAINT S_RGN_PK_PK
PRIMARY KEY ( S_RGN_PK ) )




CREATE TABLE V_RC_TR_PRG (
V_TR_PRG_ID NUMBER (11) NOT NULL,
C_INV_PK NUMBER (11) NOT NULL,
VALID_FROM TIMESTAMP(6) NOT NULL,
VALID_TO TIMESTAMP(6) DEFAULT '1/1/3000 00:00:00' NOT NULL,
S_USR_PK NUMBER (11),
C_KEK_ID NUMBER (11) NOT NULL,
V_LKP_TR_PRG_FND_TYPE_ID NUMBER (11),
TR_PRG_FND_TYPE_DESCR VARCHAR2 (255),
V_LKP_TR_PRG_STATE_ID NUMBER (11),
S_THEME_ID NUMBER (11),
S_BRANCH_ID NUMBER (11),
V_LKP_TR_PRG_TRG_GRP_ID NUMBER (11),
S_RGN_PK NUMBER (11) NOT NULL,
S_CNT_PK NUMBER (11) NOT NULL,
C_BUILD_ID NUMBER (11),
V_LKP_TR_PRG_IMPL_AREA_ID NUMBER (11),
TR_PRG_IMPL_AREA_DESCR VARCHAR2 (255),
DATA_ENTRY_DATE DATE,
PRG_TITLE VARCHAR2 (255),
PRG_OBJECTIVE VARCHAR2 (255),
PRG_APRV_CODE VARCHAR2 (255),
PRG_CODE VARCHAR2 (255),
PRG_TECH_OFFER_GRD FLOAT,
PRG_START_DATE DATE,
PRG_IS_INTERRUPTED NUMBER (1),
PRG_THEORY_HR_COUNT NUMBER (11),
PRG_PRACTICE_HR_COUNT NUMBER (11),
PRG_END_DATE DATE,
PRG_THRY_HR_CNT_TIL_ENTRY_DATE NUMBER (11),
PRG_PRACTICE_HR_CNT_TIL_ENTRY_ NUMBER (11),
PRG_HAS_CERT_EXAMS NUMBER (1),
PRG_OCP_DATA_ENTRY_DATE DATE,
PRG_START_MEN_CNT NUMBER (11),
PRG_START_WMEN_CNT NUMBER (11),
PRG_START_VSG_MEN_CNT NUMBER (11),
PRG_START_VSG_WMEN_CNT NUMBER (11),
PRG_END_MEN_CNT NUMBER (11),
PRG_END_WMEN_CNT NUMBER (11),
PRG_END_VSG_MEN_CNT NUMBER (11),
PRG_END_VSG_WMEN_CNT NUMBER (11),
V_LKP_TR_PRG_ABND_RSN_ID NUMBER (11),
TR_PRG_ABND_RSN_DESCR VARCHAR2 (255),
PRG_ABND_MEN_CNT NUMBER (11),
PRG_ABND_WMEN_CNT NUMBER (11),
PRG_ABND_VSG_MEN_CNT NUMBER (11),
PRG_ABND_VSG_WMEN_CNT NUMBER (11),
PRG_EXAMS_MEN_CNT NUMBER (11),
PRG_EXAMS_WMEN_CNT NUMBER (11),
PRG_EXAMS_VSG_MEN_CNT NUMBER (11),
PRG_EXAMS_VSG_WMEN_CNT NUMBER (11),
PRG_CERT_MEN_CNT NUMBER (11),
PRG_CERT_WMEN_CNT NUMBER (11),
PRG_CERT_VGS_MEN_CNT NUMBER (11),
PRG_CERT_VGS_WMEN_CNT NUMBER (11),
PRG_ABSRBD_MEN_CNT NUMBER (11),
PRG_ABSRBD_WMEN_CNT NUMBER (11),
PRG_ABSRBD_VSG_MEN_CNT NUMBER (11),
PRG_ABSRBD_VSG_WMEN_CNT NUMBER (11),
PRG_OCCPD_MEN_CNT NUMBER (11),
PRG_OCCPD_WMEN_CNT NUMBER (11),
PRG_OCCPD_VSG_MEN_CNT NUMBER (11),
PRG_OCCPD_VSG_WMEN_CNT NUMBER (11),
NOTES CLOB,
CONSTRAINT PK_V_RC_TR_PRG
PRIMARY KEY ( V_TR_PRG_ID, C_INV_PK, VALID_FROM ) )

Thanks in advance for your time. If you need me to send data by e-mail please just let me knwo

[jkoopmann]

nothing unusuall there but you did forget V_CMPL

[ospyros]

SORRY:


CREATE TABLE V_CMPL (
C_KEK_ID NUMBER (11) NOT NULL,
C_INV_PK NUMBER (11) NOT NULL,
VALID_FROM TIMESTAMP(6) NOT NULL,
VALID_TO TIMESTAMP(6) DEFAULT '1/1/3000 00:00:00' NOT NULL,
S_USR_PK NUMBER (11),
FIRST_CHK_DATE DATE,
FIRST_CHK_ARVL_DATE DATE,
FIRST_CHK_PRTCL_NUM VARCHAR2 (255),
FIRST_CHK_IN_TIME NUMBER (1),
FIRST_CHK_IS_PRINTED NUMBER (1),
FIRST_CHK_VALID_PRNTOUT NUMBER (1),
FIRST_CHK_ORG_PAGE_CNT NUMBER (11),
FIRST_CHK_CPY_PAGE_CNT NUMBER (11),
FIRST_CHK_LTR_PRN_DATE DATE,
V_LKP_CMPLT_STATE_ID NUMBER (11),
SCND_CHK_DATE DATE,
SCND_CHK_ARVL_DATE DATE,
SCND_CHK_PRTCL_NUM VARCHAR2 (255),
SCND_CHK_LTR_PRN_DATE DATE,
NOTES CLOB,
INTRNL_NOTES CLOB,
CONSTRAINT PK_V_COMPLETENESS
PRIMARY KEY ( C_KEK_ID, C_INV_PK, VALID_FROM ) )


CREATE UNIQUE INDEX SYS_IL0000035784C00020$$ ON
V_CMPL()


CREATE UNIQUE INDEX SYS_IL0000035784C00021$$ ON
V_CMPL()


CREATE INDEX V_CMPL_INV_PK ON
V_CMPL(C_INV_PK)


CREATE INDEX V_CMPL_KEK_ID ON
V_CMPL(C_KEK_ID)


CREATE INDEX V_CMPL_VALID_FROM ON
V_CMPL(VALID_FROM)


CREATE INDEX V_CMPL_VALID_TO ON
V_CMPL(VALID_TO)


But I dont think there is anything unusual on that table either...