OK, this is working fine (with a few extra fields tossed in). If you still would like to see the Explain, let
me know.... But I'm satisfied with this for now... Thanks everyone, for all your help.... This will make our business folks very happy.
[code:1:b8b5cd4b14]SELECT T_POL_NO AS POL_NO
,T_POL_SUFFIX AS POL_SUF
,POL_EFF_DATE AS EFF_DT
,MCNT AS MOTORCYCLE_COUNT
,VEHICLE_TYPE AS VEH_TYPE
,YEAR_OF_MODEL AS MODEL_YR
,TRADE_NAME
FROM N090DSNM.H2591DB.TCSAU_VEHI
INNER JOIN (
SELECT POL_NUM AS T_POL_NO
,POL_SUFFIX AS T_POL_SUFFIX
,POL_EFF_DATE AS T_POL_EFF_DATE
,SUM(CASE WHEN VEHICLE_TYPE = 'M' THEN +1 ELSE +0 END) AS MCNT
,SUM(CASE WHEN VEHICLE_TYPE
That's correct. I only want policies that only have motorcycle rows.
Your version of the query works well :
(again, I adde POL_SUFFIX to the query since that's considered part of the policy number)
Code:
SELECT POL_NUM
,POL_SUFFIX
,POL_EFF_DATE
,VEHICLE_TYPE
,YEAR_OF_MODEL
,TRADE_NAME
FROM N090DSNM.H2591DB.TCSAU_VEHI
WHERE STATE = '20'
AND GENERAL_STATUS||REASON_GENERAL = '1A'
AND
POL_NUM||POL_SUFFIX IN (SELECT POL_NUM||POL_SUFFIX
FROM N090DSNM.H2591DB.TCSAU_VEHI
WHERE STATE = '20'
AND GENERAL_STATUS||REASON_GENERAL = '1A'
GROUP BY POL_NUM||POL_SUFFIX
HAVING SUM(CASE WHEN VEHICLE_TYPE = 'M'
THEN 1 ELSE 0 END) >= 1
AND SUM(CASE WHEN VEHICLE_TYPE <> 'M'
THEN 1 ELSE 0 END) = 0)
All times are GMT - 5 Hours Goto page Previous1, 2
Page 2 of 2
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
