Saved queries
This page explores Saved Queries
Last updated
This page explores Saved Queries
Last updated
Saved queries allows you to query any data within Cinchy (respecting entitlements) and save them to be used as APIs by external systems.
You can access your Saved Query directly by either CinchyID or the domain and name of the Saved Query.
<baseurl>/Query/Execute?queryId=<cinchyid>
<baseurl>/Query/Execute/<domain>/<saved query name>
You can find this information in the Saved Queries table.
You can also search your Cinchy Homepage to find your Saved Query.
From the homepage, select Create > Query (Image 1)
Fill out the following information:
Under the Info tab, you can fill out information on the query if you wish to save it (Image 2):
Query Name: Mandatory field. Must be unique within the Domain.
Icon: You can optionally pick a non-default icon, as well as color for your table. This will be displayed in My Network.
Domain: You need to select a Domain your query will reside in. As an admin, you can also create new domains in this screen.
Description: You can give your query a description. This description will be displayed on the home screen to users browsing the marketplace. It will also be searchable.
Return Type: Queries have six different return types:
Query Results (Approved Data Only)
This is the default return type, it returns a table from a select query with only approved data for tables with Change Approval enabled, or all data for tables without Change Approval. This is generally used for external APIs as you will want to query approved data, rather than drafts.
Query Results (Including Draft Data)
This return type returns a table from a SELECT query (including draft data) for tables with Change Approval enabled. Use this return type when looking to display results of records that are pending approval.
Query Results (Including Version History)
This return type returns a table from a SELECT query (including draft data) with historical data for all tables, as seen in the Collaboration Log of any record. This data includes all changes that happened to all records within the scope of the select query.
Number of Rows Affected
This return type returns a single string response with the number of rows affected if the last statement in the query is an INSERT, UPDATE, or DELETE statement.
Execute DDL Script
Use this return type when your query contains DDL commands that implement schema changes such as CREATE|ALTER|DROP TABLE, CREATE|ALTER|DROP VIEW, or CREATE|DROP INDEX.
Single Value (First Column of First Row)
This return type returns a result of 1 row x 1 column, irrespective of the underlying result set.
In the Query screen, you can modify and run your query (Image 3).
On the left hand side you have the Object tree, which shows you all the domains, tables, and columns you have access to query within Cinchy. You can search or simply navigate by expanding the domains and tables.
You can drag and drop the columns or table you're looking for into the Query Builder.
Once you are satisfied with your query, you can click save to keep a copy. You can then find your query in the "Saved Queries" table (Image 4):
Once you've set up your saved query, you can find it on your homepage (Image 5).
2. Clicking the query will allow you to "Execute Query" and show you the result set (if there is a SELECT at the end). Sometimes the query will have parameters you need to fill out first before executing (Image 6).
Once you execute a query, you can switch the Display to Pivot Mode to see different visualizations of the data (Image 7).
If you want to share the report, you can click the Pivot URL button on the top right to copy the URL to that pivoted report. Simply add it as an applet and bookmark it to return to the pivoted view!
For more information and documentation on Cinchy Query Language (CQL), please see the CQL basics page
