|
|
| Author |
Message |
sushanth bobby
Active User
Joined: 29 Jul 2008
Posts: 202
Location: INDIA
|
|
|
|
Hi,
This query Seem to be little tricky for me, somebody solve this puzzle or suggest me something. The query doesn't have any errors. But it takes too much of resource and it backs out. The 2 inner queries are nearly same except the AND condition. I even tried stopped the RESOURCE LIMIT. But its taking infinite time, so i had to terminate the session.
This is the code
| Code: |
SELECT DISTINCT OA.AN_ANNUITANT_TIN
,D.LSWC_POL_STAT_CD
, D.LSWC_POL_NO
, OA.AN_FULL_NAME
FROM INEDATAT.ANNUITANT OA
INNER
JOIN INEDATAT.INET_LSW_CONTRACT D
ON OA.AN_ANNUITANT_TIN IN
(SELECT DISTINCT A.AN_ANNUITANT_TIN
FROM INEDATAT.ANNUITANT A
INNER
JOIN INEDATAT.INET_LSW_CONTRACT D
ON A.AN_ANNUITANT_TIN IN ( SELECT AN_ANNUITANT_TIN FROM
INEDATAT.ANNUITANT GROUP BY AN_ANNUITANT_TIN HAVING COUNT ( * )
= 2 )
INNER
JOIN INEDATAT.POL_ANNUITANT_REL B
ON A.AN_ANNUITANT_KEY = B.AN_ANNUITANT_KEY
AND B.LSWC_POL_NO = D.LSWC_POL_NO
AND (D.LSWC_POL_STAT_CD = 'A' OR D.LSWC_POL_STAT_CD = 'B' OR
D.LSWC_POL_STAT_CD = 'E'))
and
OA.AN_ANNUITANT_TIN IN
(SELECT DISTINCT A.AN_ANNUITANT_TIN
FROM INEDATAT.ANNUITANT A
INNER
JOIN INEDATAT.INET_LSW_CONTRACT D
ON A.AN_ANNUITANT_TIN IN ( SELECT AN_ANNUITANT_TIN FROM
INEDATAT.ANNUITANT GROUP BY AN_ANNUITANT_TIN HAVING COUNT ( * )
= 2 )
INNER
JOIN INEDATAT.POL_ANNUITANT_REL B
ON A.AN_ANNUITANT_KEY = B.AN_ANNUITANT_KEY
AND B.LSWC_POL_NO = D.LSWC_POL_NO
AND (D.LSWC_POL_STAT_CD NOT LIKE 'A' AND
D.LSWC_POL_STAT_CD NOT LIKE 'B' AND
D.LSWC_POL_STAT_CD NOT LIKE 'E'))
;
|
This is the 2 conditions codes in the inner query without these condition codes both the queries are same, alike.
| Code: |
AND (D.LSWC_POL_STAT_CD = 'A' OR
D.LSWC_POL_STAT_CD = 'B' OR
D.LSWC_POL_STAT_CD = 'E') |
AND
| Code: |
AND (D.LSWC_POL_STAT_CD NOT LIKE 'A' AND
D.LSWC_POL_STAT_CD NOT LIKE 'B' AND
D.LSWC_POL_STAT_CD NOT LIKE 'E') |
Query Purpose : A.AN_ANNUITANT_TIN column can have any number of duplicates. The query will return only records having 2 duplicate A.AN_ANNUITANT_TIN. After finding that inner joins are performed.
The result will be having 2 sets of duplicates. Iam not being clear i think.
This is the example,
| Code: |
SELECT A.AN_ANNUITANT_TIN
,D.LSWC_POL_STAT_CD
,D.LSWC_POL_NO
FROM INEDATAT.ANNUITANT A
INNER
JOIN INEDATAT.INET_LSW_CONTRACT D
ON A.AN_ANNUITANT_TIN IN ( SELECT AN_ANNUITANT_TIN FROM
INEDATAT.ANNUITANT GROUP BY AN_ANNUITANT_TIN HAVING COUNT ( * )
= 2 )
INNER
JOIN INEDATAT.POL_ANNUITANT_REL B
ON A.AN_ANNUITANT_KEY = B.AN_ANNUITANT_KEY
AND B.LSWC_POL_NO = D.LSWC_POL_NO
; |
When the above query is executed this is the result.
| Code: |
A.AN_ANNUITANT_TIN LSWC_POL_STAT_CD D.LSWC_POL_NO
002328787 A 1234
002328787 A 5678
002449241 A 1010
002449241 A 1111
003428906 A 1212
003428906 T 4444 |
The query i want should return only the rows having for example
Desired RESULT
| Code: |
A.AN_ANNUITANT_TIN LSWC_POL_STAT_CD D.LSWC_POL_NO
002328787 A 1234
002328787 T 5678
002449241 A 1010
002449241 T 1111
003428906 A 1212
003428906 T 4444 |
one TIN number should have STAT_CD as 'A' and same TIN number other should have 'T'. Only difference is the policy numbers.
Same TIN number one should had STAT_Cd as 'A' and other should have 'T'.
That's it. |
|
| Back to top |
|
 |
References
|
|
 |
dbzTHEdinosauer
Senior Member
Joined: 20 Oct 2006
Posts: 1641
Location: germany
|
|
|
|
| often a distinct can be resolved with a GROUP BY thus removing the DISTINCT. |
|
| Back to top |
|
|
ashimer
Senior Member
Joined: 13 Feb 2004
Posts: 360
Location: Bangalore
|
|
|
|
Try rewriting your query like this ....
| Code: |
SELECT DISTINCT OA.AN_ANNUITANT_TIN
,D.LSWC_POL_STAT_CD
, D.LSWC_POL_NO
, OA.AN_FULL_NAME
FROM INEDATAT.ANNUITANT OA
INNER
JOIN INEDATAT.INET_LSW_CONTRACT D
Here use common cols of OA and D for join ...
INNER
JOIN INEDATAT.POL_ANNUITANT_REL B
ON B.LSWC_POL_NO = D.LSWC_POL_NO
AND OA.AN_ANNUITANT_TIN IN
(SELECT DISTINCT A.AN_ANNUITANT_TIN
FROM INEDATAT.ANNUITANT A
INNER
JOIN INEDATAT.INET_LSW_CONTRACT D
Here use common cols for A and D for join ....
AND A.AN_ANNUITANT_TIN IN ( SELECT AN_ANNUITANT_TIN FROM
INEDATAT.ANNUITANT GROUP BY AN_ANNUITANT_TIN HAVING COUNT ( * )
= 2 )
AND ( (D.LSWC_POL_STAT_CD IN ( 'A','B','E')) OR ( D.LSWC_POL_STAT_CD NOT IN ( 'A', 'B','E'))))
|
|
|
| Back to top |
|
|
|
|
|
