AW: JOIN on FIXED

Please provide output of

EXPLAIN
SELECT
    unit.*
FROM
     RESOURCE_ACTIVITY a
     inner join COMPANY_HR_ROLE chrr on
(a.E_CHRR_K_HUMAN_RESOURCE=chrr.E_COMP_HR_K_HUMAN_RESOURCE
                                                                     AND
a.E_CHRR_K_COMPANY=chrr.E_COMP_HR_K_COMPANY
                                                                     AND
a.E_CHRR_D_START_EMP=chrr.E_COMP_HR_D_START)
     inner join UNIT_DIVISION ud on
(chrr.E_U_DIV_K_UNIT_DIVISION=ud.K_UNIT_DIVISION)
     inner join UNIT_DIVISION_NAME unit on
(ud.k_unit_division=unit.E_U_DIV_K_UNIT_DIVISION)
WHERE
    a.K_RESOURCE_ACTIVITY=502

for further anylysis. Execute this EXPLAIN with different column definitions 
(FIXED(6,0) and FIXED(9,0))

Gert

-----Ursprüngliche Nachricht-----
Von: Fabio Pinotti [mailto:fabio.pinotti@xxxxxxxxx] 
Gesendet: Dienstag, 22. Februar 2005 17:19
An: Zabach, Elke; maxdb@xxxxxxxxxxxxxxx
Betreff: Re: JOIN on FIXED


Sorry, i forgot to provide some important informations.
I'm using MaxDb 7.5.0.23.

The query is (run in Oracle mode):

SELECT
    unit.*
FROM
     RESOURCE_ACTIVITY a
     inner join COMPANY_HR_ROLE chrr on
(a.E_CHRR_K_HUMAN_RESOURCE=chrr.E_COMP_HR_K_HUMAN_RESOURCE
                                                                     AND
a.E_CHRR_K_COMPANY=chrr.E_COMP_HR_K_COMPANY
                                                                     AND
a.E_CHRR_D_START_EMP=chrr.E_COMP_HR_D_START)
     inner join UNIT_DIVISION ud on
(chrr.E_U_DIV_K_UNIT_DIVISION=ud.K_UNIT_DIVISION)
     inner join UNIT_DIVISION_NAME unit on
(ud.k_unit_division=unit.E_U_DIV_K_UNIT_DIVISION)
WHERE
    a.K_RESOURCE_ACTIVITY=502


Here are definitions of the four tables:

CREATE TABLE "ELPVA"."RESOURCE_ACTIVITY"
(
 "K_RESOURCE_ACTIVITY"               Fixed (9,0)    NOT NULL,
 "E_ACT_K_ACTIVITY"               Fixed (9,0)    NOT NULL,
 "E_ROLE_TYP_K_ROLE_TYPE"               Fixed (6,0)    NOT NULL,
 "D_START"               Timestamp    NOT NULL,
 "O_MODIFY"               Timestamp    NOT NULL,
 "E_OPERATOR_K_OPERATOR"               Varchar (20) ASCII    NOT NULL,
 "E_CHRR_K_HUMAN_RESOURCE"               Fixed (9,0),
 "E_CHRR_K_COMPANY"               Fixed (9,0),
 "E_CHRR_D_START_EMP"               Timestamp,
 "E_CHRR_D_START"               Timestamp,
 "E_CHRR_K_UNIT_DIVISION"               Fixed (9,0),
 "E_CHRR_K_ROLE_TYPE"               Fixed (6,0),
 "E_COMPANY_K_COMPANY"               Fixed (9,0),
 "C_NOTE"               Varchar (250) ASCII,
 "K_PROG"               Fixed (3,0),
 "D_END"               Timestamp,
 "E_HUM_RES_K_HUMAN_RESOURCE"               Fixed (9,0),
 "E_U_DIV_K_UNIT_DIVISION"               Fixed (9,0),
 "E_PTY_K_PARTY"               Fixed (9,0)
)


CREATE TABLE "ELPVA"."COMPANY_HR_ROLE"
(
 "E_COMP_HR_K_HUMAN_RESOURCE"               Fixed (9,0)    NOT NULL,
 "E_COMP_HR_K_COMPANY"               Fixed (9,0)    NOT NULL,
 "E_COMP_HR_D_START"               Timestamp    NOT NULL,
 "E_ROLE_TYP_K_ROLE_TYPE"               Fixed (6,0)    NOT NULL,
 "D_START"               Timestamp    NOT NULL,
 "O_MODIFY"               Timestamp    NOT NULL,
 "E_OPERATOR_K_OPERATOR"               Varchar (20) ASCII    NOT NULL,
 "E_U_DIV_K_UNIT_DIVISION"               Fixed (6,0),
 "C_NOTE"               Varchar (250) ASCII,
 "K_PROG"               Fixed (3,0),
 "D_END"               Timestamp
)


CREATE TABLE "ELPVA"."UNIT_DIVISION"
(
 "K_UNIT_DIVISION"               Fixed (9,0)    NOT NULL,
 "E_COMPANY_K_COMPANY"               Fixed (9,0)    NOT NULL,
 "D_START"               Timestamp    NOT NULL,
 "O_MODIFY"               Timestamp    NOT NULL,
 "E_OPERATOR_K_OPERATOR"               Varchar (20) ASCII    NOT NULL,
 "E_U_DIV_K_UNIT_DIVISION"               Fixed (9,0),
 "E_U_DIV_K_UNIT_DIVISION_NEW"               Fixed (9,0),
 "E_UD_TYP_K_UNIT_DIVISION_TYPE"               Fixed (6,0),
 "C_NOTE"               Varchar (250) ASCII,
 "D_END"               Timestamp,
 "C_TREE_PATH"               Varchar (1000) ASCII
)



CREATE TABLE "ELPVA"."UNIT_DIVISION_NAME"
(
 "N_UNIT_DIVISION"               Varchar (100) ASCII    NOT NULL,
 "E_U_DIV_K_UNIT_DIVISION"               Fixed (9,0)    NOT NULL,
 "K_PROG"               Fixed (3,0)    NOT NULL,
 "D_START"               Timestamp    NOT NULL,
 "O_MODIFY"               Timestamp    NOT NULL,
 "E_OPERATOR_K_OPERATOR"               Varchar (20) ASCII    NOT NULL,
 "N_SHORT_UNIT_DIVISION"               Varchar (20) ASCII,
 "C_NOTE"               Varchar (250) ASCII,
 "D_END"               Timestamp,
 PRIMARY KEY ("E_U_DIV_K_UNIT_DIVISION", "K_PROG")
)


Running the query with these definitions, return no result.
But changing  COMPANY_HR_ROLE.E_U_DIV_K_UNIT_DIVISION to Fixed(9,0), makes
it work.

Tables were created in one unique step (in detail, columns used by join were
not added or altered later)

About parameters, I don't exaclty know what you need: can you tell me in
detail what I should look for?

Thanks in advance
Fabio

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx