Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » 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 04:00 Go to next message
Marcelo Sanchez is currently offline Marcelo SanchezFriend
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/
Re: SQL is not honoured in BIRT from eclipse [message #1783530 is a reply to message #1783426] Tue, 13 March 2018 19:48 Go to previous messageGo to next message
Richard A. Polunsky is currently offline Richard A. PolunskyFriend
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)"
Re: SQL is not honoured in BIRT from eclipse [message #1783541 is a reply to message #1783530] Tue, 13 March 2018 21:34 Go to previous messageGo to next message
Marcelo Sanchez is currently offline Marcelo SanchezFriend
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!
Re: SQL is not honoured in BIRT from eclipse [message #1783543 is a reply to message #1783541] Tue, 13 March 2018 22:22 Go to previous message
Marcelo Sanchez is currently offline Marcelo SanchezFriend
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.
Previous Topic:Basic requirements for BIRT
Next Topic:Encoding UTf-8 on birtviewer server
Goto Forum:
  


Current Time: Mon Dec 09 10:21:22 GMT 2019

Powered by FUDForum. Page generated in 0.02051 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top