In PolicyCenter on-premise we could use this SQL query to address a certain problem. I wonder how to tranform it into a Gosu Query now that we use the cloud version of PolicyCenter :
SELECT contactId, ADDRESSTYPE
FROM (
SELECT c.ID AS contactID, a.CREATETIME, a.VALIDFROM, a.ADDRESSTYPE
FROM WPC.PC_CONTACT c
JOIN WPC.PC_ADDRESS a ON c.PRIMARYADDRESSID = a.ID
UNION ALL
SELECT ca.contactID AS ContactID, a.CREATETIME, a.VALIDFROM, a.ADDRESSTYPE
FROM WPC.PC_CONTACTADDRESS ca
JOIN WPC.PC_ADDRESS a ON ca.ADDRESSID= a.ID
)
GROUP BY contactID, VALIDFROM, ADDRESSTYPE
HAVING COUNT(VALIDFROM) >= 2;
I tried a lot of joinings, unions etc... But i can't manage to get it to do the same ! Thank you for any help given !
I think you would need to pass the union call as an argument to a subselect which Guidewire says is not allowed:
Note: You cannot use the union method on query objects passed as arguments to the subselect method.
This might be one of those fun instances that does not have a way to accomplish strictly using a query API call. It certainly would not be as efficient, but you could do both queries and then determine if 2 or more results were returned in gosu code since you are coding it in gosu anyway.
What do you use the results for? Is this a query that you run, get the results, and then take some other action outside of PC? If optimal performance is not important, I would just write this with a combination of query API calls and gosu code and not worry about recreating this SQL query exactly using a query API call.