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: 53
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: 1278
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: 1625
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: 53
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm


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