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
 

 

DB2 join problem

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

New User


Joined: 21 Jan 2012
Posts: 58
Location: India

PostPosted: Thu Apr 10, 2014 5:55 pm    Post subject: DB2 join problem
Reply with quote

Hi ,

I have requirement to join 4 tables as below:

Retreive those records from Auth_table where
PART_NO in AUTH_TABLE matches with ARCH_NO +1 retrieved from the ARCH_TABLE where UPDATE_TIMESTAMP is maximum value. and BRANCH_NO in AUTH_TABLE matches with SORT_NO in SORT_TABLE and SRC_ID in DATE_TABLE is equal to = MYSORE. and LOG_TIMESTAMP in AUTH_TABLE is between FROM_TIMSTAMP and TO_TIMESTAMP from DATE_TABLE.

Problem is I am getting duplicate records from the AUTH_TABLE. Can you please help me?

Code:

SELECT A.* FROM
 CA.AUTH_TABLE A
 CA.SORT_TABLE B
 CA.DATE_TABLE C
 CA.ARCH_TABLE D
WHERE (A.PART_NO = D.ARCH_NO + 1 AND
         D.UPDATE_TIMESTAMP = SELECT MAX(UPDATE_TIMESTAMP)
                                          FROM CA.ARCH_TABLE))
    AND A.BRANCH_NO = B.SORT_CODE     
    AND C.SRC_ID = 'MYSORE'
    AND (A.LOG-TIMESTAMP > C.FROM_TIMESTAMP AND
            A.LOG_TIMESTAMP <+ C.TO_TIMESTAMP)

Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Apr 10, 2014 7:03 pm    Post subject:
Reply with quote

Well you need to give some info about uniqueness :
split your query in 3 different ones , and see where the duplication occurs :

which of the following queries returns rows ?
Code:
select PART_NO, count(*) from CA.AUTH_TABLE A, CA.ARCH_TABLE D
where D.UPDATE_TIMESTAMP = SELECT MAX(d2.UPDATE_TIMESTAMP)   
                                FROM CA.ARCH_TABLE D2))
    and A.PART_NO = D.ARCH_NO + 1
group by part_no
having count(*) > 1


Code:
select PART_NO, count(*) from CA.AUTH_TABLE A, CA.SORT_TABLE B
where A.BRANCH_NO = B.SORT_CODE
group by part_no
having count(*) > 1


Code:
select PART_NO, count(*) from CA.AUTH_TABLE A, CA.DATE_TABLE C
where C.SRC_ID = 'MYSORE'
AND (A.LOG-TIMESTAMP > C.FROM_TIMESTAMP AND
        A.LOG_TIMESTAMP <+ C.TO_TIMESTAMP) 
group by part_no
having count(*) > 1
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1736
Location: NY,USA

PostPosted: Fri Apr 11, 2014 6:07 am    Post subject:
Reply with quote

GuyC has suggested this which would save your time.

Chetan, the generic way is to start commenting out each join may be starting from the bottom and see which join is giving you duplicates.
Back to top
View user's profile Send private message
chetanambi

New User


Joined: 21 Jan 2012
Posts: 58
Location: India

PostPosted: Fri Apr 11, 2014 12:00 pm    Post subject:
Reply with quote

Thanks GuyC and Rohit for your suggestion.

As suggested by GuyC I have split the query into 3 parts and found 2nd query is returning duplicates because of data issue in SORT_CODE column. It was having invalid value zero which was causing the issue. This issue will be in test region but in prodcution this is not the case. So for testing purposeI have modified query as below and I am getting correct results now:

Code:

SELECT A.* FROM
 CA.AUTH_TABLE A
 CA.SORT_TABLE B
 CA.DATE_TABLE C
 CA.ARCH_TABLE D
WHERE (A.PART_NO = D.ARCH_NO + 1 AND
         D.UPDATE_TIMESTAMP = SELECT MAX(UPDATE_TIMESTAMP)
                                          FROM CA.ARCH_TABLE))
    AND A.BRANCH_NO = B.SORT_CODE     
    AND B.SORT_CODE   > 0 
    AND C.SRC_ID = 'MYSORE'
    AND (A.LOG-TIMESTAMP > C.FROM_TIMESTAMP AND
            A.LOG_TIMESTAMP <+ C.TO_TIMESTAMP)[
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 Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 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
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us