|
View previous topic :: View next topic
|
| Author |
Message |
banand
New User
Joined: 05 Jun 2007
Posts: 28
Location: Mumbai
|
|
|
|
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 |
|
 |
muthuvel
Active User
Joined: 29 Nov 2005
Posts: 217
Location: Canada
|
|
|
|
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 |
|
|
muthuvel
Active User
Joined: 29 Nov 2005
Posts: 217
Location: Canada
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
banand
New User
Joined: 05 Jun 2007
Posts: 28
Location: Mumbai
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| 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 |
|
|
banand
New User
Joined: 05 Jun 2007
Posts: 28
Location: Mumbai
|
|
|
|
Hi,
I got the answer from the above itself .
Sorry for the inconvenience.
Thanks A lot |
|
| Back to top |
|
|
|
|
