Incorrect result from VIEW in Oracle 8i Lite

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 Tue Aug 13 2002 - 02:18:25 CDT