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





----- Original Message ----- 
From: "Zabach, Elke" <elke.zabach@xxxxxxx>
To: "Fabio Pinotti" <fabio.pinotti@xxxxxxxxx>; <maxdb@xxxxxxxxxxxxxxx>
Sent: Tuesday, February 22, 2005 4:28 PM
Subject: AW: JOIN on FIXED


Fabio Pinotti wrote
>
> Hi, I've encountered a strange behaviour with join on columns of
different
> data type.
> I had a Fixed(9,0) column in table A and a Fixed(6,0) column in table
B,
> with the same numerical value. But the join between the tables
returned no
> result.
> Only upgrading the fixed(6,0) column in table B to data type
fixed(9,0),
> solved the problem.
>
> Is this a known bug?

Which version are you using?
How does the explain for the select look like?
How does the select look like?
Please provide the table definitions of all tables concerned plus the
index-definitions to see which part of primary/secondary key was formed
by this column.
Did you add that column after the first creation of the table or was it
part of the table creation?
Please provide the installation parameter values (if I knew the version
you are using, I could tell you what I am really interested in).
Did you change some kernel-settings after contact with one of my
colleagues, especially some join/optimize-settings?

Elke
SAP Labs Berlin

>
> Best regards
> Fabio


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