View previous topic :: View next topic
|
Author |
Message |
prasun dhara
New User
Joined: 01 Mar 2008 Posts: 49 Location: kolkata
|
|
|
|
Requiremnt :
Need to find out all the event no whose first 4 bytes are same but last 2 bytes are different
Table A
Code: |
event no
123455
223455
223455
123456
672744
672746
|
Expected out put
Code: |
123455
123456
672744
672746 |
We have written a query BUT THIS IS GETTING TIME OUT abend
Code: |
SELECT DISTINCT A.EVN FROM DB2.TABLEA A
WHERE EXISTS
( SELECT 1 FROM DB2.TABLEA B
WHERE A.EVN BETWEEN B.EVN AND LEFT(B.EVN,4)||'99'
AND A.EVN > B.EVN)
WITH UR; |
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
can't test, but:
Code: |
SELECT A.EVN
FROM TABLE A
,TABLE B
WHERE LEFT(A.EVN,4) = LEFT(B.EVN,4)
AND A.EVN <> B.EVN
GROUP BY A.EVN |
|
|
Back to top |
|
|
prasun dhara
New User
Joined: 01 Mar 2008 Posts: 49 Location: kolkata
|
|
|
|
Cost of query is even more for this.
Code: |
SELECT A.EVN
FROM TABLE A
,TABLE B
WHERE LEFT(A.EVN,4) = LEFT(B.EVN,4)
AND A.EVN <> B.EVN
GROUP BY A.EVN
|
Can you please suggest some other for which cost of query will be less other wise job will get ebended with timeout |
|
Back to top |
|
|
|