Eclipse Community Forums: BIRT » SQL is not honoured in BIRT from eclipse

Home » Archived » BIRT » SQL is not honoured in BIRT from eclipse(SQL is not honoured in BIRT from eclipse)

SQL is not honoured in BIRT from eclipse [message #1783426]

Tue, 13 March 2018 00:00

Eclipse User

Hi There,

The following SQL from JIRA does not run the same in eclipse:

SELECT
p.name AS "Project",
i.key AS "Issue",
td.name AS "Type",
sd.name AS "Status",
i.assignee AS "Assignee",
i.created AS "Created",
i.summary AS "Summary",
i.reporter AS "Reporter",
i.updated AS "Updated" ,
i.due AS "Due date",
icd.name AS "Custom Field",
icv.value AS "Value"
FROM
issues i
INNER JOIN
projects p ON p.id=i.projectid
INNER JOIN
issuetypedefinitions td ON td.id=i.typeid
INNER JOIN
issuestatusdefinitions sd ON sd.id=i.statusid
INNER JOIN
ISSUECUSTOMFIELDVALUES icv on icv.issueid=i.id
INNER JOIN
ISSUECUSTOMFIELDDEFINITIONS icd on icd.id=icv.customfieldid
WHERE
p.name = 'AU: Australian Health Managment' and
jql='status in (Open,
Resolved) AND assignee in (membersOf("AUS Service Ops Admin")) ORDER BY summary DESC'

The sql results in 23 rows while running the same SQL in a BIRT report in eclipse is just returning 5 rows.

I have exhausted problem determination and as I a new on BIRT/eclipse I would like to ask for some pointer on how to be able to fix this issue.

Thanks.

Marcelo/

Re: SQL is not honoured in BIRT from eclipse [message #1783530 is a reply to message #1783426]

Tue, 13 March 2018 15:48

Eclipse User

Your problem is that the query generated by JIRA is NOT the query you need to send to the database. JIRA uses that "jql" piece to modify the base query using JIRA-internal functionality. You will need to find out what the "membersOf" function does - probably a lookup into user name groups, and write your own equivalent of "status in (Open, Resolved)"

Re: SQL is not honoured in BIRT from eclipse [message #1783541 is a reply to message #1783530]

Tue, 13 March 2018 17:34

Eclipse User

Thank you Richard for your advise.

I totally believed that the SQL generated in JIRA would be running the same from report designer in eclipse.

It is very misleading as there are a few others different queries (in presence of JQL syntax) were running the same in both JIRA and report designer..

Anyway, now I know, hopefully will be able to generate the right sql to run from report designer.

Thanks again!

Re: SQL is not honoured in BIRT from eclipse [message #1783543 is a reply to message #1783541]

Tue, 13 March 2018 18:22

Eclipse User

Hi again,

I have read the SQL for Jira Driver documentation and this stated that the SQL generated in JIRA should work in any application running the JDBC driver, this is known as SQL+JQL -> https://kintosoft.atlassian.net/wiki/spaces/SFJ/overview
While trying to use standard SQL in the report designer or in JIRA directly (SQL for JIRA Driver query windows) it kept on failing as you should provide with a jql= part used by JIRA....
This is not consistent with the documentation.
Any other hints?

Cheers.

Previous Topic:	Basic requirements for BIRT
Next Topic:	Encoding UTf-8 on birtviewer server

Goto Forum:

-=] Back to Top [=-

Current Time: Sat Jul 12 10:58:10 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter