SQL Report in iReport

[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 (60.1 KiB) Viewed 6900 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

[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

[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?

[lnovoa]

Missing in the group by adding the NBS_NAME

...GROUP BY NDV_NAME, PSR_USER, NBS_NAME

[dferguson]

This worked exactly how I needed it to, thanks!

[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?

[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.

[Pitt]

Can you explain the meaning of "PSR_FROM" please?

[jllort]

Hi Pitt

I suspect that column is something the user created itself. It does not seem an OpenKM column name.