Re: Incorrect result from VIEW in Oracle 8i Lite

Hi Ruud,

When it works when you don't use the view, are you sure you are adding the additional AND condition *after* the NOT EXISTS AND condition list (ie. after the last ")" ) as would the view and not within the NOT EXIST condition ?

Cheers

Richard

Ruud Laanbroek wrote:
>
> 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:33:51 CDT