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

> -----Message d'origine-----
> De : Grossmann, Gert [mailto:gert.grossmann@xxxxxxx]
> Envoyé : vendredi 18 mars 2005 07:31
> À : maxdb
> Objet : 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.


if the rowno (the numbering) is done before the order by clause, then
it is not like the TOP clause of SQL Server (if you thought about this
dbs) ; in SQL Server, the order clause is done before the numbering, then
you can extract then TOP 1, 5, x, or 10%, x% ligns of the ordered query.


> 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=fabrice.bourdel@xxxxxxx



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