Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Join with Max function

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

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Thu Dec 18, 2008 9:43 am    Post subject: Join with Max function
Reply with quote

HI

Below I have given query with example . Please help me to solve the problem

I have two table Table A and Table B

Table A
---------

Prod_type_Cd Trans_type
NA01 T
NA02 T
NA03 T
NA04 T


Table B
------------

Prod_Type_Cd acct_no Rank

NA01 0001 3
NA01 0002 4
NA01 0002 5
NA01 0003 4
NA01 0003 6

For each Prod_type_cd in Table A there will multiple Acct_no in table b.

Now I need to Join Table A with the Table B on Prod Type cd
and also I need to pick only the MAX rank for each acct_no

Finally I need the below result

Prod_type_Cd Trans_type acct_no Rank
NA01 T 0001 3
NA01 T 0002 5
NA01 T 0003 6
NA02 T ------ -
NA03 T ------ -
NA04 T ------ -

I have used the below quey
SELECT A.prod_type_Cd,
B.ACCT_NO, B.Rank
FROM ACE_PROCES_DEF A
LEFT outer join
ACE_CLC_HDR B
on a.prod_type_cd = b.prod_type_cd
WHERE B.Rank=(SELECT MAX(Rank)
FROM ACE_CLC_HDR GROUP BY PROD_TYPE_CD,ACCT_NO
)

but it is giving the below results

Prod_type_Cd Trans_type acct_no Rank
NA01 T 0001 3
NA01 T 0002 5
NA01 T 0003 6
Back to top
View user's profile Send private message

muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Thu Dec 18, 2008 11:13 am    Post subject:
Reply with quote

The reason for not getting the below in output

Code:
NA02 T ------ -
NA03 T ------ -
NA04 T ------ -


Because a.prod_type_cd = b.prod_type_cd NA02,NA03,NA04 are not present in table A.So the query is not selecting those in table b alone
Back to top
View user's profile Send private message
muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Thu Dec 18, 2008 11:24 am    Post subject:
Reply with quote

Further analysis,
Quote:
FROM ACE_PROCES_DEF A
LEFT outer join
ACE_CLC_HDR B


So It left joins Table A to Table B meaning Table A side data cannot be null and Table B side data can be null. If you want the reverse,use RIGHT OUTER JOIN.Or you want NULLS on both sides to be available ,use FULL OUTER JOIN
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Thu Dec 18, 2008 11:44 am    Post subject:
Reply with quote

banand,

Try this one.
Code:
  SELECT  A.PROD_TYPE_CD                 
         ,A.TRANS_TYPE                   
         ,B.ACCT_NO                     
         ,MAX(B.RANK)                   
    FROM  ACE_PROCES_DEF A           
    LEFT OUTER JOIN                     
          ACE_CLC_HDR    B           
      ON  A.PROD_TYPE_CD = B.PROD_TYPE_CD
GROUP BY  A.PROD_TYPE_CD                 
         ,A.TRANS_TYPE                   
         ,B.ACCT_NO                     
 WITH UR;
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Fri Dec 19, 2008 9:35 am    Post subject:
Reply with quote

Thanks a lot for quick response,

It is working fine, but I can't add the field Trans_Type in the group by clause because i need to take max rank only based on the Prod_type_cd and Acct_no.

Please let me know if any other query solve this problem.

Thanks
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Fri Dec 19, 2008 9:48 am    Post subject:
Reply with quote

Quote:
It is working fine, but I can't add the field Trans_Type in the group by clause because i need to take max rank only based on the Prod_type_cd and Acct_no.
banand,

Can you be a little more clear. It works fine for the example given. May be you need to provide a better example of input and output rows to further explain the above statement.
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Tue Dec 30, 2008 10:06 pm    Post subject:
Reply with quote

Hi,

I got the answer from the above itself .
Sorry for the inconvenience.
Thanks A lot
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 Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us