Page 1 of 1

SQL Report in iReport

PostPosted:Mon Apr 16, 2018 8:18 pm
by dferguson
I have really struggled to get a filtered view of some SQL query results to put in a iReport for one of our users.

We are using the subscription feature AND the 'GET_DOCUMENT_CONTENT to verify that users have self-trained on required documents. Using iReport I built this query...
Code: Select all
SELECT
     OKM_ACTIVITY.`ACT_DATE` AS OKM_ACTIVITY_ACT_DATE,
     OKM_PROP_SUB_RECEIVED.`PSR_USER` AS OKM_PROP_SUB_RECEIVED_PSR_USER,
     OKM_NODE_BASE.`NBS_NAME` AS OKM_NODE_BASE_NBS_NAME,
     OKM_NODE_DOCUMENT_VERSION.`NDV_NAME` AS OKM_NODE_DOCUMENT_VERSION_NDV_NAME
FROM
     `OKM_PROP_SUB_RECEIVED` OKM_PROP_SUB_RECEIVED INNER JOIN `OKM_NODE_BASE` OKM_NODE_BASE ON OKM_PROP_SUB_RECEIVED.`PSR_NODE` = OKM_NODE_BASE.`NBS_UUID`
     INNER JOIN `OKM_NODE_DOCUMENT_VERSION` OKM_NODE_DOCUMENT_VERSION ON OKM_NODE_BASE.`NBS_UUID` = OKM_NODE_DOCUMENT_VERSION.`NDV_PARENT`
     INNER JOIN `OKM_ACTIVITY` OKM_ACTIVITY ON OKM_NODE_BASE.`NBS_UUID` = OKM_ACTIVITY.`ACT_ITEM`
WHERE
   PSR_FROM = 'jmcnaughton'
 AND ACT_ACTION = 'GET_DOCUMENT_CONTENT'
 AND NDV_CURRENT = 'T'

ORDER BY PSR_USER, NBS_NAME, NDV_NAME, ACT_DATE DESC
sql query.png
sql query.png (60.1 KiB) Viewed 5398 times
It does great, except it lists every time they have viewed the current version. I would like to filter by ACT_DATE to only show the last 'GET_DOCUMENT_CONTENT'.

Since this example joins so many tables it has been hard for me to use things like MAX(ACT_DATE). When I try to do that I get the following error
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'okmdb.OKM_PROP_SUB_RECEIVED.PSR_USER'; this is incompatible with sql_mode=only_full_group_by
I figure there has to be a simple solution to this, but I am very new to all this and it has eluded me so far. Any help would be greatly appreciated.

Doug

Re: SQL Report in iReport

PostPosted:Wed Apr 18, 2018 9:47 am
by lnovoa
Hi!,
try this.

I hope it's what you need
Code: Select all
SELECT
     MAX(OKM_ACTIVITY.`ACT_DATE`) AS OKM_ACTIVITY_ACT_DATE,
     OKM_PROP_SUB_RECEIVED.`PSR_USER` AS OKM_PROP_SUB_RECEIVED_PSR_USER,
     OKM_NODE_BASE.`NBS_NAME` AS OKM_NODE_BASE_NBS_NAME,
     OKM_NODE_DOCUMENT_VERSION.`NDV_NAME` AS OKM_NODE_DOCUMENT_VERSION_NDV_NAME
FROM
     `OKM_PROP_SUB_RECEIVED` OKM_PROP_SUB_RECEIVED INNER JOIN `OKM_NODE_BASE` OKM_NODE_BASE ON OKM_PROP_SUB_RECEIVED.`PSR_NODE` = OKM_NODE_BASE.`NBS_UUID`
     INNER JOIN `OKM_NODE_DOCUMENT_VERSION` OKM_NODE_DOCUMENT_VERSION ON OKM_NODE_BASE.`NBS_UUID` = OKM_NODE_DOCUMENT_VERSION.`NDV_PARENT`
     INNER JOIN `OKM_ACTIVITY` OKM_ACTIVITY ON OKM_NODE_BASE.`NBS_UUID` = OKM_ACTIVITY.`ACT_ITEM`
WHERE
   PSR_FROM = 'jmcnaughton'
 AND ACT_ACTION = 'GET_DOCUMENT_CONTENT'
 AND NDV_CURRENT = 'T'
 
GROUP BY NDV_NAME, PSR_USER
ORDER BY PSR_USER, NBS_NAME, NDV_NAME DESC

Re: SQL Report in iReport

PostPosted:Wed Apr 18, 2018 12:47 pm
by dferguson
Thank for that, but I still get this error...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'okmdb.OKM_NODE_BASE.NBS_NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Does anyone know if changing this global for the database would have adverse effect?

Re: SQL Report in iReport

PostPosted:Thu Apr 19, 2018 7:40 am
by lnovoa
Missing in the group by adding the NBS_NAME

...GROUP BY NDV_NAME, PSR_USER, NBS_NAME

Re: SQL Report in iReport

PostPosted:Thu Jul 12, 2018 1:31 pm
by dferguson
This worked exactly how I needed it to, thanks!

Re: SQL Report in iReport

PostPosted:Thu Jul 12, 2018 3:27 pm
by dferguson
Our quality director would like to see a history of all subscribed document versions (not just current = 'T') and when users looked at the document (ACT_ACTION = 'GET_DOCUMENT_CONTENT'). Obviously I'd have to identify the date the version was created and then find a single instance of the user getting the document content where the date is greater than the created date. For cases where there are multiple revisions of a document I would need to loop through all the 'GET_DOCUMENT_CONTENT' entries and find a single instance that fell within that revision's created date and the next revision's created date. I can think of how to do this in other programming languages, but can't think of way to SQL query this information and then get it into a iReport.

Ideas?

We are using subscriptions and 'GET_DOCUMENT_CONTENT' as our method to verify training on a document. I'm not sure why he would like to keep a training history of old revisions, but he asked. Is this the best way or am I missing something more obvious?

Re: SQL Report in iReport

PostPosted:Fri Jul 13, 2018 4:01 pm
by jllort
We have some customer with something similar, basically they have an ISO, and they distribute the document to several users. Then they want to get a report based on what specific document version and when the users reviewed the document. For it is better an specific customization rather trying to play only with current database data, what might be some pain on it.