|
View previous topic :: View next topic
|
| Author |
Message |
chetanambi
New User
Joined: 21 Jan 2012
Posts: 58
Location: India
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
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 |
|
|
chetanambi
New User
Joined: 21 Jan 2012
Posts: 58
Location: India
|
|
|
|
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 |
|
|
|
|
