ok,
as you say, "then these x rows will be handled according ORDER BY clause..."
then maxdb :
- extract data (from clause)
- order ligns
- add the rowno column
But i my case, the first 20 ligns from this query :
SELECT ROWNO AS LNG, IMDO.IMDO_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 = 1
AND IMDO.IMDO_CHA = 'S03F04C06'
AND IMDO_INTERCO = 'N'
AND IMDO_TRAIT IS NULL
ORDER BY IMDO_CA DESC
are not the same as :
SELECT ROWNO AS LNG, IMDO.IMDO_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 = 1
AND IMDO.IMDO_CHA = 'S03F04C06'
AND IMDO_INTERCO = 'N'
AND IMDO_TRAIT IS NULL
AND ROWNO <= 20
ORDER BY IMDO_CA DESC
is there something i missed ?
> -----Message d'origine-----
> De : Grossmann, Gert [mailto:[EMAIL PROTECTED]
> 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.
>
> Gert
>
> -----Urspr�ngliche Nachricht-----
> Von: Fabrice Bourdel [mailto:[EMAIL PROTECTED]
> 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/[EMAIL PROTECTED]
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
