Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: How Can I Join These Tables?

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by r937 View Post
    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.

  5. #5
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by tonkuma View Post
    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!

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by tonkuma View Post
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    Last edited by tonkuma; 12-04-11 at 09:55. Reason: Add "*** EXPLAIN INSTANCE ***"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •