IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Join Multiple tables in DB2


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rexx77

New User


Joined: 14 Apr 2008
Posts: 78
Location: Mysore

PostPosted: Mon May 11, 2015 4:57 pm
Reply with quote

Hi All,

Greetings!!!

I am working on a requirement which requires data retrieval from 3 different tables. here is the expectation.

1. Table A (Session table)
2. Table B (Physical table)
3. Table C (Physical Table)

I have to inner join Table B and Table C, and for the resultant records i have to join with Table A to retreive the few columns from Table A, Table b and Table c.

here is what i have written

Code:

 SELECT D.Depot
      , D.Tax
      , D.Qty_1
      , D.Qty_2
      , D.Qty 3
      , D.Timestamp
      , SUM(A.Amt1)
      , SUM(A.Amt2)
      , SUM(A.LOAN_Qty)
   FROM SESSION.XXXXX   A

   LEFT OUTER JOIN
     (
        SELECT
        COALESCE(ID,0)
       ,COALESCE(Depot,' ' )
       ,COALESCE(Tax,' ' )
       ,COALESCE(Qty_1,0)
       ,COALESCE(Qty_2,0)
       ,COALESCE(Qty_3,0)
       ,COALESCE(Timestamp,' ' )
       FROM
          Table B 
        , Table C
    WHERE 
      condition 1
      ... condition 4
      )
      AS D

      ON  D.ID         =  A.ID
      AND D.Depot     = A.Depot
      AND D.Tax             =  A.Tax
    GROUP BY D,depot
           , D.tax
            , d.qty_1
       ,d.qty_2
       ,d.qty_3
            , d.Timestamp


i am getting -401 sqlcode, when i checked the reason for that,
Code:

-401   THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT
       COMPARABLE


I need your help in validating the above query, and to resolve the issue i am facing. Any suggestions to make it simpler is also welcome

Thanks in advance.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2448
Location: Hampshire, UK

PostPosted: Mon May 11, 2015 5:21 pm
Reply with quote

You have not supplied us with thr field/column type definitions so how are we expected to help. Check them yourself to make sure that they are compatible with each other.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Mon May 11, 2015 7:01 pm
Reply with quote

rexx77 wrote:

Code:

       COALESCE(Timestamp,' ' )
 




Here I can see one error.

Still you need to check for other fields as well as Nic Suggested

Regards,
Chandan
Back to top
View user's profile Send private message
rexx77

New User


Joined: 14 Apr 2008
Posts: 78
Location: Mysore

PostPosted: Wed May 13, 2015 10:55 am
Reply with quote

Hi,

Below is the field definitions of the table fields
Depot - Char(08)
tax - Decimal(5,3)
Qty1,2,3 - Decimal(18,3)
Timestamp - Timestamp
Amt, Onloan - Decimal(18,3)

I have modified the query like below based on the suggestions
Code:

SELECT D.Depot
       , D.Tax
       , D.Qty_1
       , D.Qty_2
       , D.Qty 3
       , D.Timestamp
       , SUM(A.Amt1)
       , SUM(A.Amt2)
       , SUM(A.LOAN_Qty)
    FROM SESSION.XXXXX   A

    LEFT OUTER JOIN
      (
         SELECT
         COALESCE(ID,0)
        ,COALESCE(Depot,' ' )
        ,COALESCE(Tax,0 )
        ,COALESCE(Qty_1,0)
        ,COALESCE(Qty_2,0)
        ,COALESCE(Qty_3,0)
        ,COALESCE(Timestamp,'0000-00-00-00.00.00.000000' )
        FROM
           Table B 
         , Table C
     WHERE 
       condition 1
       ... condition 4
       )
       AS D

       ON  D.ID         =  A.ID
       AND D.Depot     = A.Depot
       AND D.Tax             =  A.Tax
     GROUP BY D,depot
            , D.tax
             , d.qty_1
        ,d.qty_2
        ,d.qty_3
             , d.Timestamp


Now I am getting -206 sqlcode, the issue is w.r.t D.ID column. The resultant table records of Table B & C is referred as Table D. But I am unable to join the Table D column value with the Session table (Table A) .

Appreciate your solutions
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Wed May 13, 2015 1:42 pm
Reply with quote

Change as below

Code:
SELECT
         COALESCE(ID,0)            AS ID
        ,COALESCE(Depot,' ' )      AS DEPOT
        ,COALESCE(Tax,0 )          AS TAX
        ,COALESCE(Qty_1,0)        AS QTY_1
        ,COALESCE(Qty_2,0)        AS QTY_2
        ,COALESCE(Qty_3,0)       AS QTY_3
        ,COALESCE(Timestamp,'0000-00-00-00.00.00.000000' )  as TIMESTAMP


Regards,
Chandan
Back to top
View user's profile Send private message
rexx77

New User


Joined: 14 Apr 2008
Posts: 78
Location: Mysore

PostPosted: Wed May 13, 2015 3:16 pm
Reply with quote

Thanks Chandan for your suggestion. I have incorporated the changes and able to Open the cursor now.

There is a problem when fetching records, getting sqlcode -305 now for the first column. Ideally after adding COALESCE function there should not be NULL returned from the query. Even added null indicator for the first column (Depot) and tried. But now getting -305 issue for the 2nd column. These columns are not NULL also in the DB2 table.

Can you please advise me?
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Wed May 13, 2015 7:29 pm
Reply with quote

I would suggest you to go through basics of DB2 join..

you are getting null values because of this

Quote:
Code:
SELECT D.Depot
       , D.Tax
       , D.Qty_1
       , D.Qty_2
       , D.Qty 3
       , D.Timestamp


you are selecting columns from Table D which is on right side of left outer join then its pretty much expected to get Null values.

Change your select clause to have columns selected from table A, you will not get null values

Hope it clears your doubts

Regards,
Chandan
Back to top
View user's profile Send private message
rexx77

New User


Joined: 14 Apr 2008
Posts: 78
Location: Mysore

PostPosted: Mon May 18, 2015 5:35 pm
Reply with quote

Thanks Chandan.. I just overlooked that fact when replying in this thread but later corrected that issue and was able to complete the requirement.

Thanks a lot folks for your time.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Creating a single-record file with CO... DFSORT/ICETOOL 8
No new posts Join lines with dfsort DFSORT/ICETOOL 5
No new posts Read 4MB message and split into multi... COBOL Programming 9
No new posts insert into session tables with UR DB2 0
No new posts Sync logic between VSAM files and DB2... COBOL Programming 9
Search our Forums:

Back to Top