Hello Alexander, ok... then, the numbering (rowno) is done berore ordering...
numbering in a last pass of the query (after ordering) would have be good idea ? > -----Message d'origine----- > De : Schroeder, Alexander [mailto:[EMAIL PROTECTED] > Envoy� : lundi 21 mars 2005 14:57 > � : Fabrice Bourdel; maxdb > Cc : Grossmann, Gert > Objet : RE: strange problem with rowno : doesn't produce good result set > when where conditions are present > > > 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]
