Create a Named Query
This is a practical example of creating and using a named query.
1. Create the Named Query
The first iteration shown here has properties and sub-objects with properties, but it has no parameters or filters. Make sure that this works as expected first.
2. Test the Named Query
Refer to the named query documentation for details. In this example:
- Base url: http://ec2-xx-yy-zz.compute.amazonaws.com/semarchy-postgres/api/rest/
- Data location: HCO_HCP
- Named query: HCP_BY_NAME_AND_ADDRLINE1
- View type: Golden Records
- Complete URL: http://ec2-xx-yy-zz.compute.amazonaws.com/semarchy-postgres/api/rest/named-query/HCO_HCP/HCP_BY_NAME_AND_ADDRLINE1/GD
Results:
3. Update the Named Query - Add Parameters
In this case we add HCP_ADDRLINE1 and HCP_NAME both as mandatory parameters without default values.
If you run exactly the same query as before you'll now expect to get an error:
{
"error" : {
"errorMessage" : "Mandatory parameter 'HCP_ADDRLINE1' not provided",
"errorId" : "7c752b56-f708-4cbd-bf63-b737a84bf5bd",
"statusCode" : 400
}
}
4. Use the Parameters
To use the named query now, you must include the mandatory parameters:
Note:
- Spaces are url encoded to %20
- The query results are identical to before. The parameters are mandatory, but they are not yet used in any way. That's because you need to set up a filter to use those parameters.
5. Update the Named Query - Add Filter based on the Parameters
Define the filter. For example:
StreetAddressLine1 = :QUERY_PARAM_HCP_ADDRLI AND LastName = :QUERY_PARAM_HCP_NAME
Test. Now the exact same query returns only a single result:
6. Update the Named Query - Modify Filter
Add flexibility to the Named Query by using LIKE in the filter:
StreetAddressLine1 LIKE '%' || :QUERY_PARAM_HCP_ADDRLI || '%' AND LastName LIKE '%' || :QUERY_PARAM_HCP_NAME || '%'
Now this modified query with a partial name and partial address returns exactly the same result set:
- http://ec2-xx-yy-zz.compute.amazonaws.com/semarchy-postgres/api/rest/named-query/HCO_HCP/HCP_BY_NAME_AND_ADDRLINE1/GD?HCP_ADDRLINE1=SAN%20PABLO&HCP_NAME=KHAN
URLs may use any valid UTF-8 characters (the details of the character set encoding can different depending on the application server configuration, but most Semarchy customers use Tomcat with UTF-8 enabled):
- http://ec2-xx-yy-zz.compute.amazonaws.com/semarchy-postgres/api/rest/named-query/HCO_HCP/HCP_BY_NAME_AND_ADDRLINE1/GD?HCP_ADDRLINE1=10TH&HCP_NAME=郑
Additional Notes
Parameter values should be included directly following the equals sign (=) and must not be quoted. For example &HCP_NAME=KHAN.
When filters are defined in the URL, these often include single quotes which must be quoted. For example LastName LIKE '%KHAN%' would be encoded as &$f=LastName%20LIKE%20%27%25KHAN%25%27.
Refer to the Semarchy xDM Integration Guide Documentation for complete details.
Related articles