Hello Fabrice, Your first query sorts *all* matching rows, and then you look at the first 20 rows.
Your second query selects some 20 rows (in no really predictable order), and sorts them. This is really not the same operation. Regards Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: Fabrice Bourdel [mailto:[EMAIL PROTECTED] > Sent: Monday, March 21, 2005 2:50 PM > To: maxdb > Cc: Grossmann, Gert > Subject: RE: strange problem with rowno : doesn't produce > good result set when where conditions are present > > > 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] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
