Joins on columns with different data types

Chris Flink

Joins on columns with different data types
I have some SQL that is joining 2 tables on columns with different data
types. The columns are str_no defined as smallint and str_cd defined as
char(4). The SQL looks like this...

SELECT
STR_NO,
FAC_ID,
ITM_NO,
TPR_RTL_UNITS,
TPR_RTL_PRICE,
TXN_STATUS,
TPR_BEG_DT,
TPR_END_DT,
RTL_VNDR_CD
FROM DB2T.DB2TDBA.TPR_TRANS_TBL,
DB2T.DB2TDBA.RIM_STR_CNTRL
WHERE STR_NO = CAST(STR_CD AS SMALLINT)

This works but is pretty slow due to the casted value being nonindexable, I
think . Is there a way to code this so the query will use indexes?







**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please destroy
and notify the sender by return mail.
**********************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Bell

Re: Joins on columns with different data types
(in response to Chris Flink)
Your choices are
1. wait for V8 (V8 will make this indexable)
2. force view materialization on the table with the cast - this will do the
conversion at the front end and then do the join with the index.
View materialization is one of those things everyone says is a performance
problem until you try to do something like this. requirements are sufficient
space in the work space for this plus the rest of your normal sorts, etc and
it will still run longer than 2 tables with matching data types.
3. add a new column STR_CD_SMALLINT or something to the problem table and
update it with the cast value. If you can't setup the maintenance to
maintain it, you will need to rerun the update each time. good case for a
trigger.

Mike Bell
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of [login to unmask email]
Sent: Wednesday, January 07, 2004 10:28 AM
To: [login to unmask email]
Subject: Joins on columns with different data types


I have some SQL that is joining 2 tables on columns with different data
types. The columns are str_no defined as smallint and str_cd defined as
char(4). The SQL looks like this...

SELECT
STR_NO,
FAC_ID,
ITM_NO,
TPR_RTL_UNITS,
TPR_RTL_PRICE,
TXN_STATUS,
TPR_BEG_DT,
TPR_END_DT,
RTL_VNDR_CD
FROM DB2T.DB2TDBA.TPR_TRANS_TBL,
DB2T.DB2TDBA.RIM_STR_CNTRL
WHERE STR_NO = CAST(STR_CD AS SMALLINT)

This works but is pretty slow due to the casted value being nonindexable, I
think . Is there a way to code this so the query will use indexes?







**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please destroy
and notify the sender by return mail.
**********************************************************************

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can
be reached at [login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 10/22/2003

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 10/22/2003

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: Joins on columns with different data types
(in response to Mike Bell)
One trick you could use would be to add a column to one of the two tables that stores the data in the same representation as the other table. This new column could be populated by an insert trigger or you change the application which performs the inserts. Then you join on the like data types.
Another option would be to use a materialized expression. This is only helpful, when you would be materializing a small resultset and not the SQL you provided below. Please try this though and see how it works for you. It could, also, be switched around and you nest the other table if that is the one you have indexable input.
SELECT
STR_NO,
FAC_ID,
ITM_NO,
TPR_RTL_UNITS,
TPR_RTL_PRICE,
TXN_STATUS,
TPR_BEG_DT,
TPR_END_DT,
RTL_VNDR_CD
FROM DB2T.DB2TDBA.TPR_TRANS_TBL,
,TABLE(SELECT CAST(STR_CD AS SMALLINT) AS STR_CD
, other cols....
FROM DB2T.DB2TDBA.RIM_STR_CNTRL
WHERE COL = ????
GROUP BY STR_CD, other cols...) AS A
WHERE STR_NO = STR_CD



Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 1/7/04 11:28:21 AM >>>
I have some SQL that is joining 2 tables on columns with different data
types. The columns are str_no defined as smallint and str_cd defined as
char(4). The SQL looks like this...

SELECT
STR_NO,
FAC_ID,
ITM_NO,
TPR_RTL_UNITS,
TPR_RTL_PRICE,
TXN_STATUS,
TPR_BEG_DT,
TPR_END_DT,
RTL_VNDR_CD
FROM DB2T.DB2TDBA.TPR_TRANS_TBL,
DB2T.DB2TDBA.RIM_STR_CNTRL
WHERE STR_NO = CAST(STR_CD AS SMALLINT)

This works but is pretty slow due to the casted value being nonindexable, I
think . Is there a way to code this so the query will use indexes?







**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please destroy
and notify the sender by return mail.
**********************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NOMAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm