Re: Strange query result with Order by

From: Chris <christianboivin1_at_hotmail.com>
Date: 30 Nov 2004 06:13:44 -0800
Message-ID: <da20daf0.0411300613.670c7fdb@posting.google.com>

I'm on win 2K with Oracle 9.2.0.1.0
I'm the only one who work on the data
so, its not a commit issue ...
and its repeatable, one week after the insertion of the records ... the second row "disappear" just when i add the "ORDER BY hev_annee_deb DESC"
clause ... without DESC its OK ...

I'm on a LM tablespace and here's the script for the table

CREATE TABLE HIS_EVAL
(

  HEV_ANNEE_DEB      NUMBER(4),
  HEV_ANNEE_FIN      NUMBER(4),
  HEV_ID_UEF         NUMBER(12),
  HEV_VALEUR_T       NUMBER(15),
  HEV_VALEUR_B       NUMBER(15),
  HEV_PCT_VARIATION  VARCHAR2(15 BYTE),
  HEV_DATE_CREATED   DATE                       DEFAULT SYSDATE       
       NOT NULL,

  HEV_DATE_MODIFIED DATE
)
TABLESPACE USER_DATA
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (

            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )

LOGGING
NOCACHE
NOPARALLEL; thanks for helping
Chris

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0411251412.7ab90762_at_posting.google.com>...
> christianboivin1_at_hotmail.com (Chris) wrote in message news:<da20daf0.0411250530.4d7a6d3a_at_posting.google.com>...
> > Hi all,
> >
> > First time i see this ... when i run this query :
> >
> > SELECT hev_annee_deb, hev_annee_fin, hev_id_uef, hev_valeur_t,
> > hev_valeur_b,
> > NVL (hev_valeur_t, 0) + NVL (hev_valeur_b, 0) valeur_totale,
> > HEV_PCT_VARIATION
> > FROM HIS_EVAL
> > WHERE hev_id_uef = 1449902
> > ORDER BY hev_annee_deb DESC
> >
> > i got 1 row ...
> >
> > 2002,2002,1449902,23500,84700,108200,
> >
> > if i remove the descending order ... like this
> >
> > SELECT hev_annee_deb, hev_annee_fin, hev_id_uef, hev_valeur_t,
> > hev_valeur_b,
> > NVL (hev_valeur_t, 0) + NVL (hev_valeur_b, 0) valeur_totale,
> > HEV_PCT_VARIATION
> > FROM HIS_EVAL
> > WHERE hev_id_uef = 1449902
> > ORDER BY hev_annee_deb
> >
> > i got 2 row ...
> >
> > 2001,2001,1449902,23500,61700,85200,
> > 2002,2002,1449902,23500,84700,108200,
> >
> > What's the problem ... ?
> > Thanks
> > Chris
>
> Chris,
> First, what version of Oracle? On what platform?
> Second, is this repeatable? As shown the result could be due to
> someone inserting a row and committing it after you first query
> started. The second query would then show the new row.
>
> HTH -- Mark D Powell --
Received on Tue Nov 30 2004 - 08:13:44 CST