Since the 7.17.0 version, the sql function requires a query ID ad the first parameter. It is no longer possible to run an arbitrary query from the Issue Navigator: only trusted users can write and save queries to get a Query ID
It also supports parameters now...
sql(#ID, parameters,...)
SELECT key(jql.issue) AS "Issue", nvl2(ic.id, 1, 0)as "#Comments" FROM JQL LEFT JOIN issuecomments ic ON ic.issueid = jql.issueid WHERE jql.query = 'project = DEMO' GROUP BY "Issue" HAVING "#Comments" = 0 |
In the example above the query returns all the issues in the DEMO project with no comments.
Ensure that the first column returns an Issue KEY (plain text with no HTML markup) or Issue ID (Numeric) |
Replace the constant with the SQL variable symbol → ?
SELECT key(jql.issue) AS "Issue", nvl2(ic.id, 1, 0)as "#Comments" FROM JQL LEFT JOIN issuecomments ic ON ic.issueid = jql.issueid WHERE jql.query = ? GROUP BY "Issue" HAVING "#Comments" = ? |
Note that two variables have been introduced by replacing the JQ.QUERY value (project = DEMO) and the HAVING "#Comments" condition (0)
Run the query and click on the Save button anf fill out a name (#Comments in this example) and a description (Optional).
NOTE: If the query has parameters then you get an error as the console does not support parameters. Please, ignore them and go forward saving the parameterized query.
Click on the Saved queries link to get your new Saved query ID (1159 in this example)
issue in sql(1159, "project=DEMO", 0) |
Use the built-in sql() JQL function to run your query. Provide values for all the parameters (if any)
Pay attention to the subtle double transformation: JQL → SQL → JQL
You started with a JQL
project=DEMO
Then you transformed it into SQL
SELECT ... FROM JQL JOIN ... WHERE JQL:QUERY = 'poject= DEMO' AND ...
And finally, it was transformed to JQL again:
issue in sql(..)
Since the built-in sql() JQL function is a standard JQL function it can be used as an input of a new SQL query (JQL → SQL → JQL → SQL → JQL...)
SELECT * FROM JQL WHERE JQL.QUERY = 'issue in sql(1159, "project="DEMO", 0)'
In the example above, the SQL query will internally invoke another SQL