Re: Oracle8i Select Statement

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 16 Jul 1999 14:02:33 +0100
Message-ID: <932130465.15343.0.nnrp-01.9e984b29@news.demon.co.uk>

I think you have two problems here,
The first is about row elimination,
the second is about scope.

Your query MUST return three rows, as
you have queried the whole table without a WHERE clause, and there are three rows in the table.

The scope problem is a little more subtle perhaps:

>CREATE or replace TYPE BODY Zimmer_ty As
>Member FUNCTION getpreis(Zim Number, Hot Number) RETURN REAL As
> Preis_pro_Tag REAL;
>BEGIN
> SELECT preis INTO Preis_pro_Tag
> FROM zimmer
> WHERE (Zimmernr = Zim)
> AND (Hotel_id = Hot);
> RETURN Preis_pro_Tag;
>END getpreis;
>End;

The table ZIMMER does not have a column called PREIS, so the select of PREIS is actually converted to a select of the (local constant) SELF.PREIS, which is the value in the current row each time you run the query, so what hits the database (three times, once for each row) is:

    select :b1 from room
    where roombumber = :b2
    and hotel_id = :b3

:b2 and :b3 are ALWAYS set to 1,
but :b1 is 60 for the first run, 55 for the second, and 40 for the third.

If you changed the code to read:

> SELECT ZM.Z_EIGENSCHAFT.preis INTO Preis_pro_Tag
> FROM zimmer ZM
> WHERE (Zimmernr = Zim)
> AND (Hotel_id = Hot);
> RETURN Preis_pro_Tag;

Then you would get the result:

P.Z_EIGENSCHAFT.GETPREIS(1, 1)

---

                           60
                           60
                           60

which is still not what you want, but at least selects data from the one row where room = 1 and hotel_id =1 for every single row accessed.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

news.isar.net wrote in message <7mn6oo$865$1_at_nixe.ISAR.net>...
>Hello
>
>I have a question!!! I'm using Oracle8i, Release 8.1.5, and have a problem
>with a select statement.
>
>What is wrong with the method "getpreis" in the TYPE "Zimmer_ty", because
>the SELECT Statement gets the wrong data. Why does the statement return 3
>values?
>
>
Received on Fri Jul 16 1999 - 08:02:33 CDT