How Can I Join These Tables?

dvdaddict32 · #1 (**permalink**) 12-02-11, 09:38

I have two tables with matching field data, but I can't do a direct inner join because the fields are different data types.

My fields to join are:

folderid - varchar
mbr_ssn_nbr - integer

here is a look at my query:

Code:

SELECT   C.MBR_F_NM,
         C.MBR_L_NM,
         A.FOLDID,
         B.USERID,
         B.TIMECRTE,
         B.SHRTDESC,
         B.NOTETEXT
         
         
FROM     DSNP.EYPTFOLD01 A,
         DSNP.EYPTNOTE01 B,
         DSNP.PR01_T_MBR C
         



         
WHERE    A.CLUSTID=B.CLUSTID
AND      A.FOLDID=C.MBR_SSN_NBR

DB2 complains about the datatypes not matching. Can anyone help?

tonkuma · #2 (**permalink**) 12-02-11, 09:55

Quote:

folderid - varchar
mbr_ssn_nbr - integer

Cast explicitly to make datatypes be compatible, depending on what values in folderid and how to match each other.

For example:
1) a.folderid = VARCHAR(c.mbr_ssn_nbr)
If you are using older version of DB2, it may not accept VARCHAR(c.mbr_ssn_nbr).
If so, try "a.folderid = CHAR(c.mbr_ssn_nbr)"

2) INTEGER(a.folderid) = c.mbr_ssn_nbr

r937 · #3 (**permalink**) 12-02-11, 09:58

how come your problem is DB2 here, but SQL Server on another forum?

SQL Server's CONVERT function won't work in DB2, will it?

use CAST instead

dvdaddict32 · #4 (**permalink**) 12-02-11, 10:11

Quote:

Originally Posted by r937

how come your problem is DB2 here, but SQL Server on another forum?

SQL Server's CONVERT function won't work in DB2, will it?

use CAST instead

LOl my background is SQL, and I'm a lot more comfortable with it than DB2 so I post there first. Most of the time SQL code works just fine with DB2. I couldn't get convert() to work though.

dvdaddict32 · #5 (**permalink**) 12-02-11, 10:14

Quote:

Originally Posted by tonkuma

Cast explicitly to make datatypes be compatible, depending on what values in folderid and how to match each other.

For example:
1) a.folderid = VARCHAR(c.mbr_ssn_nbr)
If you are using older version of DB2, it may not accept VARCHAR(c.mbr_ssn_nbr).
If so, try "a.folderid = CHAR(c.mbr_ssn_nbr)"

2) INTEGER(a.folderid) = c.mbr_ssn_nbr

That worked great. Thanks so much!

Peter.Vanroose · #6 (**permalink**) 12-02-11, 12:32

Quote:

Originally Posted by tonkuma

Cast explicitly to make datatypes compatible

Indeed; but beware that such a join may have very bad performance!
Consider creating an index on expression on the CAST expression and verify with EXPLAIN what's the performance difference.

dav1mo · #7 (**permalink**) 12-02-11, 13:08

to add on to Peter's suggestion, perform the CAST on the first accesed table, so as to get index access on the other. For instance, if access path chooses table A as the first accessed table, then cast the column in the A table to the datatype of the B table, to get index access on the B table or vice versa.
Dave Nance

tonkuma · #8 (**permalink**) 12-04-11, 08:47

Although OP might use DB2 for z/OS (because schema DSNP was used),
I tested on DB2 9.7.5 for Windows.

Example 1: CAST to INTEGER
Both indexes(for A.FOLDID and C.MBR_SSN_NBR) were used.

Code:

SELECT C.MBR_F_NM
     , C.MBR_L_NM
     , A.FOLDID
     /* COUNT(*) count_rows */
 FROM  DSNP.PR01_T_MBR C
 INNER JOIN
       DSNP.EYPTFOLD01 A
/* ON          A.FOLDID  = VARCHAR(C.MBR_SSN_NBR) */
   ON  INTEGER(A.FOLDID) =         C.MBR_SSN_NBR
 FETCH FIRST 10 ROWS ONLY
;

Code:

Original Statement:
------------------
SELECT C.MBR_F_NM , C.MBR_L_NM , A.FOLDID /* COUNT(*) count_rows */
FROM DSNP.PR01_T_MBR C INNER JOIN DSNP.EYPTFOLD01 A /* ON A.FOLDID =
        VARCHAR(C.MBR_SSN_NBR) */ ON INTEGER(A.FOLDID) = C.MBR_SSN_NBR
FETCH FIRST 10 ROWS ONLY


Optimized Statement:
-------------------
SELECT Q2.MBR_F_NM AS "MBR_F_NM", Q2.MBR_L_NM AS "MBR_L_NM", Q1.FOLDID AS
        "FOLDID"
FROM DSNP.EYPTFOLD01 AS Q1, DSNP.PR01_T_MBR AS Q2
WHERE (INTEGER(Q1.FOLDID) = Q2.MBR_SSN_NBR)

Access Plan:
-----------
        Total Cost:             105.118
        Query Degree:           1

                 Rows
                RETURN
                (   1)
                 Cost
                  I/O
                  |
                  10
                NLJOIN
                (   2)
                105.118
                  11
          /-------+-------\
       1000                  1
      IXSCAN              FETCH
      (   3)              (   4)
      16.3861             15.1706
         2                   2
        |               /---+----\
       1000            1          1000
  INDEX: DSNP       IXSCAN   TABLE: DSNP
 EYPTFOLD01_FOLDID  (   5)     PR01_T_MBR
        Q1          7.60009        Q2
                       1
                      |
                     1000
                INDEX: SYSIBM
              SQL111204213435800
                      Q2

Example 2: CAST to VARCHAR
Index for A.FOLDID was used.
Index for C.MBR_SSN_NBR was not used.

Code:

SELECT C.MBR_F_NM
     , C.MBR_L_NM
     , A.FOLDID
     /* COUNT(*) count_rows */
 FROM  DSNP.PR01_T_MBR C
 INNER JOIN
       DSNP.EYPTFOLD01 A
   ON          A.FOLDID  = VARCHAR(C.MBR_SSN_NBR)
/* ON  INTEGER(A.FOLDID) =         C.MBR_SSN_NBR */
 FETCH FIRST 10 ROWS ONLY
;

Code:

Original Statement:
------------------
SELECT C.MBR_F_NM , C.MBR_L_NM , A.FOLDID /* COUNT(*) count_rows */
FROM DSNP.PR01_T_MBR C INNER JOIN DSNP.EYPTFOLD01 A ON A.FOLDID =
        VARCHAR(C.MBR_SSN_NBR) /* ON INTEGER(A.FOLDID) = C.MBR_SSN_NBR */
FETCH FIRST 10 ROWS ONLY


Optimized Statement:
-------------------
SELECT Q2.MBR_F_NM AS "MBR_F_NM", Q2.MBR_L_NM AS "MBR_L_NM", Q1.FOLDID AS
        "FOLDID"
FROM DSNP.EYPTFOLD01 AS Q1, DSNP.PR01_T_MBR AS Q2
WHERE (Q1.FOLDID = VARCHAR(Q2.MBR_SSN_NBR))

Access Plan:
-----------
        Total Cost:             91.007
        Query Degree:           1

               Rows
              RETURN
              (   1)
               Cost
                I/O
                |
                10
              NLJOIN
              (   2)
              91.007
                 8
         /------+------\
      1000             13.6986
     TBSCAN            IXSCAN
     (   3)            (   4)
     39.094            7.61673
        5                 1
       |                 |
      1000              1000
 TABLE: DSNP       INDEX: DSNP
   PR01_T_MBR     EYPTFOLD01_FOLDID
       Q2                Q1

Example 3: CAST to VARCHAR. Requested result was COUNT(*) only.
Both indexes were used.

Code:

------------------------------ Commands Entered ------------------------------
SELECT COUNT(*) count_rows
 FROM  DSNP.PR01_T_MBR C
 INNER JOIN
       DSNP.EYPTFOLD01 A
   ON          A.FOLDID  = VARCHAR(C.MBR_SSN_NBR)
;
------------------------------------------------------------------------------

COUNT_ROWS 
-----------
       1000

  1 record(s) selected.

Code:

Original Statement:
------------------
SELECT COUNT(*) count_rows
FROM DSNP.PR01_T_MBR C INNER JOIN DSNP.EYPTFOLD01 A ON A.FOLDID =
        VARCHAR(C.MBR_SSN_NBR)


Optimized Statement:
-------------------
SELECT Q4.$C0 AS "COUNT_ROWS"
FROM
   (SELECT COUNT(*)
   FROM
      (SELECT $RID$
      FROM DSNP.EYPTFOLD01 AS Q1, DSNP.PR01_T_MBR AS Q2
      WHERE (Q1.FOLDID = VARCHAR(Q2.MBR_SSN_NBR))) AS Q3) AS Q4

Access Plan:
-----------
        Total Cost:             78.2955
        Query Degree:           1

                 Rows
                RETURN
                (   1)
                 Cost
                  I/O
                  |
                   1
                GRPBY
                (   2)
                78.2949
                   6
                  |
                13698.6
                NLJOIN
                (   3)
                75.8683
                   6
           /------+-------\
        1000              13.6986
       IXSCAN             IXSCAN
       (   4)             (   5)
       23.9553            7.61673
          3                  1
         |                  |
        1000               1000
   INDEX: SYSIBM      INDEX: DSNP
 SQL111204213435800  EYPTFOLD01_FOLDID
         Q2                 Q1

Code:

******************** EXPLAIN INSTANCE ********************

DB2_VERSION:       09.07.5
SOURCE_NAME:       SYSSH200
SOURCE_SCHEMA:     NULLID
SOURCE_VERSION:
EXPLAIN_TIME:      2011-12-04-xx.xx.xx.xxxxxx
EXPLAIN_REQUESTER: DB2ADMIN

Database Context:
----------------
        Parallelism:          None
        CPU Speed:            7.085164e-007
        Comm Speed:           0
        Buffer Pool size:     10250
        Sort Heap size:       256
        Database Heap size:   600
        Lock List size:       4096
        Maximum Lock List:    22
        Average Applications: 1
        Locks Available:      28835

Package Context:
---------------
        SQL Type:           Dynamic
        Optimization Level: 5
        Blocking:           Block All Cursors
        Isolation Level:    Cursor Stability

Internet Services

Web Development

Digital Marketing

Consumer Tech