Dynamic Parameters for Your SQL Queries
- Nabil OUKACI
The Kepler SQL Query Fields plugin supports three types of dynamic parameters to enhance your SQL queries. You can use them to make your queries more flexible and powerful.
Use Cases for Dynamic Parameters
When working with the Kepler SQL Query Fields plugin, it's crucial to understand how to best leverage the three dynamic parameters: {projectId}, {components}, and {customfield_xxxxx}. These parameters are designed to simplify the customization of your SQL queries based on your specific needs.
- {projectId}: Use this parameter when you want to incorporate a project's identifier into an SQL query. Instead of creating separate queries for each project, use {projectId} to make your query applicable to all your projects. This way, your query will automatically adapt to each project, providing a more efficient solution.
- {components}: When you need to include a task's component in your SQL query, the {components} parameter is the solution. It allows your query to adjust based on the currently selected component. If the issue's component changes, your query will adjust accordingly. Please note that this parameter supports only one component at a time.
- {customfield_xxxxx}: The {customfield_xxxxx} parameter is the most flexible tool at your disposal. It enables you to use a variable in your query, displaying a result for each value of this parameter. If the value of {customfield_xxxxx} changes, your query will automatically update. This provides advanced customization to address specific needs.
By judiciously combining these dynamic parameters, you can optimize your SQL queries, resulting in more precise outcomes and enhancing your overall experience with the Kepler SQL Query Fields plugin.
Using a Project Identifier
One of the available dynamic parameters is the use of a project identifier. You can incorporate this identifier into your SQL queries using the {projectId} parameter. Here's an example:
SELECT carName FROM cars WHERE idCar = '{projectId}'
In this query, {projectId} will be replaced by the project identifier at runtime.
The {projectId} can be exceptionally used in create screen.Using a Dynamic Parameters Values from an issue
As for using {projectId} parameter in your SQL queries, you can use other parameters related to the issue. This is explained in the following table.
In this query, this parameters will be replaced by their values at runtime according to the linked issue.
To use it, insere the keyword between '{}'.
| Keyword | Type | Jira Description Parameter |
|---|---|---|
| currentUser | String | Get the logged user name according to the account of Jira. |
| displayName | String | Get the logged user name according to the account of Jira. |
| id | Long | The unique ID of the issue. |
| key | String | The issue key. |
| statusId | String | |
| issueTypeId | String | The ID of the IssueType for this Issue. |
| parentId | Long | |
| projectId | Long | Gets the ID of the Project for this Issue. See section "Using a Project Identifier". |
| description | String | |
| summary | String | |
| environment | String | |
| watches | Long | |
| votes | Long | |
| originalEstimate | Long | This is the "original estimate" of work to be performed on this issue, in milliseconds. |
| estimate | Long | This is the "remaining estimate" of work left to be performed on this issue, in milliseconds. A better name would be getRemainingEstimate but for historical reasons it is called what it is called. |
| timeSpent | Long | This is the "total time spent" working on this issue, in milliseconds. |
| created | Timestamp | |
| updated | Timestamp | |
| dueDate | Timestamp | |
| resolutionDate | Timestamp | Returns the datetime that an issue was resolved on. Will be null if it hasn't been resolved yet, or if an issue has been returned to the 'unresolved' state. |
| archivedDate | Timestamp | Returns the timestamp when this issue was archived, or null if it wasn't archived. |
| creatorId | String | A user key of the creator. |
| reporterId | String | A user key of the reporter. |
| assigneeId | String | A user key of the assignee. |
| archivedById | String | Returns the userid of user who archived this issue, or null if it wasn't archived. |
| number | Long | |
| resolutionId | String | |
| securityLevelId | Long | Returns the Security Level for this Issue. |
| ..... |
See official Atlassian Issue Documentation to more details about description of keyword.
The dynamic parameters values can be used only after creation.
But, the {projectId}, {currentUser} and {components} can be exceptionally used in create screen.
Compatible with Jira 9.x.x
These Keywords are only operational and compatible from version of Jira 9.x.x
Using Jira Components
You can also leverage Jira components in your SQL queries using the {components} parameter. However, it's important to note that this works only when you have a single component. Here's an example:
SELECT carBrand FROM cars WHERE carName = '{components}'
In this query, {components} will be replaced by the selected Jira component.
If you don't know how to create a component, follow these steps.
The {components} can be exceptionally used in create screen.Working with Custom Fields
Using custom fields offers you maximum flexibility. You can create custom fields in Jira, retrieve their identifiers, and directly integrate them into your SQL queries. Here's an example:
SELECT carBrand FROM cars WHERE carName = '{customfield_10320}'
In this query, {customfield_10320} will be replaced by the value of the associated custom field.
customfield_xxxxx must contain a primitive or atomic value.Conclusion
In conclusion, the dynamic parameters offered by the Kepler SQL Query Fields plugin are a valuable asset for customizing your SQL queries in Jira. These parameters allow you to make your queries more flexible and tailored to your specific needs. Here's a summary of the key advantages of each parameter type:
- Using a Project Identifier ({projectId}): You can easily incorporate the project identifier into your queries, simplifying data customization based on the current project.
- Utilizing Jira Components ({components}): Components are important elements of your workflow. By using this parameter, you can adjust your queries to reflect the current component value, facilitating data management.
- Working with Custom Fields ({customfield_xxxxx}): Custom fields add a layer of flexibility to your queries. You can directly incorporate them into your SQL queries to interact with specific data.
By combining these dynamic parameters, you can optimize your SQL queries and achieve more precise results, enhancing your overall experience with the Kepler SQL Query Fields plugin.