Dynamic Query (SQL)

Dynamic Query is a query that contains one or more values of certain issue fields in the WHERE clause.

Building a query using custom fields

1. Create the necessary custom fields in Jira and remember their IDs. They should have a customfield_yyyyy format, where yyyyy is a number. 
   To find out a custom field ID:
   1. Go to the Custom Fields section in Jira administration.
   2. Locate your custom field and click Configure from the actions menu on the right.
      You can see your custom field ID as a parameter in the url:
      /secure/admin/ConfigureCustomField!default.jspa?customFieldId=yyyyy.
2. After you go through the steps from the Configuration page and the data source configuration page, go to Administration > CPRIME PLUGINS CONFIGURATION > Database Custom Field.
3. Fill in the Sql Query field for the main database custom field (customfield_xxxxx) with a dynamic clause of the following format:
   "WHERE col1={customfield_yyyyy} AND col2={customfield_zzzzz} OR col2={customfield_ttttt}").
4. Save the configuration.

Example

SELECT col1, col2, col3, col4 FROM testTable WHERE col1 = {customfield_10300}

Building a query using system fields

Project field works on any Jira screen, but note that Jira creates the key in uppercase.

System fields can also be used in the dynamic query by using one of the following parameters in the WHERE clause:

• project – key of the project
• assignee
• key – key of the current issue
  
• reporter
• priority
• versions
• fixVersions
• components
• labels
• created
• updated
• resolved
• duedate
• issuetype (the name of the issue type)
• status
• resolution 
• currentuser 
• currentuserkey 

• issueid – available starting with versions 3.0.8/3.1.1. The cast to numeric type is not ensured, so you need to cast it.

Example

 SELECT col1, col2, col3, col4 FROM testTable WHERE col1 = {assignee} AND col2 = {reporter}

Dynamic query using dates values

Normally we can only have columns of text type in combination with dynamic custom fields in the WHERE clause. If a date column or some other non text type is used in the WHERE clause, the query does not work.

For instance if the testdate column is numeric in the testTable table, the following query does not work, because the value of the {created} is a string.

select testdate, col1, col2 from worklog where testdate = {created};

The solution is to use functions available for the database chosen, to transform from string to the type of the column in the SQL query.

For instance, for PostgreSQL: SELECT testdate, col1, col2 from worklog WHERE testdate = cast({created} as timestamp).

For the dates(created, updated), the date from the database should be of the following format: 2012-03-12 17:40:34.973.

Types of supported custom fields

Jira custom fields

• Text Field (< 255 characters)
• User Picker
• URL Field
• Free Text Field (unlimited text)
• Number
• Select List
• Radio Buttons (since v. 2.1.13)

Additional custom field

• Database Information and Database Child Information

Dynamic query using numeric values for dependents

Normally we can only have columns of text type in combination with dynamic custom fields in the WHERE clause. If a numeric column or some other non text is used in the WHERE clause, the query does not work.

For instance if the column id is numeric in the testTable table, the following query does not work, because the value of the customfield_10300 is a string.

SELECT id, col2, col3, col4 FROM testTable WHERE id = {customfield_10300}

The solution is to use functions available for the database chosen, to transform from string to the type of the column in the SQL query.

For instance, for PostgreSQL: SELECT id, col2, col3, col4 FROM testTable WHERE id = cast({customfield_10300} as numeric).

SELECT col1 FROM table WHERE col1 LIKE '%{key}%'