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

Help

Home

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

Show: Today's Messages :: Show Polls :: Message Navigator

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

Tue, 13 March 2018 04:00

Marcelo Sanchez

Messages: 3
Registered: March 2018

Junior Member

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/

Report message to a moderator

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

Tue, 13 March 2018 19:48

Richard A. Polunsky

Messages: 212
Registered: July 2009
Location: Houston TX

Senior Member

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)"

Report message to a moderator

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

Tue, 13 March 2018 21:34

Marcelo Sanchez

Messages: 3
Registered: March 2018

Junior Member

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!

Report message to a moderator

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

Tue, 13 March 2018 22:22

Marcelo Sanchez

Messages: 3
Registered: March 2018

Junior Member

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 18 04:59:34 GMT 2019

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter