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]
