AW: strange problem with rowno : doesn't produce good result set when where conditions are present

ROWNO hasn't same meanings as TOP in other DBS.
With ROWNO you get x rows from relation given in FROM-clause (this row order 
depends on access path; index, key etc.) met conditions in WHERE-clause. Then 
these x rows will be handled according ORDER BY-clause, GROUP BY-clause etc.

Gert

-----Ursprüngliche Nachricht-----
Von: Fabrice Bourdel [mailto:fabrice.bourdel@xxxxxxx] 
Gesendet: Freitag, 18. März 2005 02:35
An: maxdb
Betreff: strange problem with rowno : doesn't produce good result set when 
where conditions are present


hi,

using maxdb 7.5.0.24, i have a stored proc with
permits me to extract data "pages by pages", for
browsing data through apache/php (a page is then
x ligns)

General procedure :
 declare cursor for selecting data with conditions
 (if there is any), add the rowno, ordering the
 data. I principaly extract rowno and the primary
 key, using a "for reuse" cursor

 declare the resuls from the first cursor, filtering
 the generated rowno beetwin :piFrom and :piTo variable
 and redo an order by (the same as for the first cursor).

The problem is when mixing conditions and doing the ordering
(while the rowno column was added) for the first cursor :
it doesn't extract the right datas...

...but in an "optimized form" :

my procedure permits me to extract data from lign 1 to n, and
lign n+1 to m, etc...
When i need from 1 to x, then, i do a "optimisation" by filtering
immediatly with rowno <= :piTo in the phase 1.
There is then the problem because it seem that the numbering done
by the rowno doen't take account of the ordered clause (when there
are where conditions)...

To get rig of this problem, i had to do one more cursor between
the first and the result cursor.

I produce here just a portion af the total code, where :piFrom = 1

****************
THE FIST FORM (doesn't give the good result
****************

Phase 1 extracts the "base" data just with the conditions, rowno and
filtered on rowno
Phase 2 produce final result, filtering on the last produced rowno (renamed
in LNG)

/* Phase 1
*********************************************************************/
DECLARE cur CURSOR FOR
SELECT      ROWNO AS LNG, IMDO.IMDO_ID, IMDO.IMSO_ID, IMDO.IMDO_CA
FROM        SA.IMPORT_DONNEE IMDO
INNER JOIN  SA.IMPORT_SOCIETE IMSO ON IMSO.IMSO_ID = IMDO.IMSO_ID
WHERE       IMSO.IMGR_ID = :piIMGR_ID
        AND IMDO.IMDO_CHA = :psIMDO_CHA
        AND IMDO_INTERCO = 'N'
        AND IMDO_TRAIT IS NULL
        AND ROWNO <= :piTo
FOR REUSE;
/* Phase 2
*********************************************************************/
DECLARE :$cursor CURSOR FOR
SELECT      IMDO.IMDO_ID,
            COSO.COSO_CODESOC,
            COSO.COSO_LIB,
            IDTC_RS.IDTC_TXT          AS IMDO_XRS,
            IDTC_ADR1.IDTC_TXT        AS IMDO_XADR1,
            IDTC_ADR2.IDTC_TXT        AS IMDO_XADR2,
            IDTC_ADR3.IDTC_TXT        AS IMDO_XADR3,
            IDTC_ADR4.IDTC_TXT        AS IMDO_XADR4,
            IDTC_CP.IDTC_TXT          AS IMDO_XCP,
            IDTC_VILLE.IDTC_TXT       AS IMDO_XVILLE,
            IDTC_ETAT.IDTC_TXT        AS IMDO_XETAT,
            IMDO.PAY_CODE,
            IDTC_IDENT_ET.IDTC_TXT    AS IMDO_XIDENT_ET,
            IDTC_IDENT_ET_PR.IDTC_TXT AS IMDO_XIDENT_ET_PR,
            IMDO.IMDO_CHA,
            IMDO.IMDO_CA
FROM        cur C
INNER JOIN  SA.IMPORT_DONNEE IMDO                           ON IMDO.IMDO_ID
= C.IMDO_ID
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_RS           ON
IDTC_RS.IDTC_ID          = IMDO.IDTC_ID_XRS
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ADR1         ON
IDTC_ADR1.IDTC_ID        = IMDO.IDTC_ID_XADR1
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ADR2         ON
IDTC_ADR2.IDTC_ID        = IMDO.IDTC_ID_XADR2
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ADR3         ON
IDTC_ADR3.IDTC_ID        = IMDO.IDTC_ID_XADR3
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ADR4         ON
IDTC_ADR4.IDTC_ID        = IMDO.IDTC_ID_XADR4
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_CP           ON
IDTC_CP.IDTC_ID          = IMDO.IDTC_ID_XCP
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_VILLE        ON
IDTC_VILLE.IDTC_ID       = IMDO.IDTC_ID_XVILLE
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ETAT         ON
IDTC_ETAT.IDTC_ID        = IMDO.IDTC_ID_XETAT
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_IDENT_ET     ON
IDTC_IDENT_ET.IDTC_ID    = IMDO.IDTC_ID_XIDENT_ET
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_IDENT_ET_PR  ON
IDTC_IDENT_ET_PR.IDTC_ID = IMDO.IDTC_ID_XIDENT_ET_PR
INNER JOIN  SA.IMPORT_SOCIETE IMSO                          ON IMSO.IMSO_ID
= IMDO.IMSO_ID
INNER JOIN  SA.CONTRAT_SOCIETE COSO                         ON COSO.COSO_ID
= IMSO.COSO_ID
WHERE       C.LNG;


=> the result is absolutely not good.


****************
THE LAST FORM (with work, but slowly)
****************

Phase 1 extracts the "base" data just with the conditions
Phase 2 just produce the rowno column, ordered corectly
Phase 3 produce final result, filtering on the last produced rowno (renamed
in LNG)

/* Phase 1
*********************************************************************/
DECLARE cur01 CURSOR FOR
SELECT      IMDO.IMDO_ID, IMDO.IMSO_ID, IMDO.IMDO_CA
FROM        SA.IMPORT_DONNEE IMDO
INNER JOIN  SA.IMPORT_SOCIETE IMSO ON IMSO.IMSO_ID = IMDO.IMSO_ID
WHERE       IMSO.IMGR_ID = :piIMGR_ID
        AND IMDO.IMDO_CHA = :psIMDO_CHA
        AND IMDO_INTERCO = 'N'
        AND IMDO_TRAIT IS NULL
FOR REUSE;
/* Phase 2
*********************************************************************/
DECLARE cur02 CURSOR FOR
SELECT    ROWNO AS LNG, IMDO_ID, IMDO_CA
FROM      CUR01
ORDER BY  IMDO_CA DESC
FOR REUSE;
/* Phase 2
*********************************************************************/
DECLARE :$cursor CURSOR FOR
SELECT      IMDO.IMDO_ID,
            COSO.COSO_CODESOC,
            COSO.COSO_LIB,
            IDTC_RS.IDTC_TXT          AS IMDO_XRS,
            IDTC_ADR1.IDTC_TXT        AS IMDO_XADR1,
            IDTC_ADR2.IDTC_TXT        AS IMDO_XADR2,
            IDTC_ADR3.IDTC_TXT        AS IMDO_XADR3,
            IDTC_ADR4.IDTC_TXT        AS IMDO_XADR4,
            IDTC_CP.IDTC_TXT          AS IMDO_XCP,
            IDTC_VILLE.IDTC_TXT       AS IMDO_XVILLE,
            IDTC_ETAT.IDTC_TXT        AS IMDO_XETAT,
            IMDO.PAY_CODE,
            IDTC_IDENT_ET.IDTC_TXT    AS IMDO_XIDENT_ET,
            IDTC_IDENT_ET_PR.IDTC_TXT AS IMDO_XIDENT_ET_PR,
            IMDO.IMDO_CHA,
            IMDO.IMDO_CA
FROM        cur02 C
INNER JOIN  SA.IMPORT_DONNEE IMDO                           ON IMDO.IMDO_ID
= C.IMDO_ID
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_RS           ON
IDTC_RS.IDTC_ID          = IMDO.IDTC_ID_XRS
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ADR1         ON
IDTC_ADR1.IDTC_ID        = IMDO.IDTC_ID_XADR1
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ADR2         ON
IDTC_ADR2.IDTC_ID        = IMDO.IDTC_ID_XADR2
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ADR3         ON
IDTC_ADR3.IDTC_ID        = IMDO.IDTC_ID_XADR3
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ADR4         ON
IDTC_ADR4.IDTC_ID        = IMDO.IDTC_ID_XADR4
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_CP           ON
IDTC_CP.IDTC_ID          = IMDO.IDTC_ID_XCP
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_VILLE        ON
IDTC_VILLE.IDTC_ID       = IMDO.IDTC_ID_XVILLE
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_ETAT         ON
IDTC_ETAT.IDTC_ID        = IMDO.IDTC_ID_XETAT
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_IDENT_ET     ON
IDTC_IDENT_ET.IDTC_ID    = IMDO.IDTC_ID_XIDENT_ET
INNER JOIN  SA.IMPORT_DONNEE_TEXTE_COMMUN IDTC_IDENT_ET_PR  ON
IDTC_IDENT_ET_PR.IDTC_ID = IMDO.IDTC_ID_XIDENT_ET_PR
INNER JOIN  SA.IMPORT_SOCIETE IMSO                          ON IMSO.IMSO_ID
= IMDO.IMSO_ID
INNER JOIN  SA.CONTRAT_SOCIETE COSO                         ON COSO.COSO_ID
= IMSO.COSO_ID
WHERE       C.LNG BETWEEN :piFROM AND :piTO
ORDER BY    C.LNG;

Do you known this problem and have another workaround ?
many thanks for responses.


-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gert.grossmann@xxxxxxx


--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx