Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Join Multiple tables in DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rexx77

New User


Joined: 14 Apr 2008
Posts: 73
Location: Mysore

PostPosted: Mon May 11, 2015 4:57 pm    Post subject: Join Multiple tables in DB2
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: 1712
Location: UK

PostPosted: Mon May 11, 2015 5:21 pm    Post subject:
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: 269
Location: Mumbai

PostPosted: Mon May 11, 2015 7:01 pm    Post subject: Re: Join Multiple tables in DB2
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: 73
Location: Mysore

PostPosted: Wed May 13, 2015 10:55 am    Post subject:
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: 269
Location: Mumbai

PostPosted: Wed May 13, 2015 1:42 pm    Post subject:
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: 73
Location: Mysore

PostPosted: Wed May 13, 2015 3:16 pm    Post subject:
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: 269
Location: Mumbai

PostPosted: Wed May 13, 2015 7:29 pm    Post subject:
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: 73
Location: Mysore

PostPosted: Mon May 18, 2015 5:35 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts Converting multiple VB files to FB fi... Viswanath Reddy JCL & VSAM 6 Mon Aug 08, 2016 11:49 pm
No new posts Execute multiple DB2 Load commands in... faizm DB2 4 Wed Aug 03, 2016 12:53 pm
This topic is locked: you cannot edit posts or make replies. JCL MULTIPLE STEP EXECUTION QUERY Susanta JCL & VSAM 18 Sat Jul 30, 2016 1:17 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us