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 !

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 !

• guidewire
• gosu

• What problem was that query solving? Maybe your query rewritten so that it is implemented with the gosu query API easier – Sergio Commented yesterday

1 Answer 1

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.

https://docs.guidewire.com/cloud/pc/202411/integration/integration/topics/querybuilderapi/c_ba3266101.html

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.