Different result from procedure and plain SQL

From: cschang <cschang_at_maxinter.net>
Date: Sat, 05 Feb 2005 15:16:39 -0500
Message-ID: <110aadlknbkr881@corp.supernews.com>

I wrote the procedure as following:

CREATE OR REPLACE PROCEDURE getLatest (in_id IN varchar2,  

   out_vid OUT t_vid,  

   out_lineId OUT t_lineID

                                                out_dt OUT t_dt)

      CURSOR c_md (p_id    IN VARCHAR2) IS
             select v_id, line_id, latestdt
             from (
                      SELECT v_id, line_id, maxdate, max(maxdate) over 
(PARTITION BY line_id) latestdt
                      FROM
                      (
                           select v_id, line_id, maxdate
                           from (
                                    select  A.v_id, A.line_id, 
status_date, max(status_date) over (partition by line_id) maxdate
                                    from A, B
                                    where A.v_id = B.v_id
                                    and A.line_id = B.line_id
                                    and B.id = p_id
                                   )  t1
                           where maxdate = t1.status_date
                           UNION
                           select v_id, line_id, maxdate
                           from (
                                    select  A.v_id, line_id, 
status_date, max(status_date) over (partition by line_id) maxdate
                                    from A, C
                                    where A.v_id = C.v_id
                                     and c.id = p_id
                                    )  t2
                           where maxdate = t2.status_date
                       )
                     ) s
               where latestdt = s.maxdate;

BEGIN

           OPEN c_md (p_id);
           FETCH c_md BULK COLLECT INTO t_vid, t_lineID, t_dt;
           CLOSE c_md;

END; However, when I run the the SQL codes of the cursor of the procedure, I got different set of data as those of the procedure. to demo.without the last layer of the MAX(..) over (PARTION BY ..) the records set I got something like

v_id       Line_id          latestdt
T1234       0001           2/5/05  10:45
T1234       0001           1/22/05  07:15
T1234       0002           2/5/05   11:12
T1234       0002           1/22/05  13:35

With the last laryer on, I got
T1234       0001           2/5/05  10:45
T1234       0002           2/5/05   11:12

from the SQl mode

But got the following from the PROCEDURE as they return through a WEB page

T1234       0001           1/22/05  07:15
T1234       0002           1/22/05  13:35

Why? I have 9 R2 on Windows 2K

C Chang Received on Sat Feb 05 2005 - 14:16:39 CST