Re: Incorrect result from VIEW in Oracle 8i Lite

From: Ruud Laanbroek <ruudl_at_commotio.nl>
Date: 14 Aug 2002 03:44:45 -0700
Message-ID: <72f6e38f.0208140244.6b85c858@posting.google.com>

Well... I figured out that the bug (at least I think it is) has to do with the use the of conditions with SYSDATE in the view.

I removed the
  AND TRUNC(SYSDATE) BETWEEN
  TRUNC(SERVICE1.STARTDATE) AND
  TRUNC(DECODE(SERVICE1.ENDDATE, NULL, SYSDATE + 1, SERVICE1.ENDDATE)) from the view.
Now it works. And I know, based on the actual data in the database (the actual values for STARTDATE and ENDATE), that this should not make a difference for the result.

Anyway.. I did a little test which proves that the SYSDATE conditions do not work as they should in general:

Query

---

SELECT 'Hello' HELLO
FROM DUAL
WHERE TRUNC(SYSDATE) BETWEEN
TRUNC(SYSDATE-100) AND
TRUNC(DECODE(NULL,NULL,SYSDATE+1,SYSDATE+100)) ;

Result

---

HELLO

---

Hello

VIEW Definition

---

CREATE OR REPLACE VIEW V_TEST_SYSDATE
AS
SELECT 'Hello' HELLO
FROM DUAL
WHERE TRUNC(SYSDATE) BETWEEN
TRUNC(SYSDATE-100) AND
TRUNC(DECODE(NULL,NULL,SYSDATE+1,SYSDATE+100)) ;
commit;

Result

---

Operation 0 succeeded.
Commit complete.

Query

---

select * from v_test_sysdate;

Result

---

no rows selected

Is this a bug? Is there a problem with SYSDATE in a VIEW?

Ruud

ruudl_at_commotio.nl (Ruud Laanbroek) wrote in message news:<72f6e38f.0208122318.1a89f876_at_posting.google.com>...
> Oracle 8i Lite version 4.0.0.2.19
> Windows NT 4.0 WS, SP6a
>
>
> I have a view as follows:
> CREATE OR REPLACE VIEW V_SLOTONCARD
> AS
> SELECT SERVICE1.SERVICEIDD, SERVICE1.DESCRIPTION,
> SLOT.SLOTIDD, SLOT.SLOTTYPEID, EVENT1.CARDNR
> FROM EVENT EVENT1, EVENTLINE, SERVICE SERVICE1, SYSTEMFUNCTION, SLOT
> WHERE EVENTLINE.EVENTID = EVENT1.EVENTIDD
> AND EVENTLINE.SERVICEID = SERVICE1.SERVICEIDD
> AND SERVICE1.SLOTID = SLOT.SLOTIDD
> AND SERVICE1.SYSTEMFUNCTIONID = SYSTEMFUNCTION.SYSTEMFUNCTIONIDD
> AND SYSTEMFUNCTION.FUNCTIONTYPEID = 10000001
> AND TRUNC(SYSDATE) BETWEEN
> TRUNC(SERVICE1.STARTDATE) AND
> TRUNC(DECODE(SERVICE1.ENDDATE, NULL, SYSDATE + 1, SERVICE1.ENDDATE))
> AND SERVICE1.ACTIVE = 1
> AND SLOT.ACTIVE = 1
> AND SYSTEMFUNCTION.ACTIVE = 1
> AND NOT EXISTS
> ( SELECT EVENT2.TRXNRCARD
> FROM EVENT EVENT2, EVENTLINE, SERVICE SERVICE2, SYSTEMFUNCTION
> WHERE EVENT2.CARDNR = EVENT1.CARDNR
> AND EVENT2.TRXNRCARD > EVENT1.TRXNRCARD
> AND EVENTLINE.EVENTID = EVENT2.EVENTIDD
> AND EVENTLINE.SERVICEID = SERVICE2.SERVICEIDD
> AND SERVICE2.SYSTEMFUNCTIONID = SYSTEMFUNCTION.SYSTEMFUNCTIONIDD
> AND SYSTEMFUNCTION.FUNCTIONTYPEID IN (10000001, 10000002)
> AND SERVICE2.SLOTID = SERVICE1.SLOTID
> )
> ;
>
> Running
> SELECT * FROM V_SLOTONCARD WHERE CARDNR = 773058;
> results in 'no rows selected'.
> This is wrong, rows should be returned.
>
> If I run the SELECT from the view directly with the CARDNR as extra
> condition I do get the desired result:
> SELECT SERVICE1.SERVICEIDD, SERVICE1.DESCRIPTION,
> ... (as in VIEW)
> AND CARDNR = 773058;
>
> If I add the CARDNR condition to the VIEW definition the result is
> still wrong, no rows returned.
>
> There are other CARDNR values for which the view does give the correct
> result (one or more rows).
>
> What is going wrong here? I don't understand...
>
> Ruud
Received on Wed Aug 14 2002 - 05:44:45 CDT