Sunday, March 28, 2010

Parties that have only non primary Contact Points

At our site, all parties are suppose to have one primary contact point. But owning to some bug, some parties had only non primary contact points such as PHONE. The following queries can identify such parties

SELECT owner_table_id
FROM ( SELECT owner_table_id
, SUM(DECODE(primary_flag,'Y',1,0)) primary_count
, SUM(DECODE(primary_flag,'Y',0,1)) non_primary_count
FROM hz_contact_points
WHERE status = 'A'
AND contact_point_type= 'PHONE'
GROUP BY owner_table_id
WHERE primary_count = 0 AND non_primary_count > 0

No comments:

Post a Comment