From: Fabrice Bourdel (fabrice.bourdelfree.fr)
Date: Fri Feb 11 2005 - 03:24:36 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hello,

I have a strange problem with a query. One version
with "id fields" work well, but it was for testing
and i need a text field of the id. When i change
the query to retrieve the text field, MaxDb
block with this message :

"General error;-8 POS(1) Execution failed, parse again"

... but the query never work...

I know it's a bit complicated query, but it's
because i have a table witch manage hierarchical
values without having a self reference field (then
father), but i use another structure.

-----> Another information : when the tables are empty,
it works !!! so, i send you file to create the 2 tables
and some data to fill the tables too
(the files are sent in the next mail)

I checked that Maxdb 7.5.0.23 (for x86) has the same problem
on Windows (2k) or on Linux debian (3.0r3)

******** The query witch work well :
(i added lign to help my explain where the query doesn't work)

1> select
2> xn1 = n1,
3> xn2 = case coso_niv when 2 then coso_lib else n2 end,
4> xn3 = case coso_niv when 3 then coso_lib else n3 end,
5> xn4 = case coso_niv when 4 then coso_lib else n4 end,
6> xn5 = case coso_niv when 5 then coso_lib else n5 end,
7> xn6 = case coso_niv when 6 then coso_lib else null end
8> from
9> (select imso.imso_id, coso00.coso_id, coso00.coso_lib, coso00.coso_niv,
10> n1 = (select coso01.coso_id from contrat_societe coso01 where
coso01.coso_niv = 1 and coso01.coso_bg < coso00.coso_bg and coso01.coso_bd >
coso00.coso_bd),
11> n2 = (select coso02.coso_id from contrat_societe coso02 where
coso02.coso_niv = 2 and coso02.coso_bg < coso00.coso_bg and coso02.coso_bd >
coso00.coso_bd),
12> n3 = (select coso03.coso_id from contrat_societe coso03 where
coso03.coso_niv = 3 and coso03.coso_bg < coso00.coso_bg and coso03.coso_bd >
coso00.coso_bd),
13> n4 = (select coso04.coso_id from contrat_societe coso04 where
coso04.coso_niv = 4 and coso04.coso_bg < coso00.coso_bg and coso04.coso_bd >
coso00.coso_bd),
14> n5 = (select coso05.coso_id from contrat_societe coso05 where
coso05.coso_niv = 5 and coso05.coso_bg < coso00.coso_bg and coso05.coso_bd >
coso00.coso_bd)
15> from contrat_societe coso00
16> inner join import_societe imso
17> on imso.coso_id = coso00.coso_id)

******** Step one, lign 10, changing "n1 = (select coso01.coso_id" with "n1
= (select coso01.coso_lib"
->> the query work

******** Step two, lign 11, changing "n2 = (select coso02.coso_id" with "n2
= (select coso02.coso_lib"
->> the query DON'T work

******** Step three, changing all the "coso_id" with "coso_codesoc", a
varchar(100) field like coso_lib
->> the query work, but i need the text (coso_lib) !!!

Just any information about the goal of the query :

The table contrat_societe contains a tree of values like :

Examples
*1) one record only
TEXT LEVEL LEFT RIGHT
A 1 1 2

*2) two records, the tree is A->B
TEXT LEVEL LEFT RIGHT
A 1 1 4
B 2 2 3

*3) tree records, the tree is A->B, A->C
TEXT LEVEL LEFT RIGHT
A 1 1 6
B 2 2 3
C 2 4 5

Select on the tree ordered by elements :
SELECT TEXT FROM contrat_societe ORDER BY LEFT

I need this : select the father of an element, for
example C :
SELECT TEXT FROM contrat_societe WHERE LEVEL = 1 AND LEFT < 2 AND RIGHT > 5

But, i need much more because i must populate calculated columns in the
query to
extract all the levels (finite levels)

I fact, the query must produce (based on the third example) :
LEV1 LEV2
A
A B
A C
and this result coupled with datas.

The n1, n2, n3, n4 and n5 in the query extract the respective
fathers of those levels (i have maximum 6 levels)

The "outer" query with the _case_ produce the final result, because
i must then output 6 columns : xn1, xn2, xn3, xn4, xn5, xn6 and the
n1 to n5 are the fathers only.

The import_societe table contains just the values from the tree that
are used after an "import".

******** Script of the tables :
//
CREATE TABLE "CONTRAT_SOCIETE"
(
        "COSO_ID" Fixed (10,0) NOT NULL,
        "COGR_ID" Fixed (10,0) NOT NULL,
        "COSO_CODESOC" Varchar (100) ASCII NOT NULL,
        "COSO_LIB" Varchar (100) ASCII NOT NULL,
        "COSO_IDENT_ET" Varchar (100) ASCII,
        "COSO_IDENT_ET_PR" Varchar (100) ASCII,
        "PAY_CODE" Char (3) ASCII,
        "COSO_NIV" Fixed (10,0) NOT NULL,
        "COSO_BG" Fixed (10,0) NOT NULL,
        "COSO_BD" Fixed (10,0) NOT NULL,
        "COSO_DTCRE" Timestamp DEFAULT TIMESTAMP,
        "COSO_DTMOD" Timestamp,
        PRIMARY KEY ("COSO_ID")
)
//
DATALOAD TABLE "CONTRAT_SOCIETE"
"COSO_ID" 1 CHAR
"COGR_ID" 2 CHAR
"COSO_CODESOC" 3 CHAR
"COSO_LIB" 4 CHAR
"COSO_IDENT_ET" 5 CHAR DEFAULT NULL
"COSO_IDENT_ET_PR" 6 CHAR DEFAULT NULL
"PAY_CODE" 7 CHAR DEFAULT NULL
"COSO_NIV" 8 CHAR
"COSO_BG" 9 CHAR
"COSO_BD" 10 CHAR
"COSO_DTCRE" 11 CHAR DEFAULT NULL
"COSO_DTMOD" 12 CHAR DEFAULT NULL
INSTREAM FILE 'zCOSO.data'
 COMPRESSED ASCII
DECIMAL '//./'
SEPARATOR ';'
DELIMITER ''
DATE INTERNAL
TIME INTERNAL
TIMESTAMP INTERNAL
NULL '? '
BOOLEAN 'TRUE/FALSE'
//
CREATE INDEX "IND_COSO_FK_COGR_ID" ON "CONTRAT_SOCIETE" ("COGR_ID" ASC )
//
CREATE INDEX "IND_COSO_FK_PAY_CODE" ON "CONTRAT_SOCIETE" ("PAY_CODE" ASC )
//
CREATE INDEX "IND_COSO_FK_COSO_NIV" ON "CONTRAT_SOCIETE" ("COSO_NIV" ASC )
//
CREATE INDEX "IND_COSO_FK_COSO_BG" ON "CONTRAT_SOCIETE" ("COSO_BG" ASC )
//
CREATE INDEX "IND_COSO_FK_COSO_BD" ON "CONTRAT_SOCIETE" ("COSO_BD" ASC )
//
CREATE TABLE "IMPORT_SOCIETE"
(
        "IMSO_ID" Fixed (10,0) NOT NULL,
        "IMGR_ID" Fixed (10,0) NOT NULL,
        "COSO_ID" Fixed (10,0) NOT NULL,
        "IMSO_DTCRE" Timestamp DEFAULT TIMESTAMP,
        "IMSO_DTMOD" Timestamp,
        PRIMARY KEY ("IMSO_ID")
)
//
DATALOAD TABLE "IMPORT_SOCIETE"
"IMSO_ID" 1 CHAR
"IMGR_ID" 2 CHAR
"COSO_ID" 3 CHAR
"IMSO_DTCRE" 4 CHAR DEFAULT NULL
"IMSO_DTMOD" 5 CHAR DEFAULT NULL
INSTREAM FILE 'zIMSO.data'
 COMPRESSED ASCII
DECIMAL '//./'
SEPARATOR ';'
DELIMITER ''
DATE INTERNAL
TIME INTERNAL
TIMESTAMP INTERNAL
NULL '? '
BOOLEAN 'TRUE/FALSE'
//
CREATE INDEX "IND_IMSO_FK_IMGR_ID" ON "IMPORT_SOCIETE" ("IMGR_ID" ASC )
//
CREATE INDEX "IND_IMSO_FK_COSO_ID" ON "IMPORT_SOCIETE" ("COSO_ID" ASC )
//

Please, can you reproduce the problem and do you have any idea about
this problem ?

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]