Create a Named Query

Owned by Matthew Dahlman (Deactivated)
Last updated: Oct 11, 2018 by Anna Rider (Deactivated)

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:

Results:

JSON Results (abbreviated) 
{
  "records" : [ {
    "ID" : 60001,
    "PhysicianProfileID" : "475",
    "FirstName" : "Qamar",
    "LastName" : "Khan",
    "StreetAddressLine1" : "3901 Rainbow Blvd",
    "StreetAddressLine2" : "Dept Of Internal Medicine",
    "State" : "KS",
    "ZipCode" : "66160",
    "Encounters" : [ ],
    "HpcSpecialties" : [ {
      "ID" : "1.ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyCode" : "ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyName" : "Allopathic & Osteopathic Physicians"
    }, {
      "ID" : "1.HEMATOLOGY_ONCOLOGY",
      "SpecialtyCode" : "HEMATOLOGY_ONCOLOGY",
      "SpecialtyName" : "Hematology & Oncology"
    }, {
      "ID" : "1.INTERNAL_MEDICINE",
      "SpecialtyCode" : "INTERNAL_MEDICINE",
      "SpecialtyName" : "Internal Medicine"
    } ]
  }, {
    "ID" : 60025,
    "PhysicianProfileID" : "326552",
    "FirstName" : "ASHER",
    "LastName" : "CHANAN-KHAN",
    "StreetAddressLine1" : "4500 SAN PABLO RD S",
    "StreetAddressLine2" : null,
    "State" : "FL",
    "ZipCode" : "32224-1865",
    "Encounters" : [ {
      "EncounterID" : "1087",
      "EncounterDate" : "1/21/18",
      "Medication" : "Sacubitril and Valsartan Film",
      "Condition" : "Brain Abscess or Spinal Abscess Treatment"
    } ],
    "HpcSpecialties" : [ {
      "ID" : "931.ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyCode" : "ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyName" : "Allopathic & Osteopathic Physicians"
    }, {
      "ID" : "931.HEMATOLOGY_ONCOLOGY",
      "SpecialtyCode" : "HEMATOLOGY_ONCOLOGY",
      "SpecialtyName" : "Hematology & Oncology"
    }, {
      "ID" : "931.INTERNAL_MEDICINE",
      "SpecialtyCode" : "INTERNAL_MEDICINE",
      "SpecialtyName" : "Internal Medicine"
    }, {
      "ID" : "932.ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyCode" : "ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyName" : "Allopathic & Osteopathic Physicians"
    }, {
      "ID" : "932.HEMATOLOGY_ONCOLOGY",
      "SpecialtyCode" : "HEMATOLOGY_ONCOLOGY",
      "SpecialtyName" : "Hematology & Oncology"
    }, {
      "ID" : "932.INTERNAL_MEDICINE",
      "SpecialtyCode" : "INTERNAL_MEDICINE",
      "SpecialtyName" : "Internal Medicine"
    } ]
  }, {
      ...
  } ]
}

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:

Failure to pass mandatory parameter
{
  "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:

Filtering is now applied 
{
  "records" : [ {
    "ID" : 60025,
    "PhysicianProfileID" : "326552",
    "FirstName" : "ASHER",
    "LastName" : "CHANAN-KHAN",
    "StreetAddressLine1" : "4500 SAN PABLO RD S",
    "StreetAddressLine2" : null,
    "State" : "FL",
    "ZipCode" : "32224-1865",
    "Encounters" : [ {
      "EncounterID" : "1087",
      "EncounterDate" : "1/21/18",
      "Medication" : "Sacubitril and Valsartan Film",
      "Condition" : "Brain Abscess or Spinal Abscess Treatment"
    } ],
    "HpcSpecialties" : [ {
      "ID" : "931.ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyCode" : "ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyName" : "Allopathic & Osteopathic Physicians"
    }, {
      "ID" : "931.HEMATOLOGY_ONCOLOGY",
      "SpecialtyCode" : "HEMATOLOGY_ONCOLOGY",
      "SpecialtyName" : "Hematology & Oncology"
    }, {
      "ID" : "931.INTERNAL_MEDICINE",
      "SpecialtyCode" : "INTERNAL_MEDICINE",
      "SpecialtyName" : "Internal Medicine"
    }, {
      "ID" : "932.ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyCode" : "ALLOPATHIC_OSTEOPATHIC_PHYSICIANS",
      "SpecialtyName" : "Allopathic & Osteopathic Physicians"
    }, {
      "ID" : "932.HEMATOLOGY_ONCOLOGY",
      "SpecialtyCode" : "HEMATOLOGY_ONCOLOGY",
      "SpecialtyName" : "Hematology & Oncology"
    }, {
      "ID" : "932.INTERNAL_MEDICINE",
      "SpecialtyCode" : "INTERNAL_MEDICINE",
      "SpecialtyName" : "Internal Medicine"
    } ]
  } ]
}

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.