Hi, the output for FIXED(9,0) is
OWNER TABLENAME COLUMN_OR_INDEX
STRATEGY PAGE_COUNT
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--
A
IDX_RESOURCE_ACTIVITY_1 EQUAL CONDITION FOR INDEX
2
CHRR
IDX_COMPANY_HR_ROLE_1 JOIN VIA MULTIPLE INDEXED COLUMNS
1
E_COMP_HR_K_HUMAN_RESOURCE (USED INDEX COLUMN)
E_COMP_HR_K_COMPANY (USED INDEX COLUMN)
UD
IDX_UNIT_DIVISION_2 JOIN VIA INDEXED COLUMN
1
UNIT
E_U_DIV_K_UNIT_DIVISION JOIN VIA KEY RANGE
1
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED , COSTVALUE IS 7
The output for FIXED(6,0) is exactly the same.
To make this last test, I altered the column from FIXED(9,0) to FIXED(6,0),
but now query returns correct result in both cases.
I really don't understant what's going on.
Thanks
Fabio
----- Original Message -----
From: "Grossmann, Gert" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Wednesday, February 23, 2005 7:49 AM
Subject: 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:[EMAIL PROTECTED]
Gesendet: Dienstag, 22. Februar 2005 17:19
An: Zabach, Elke; [email protected]
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/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
