Hello, 

following on from this question <(re) formatting the TOD output in zSecure reports | IBM Security Z Security> 

To recap:

I am building an Access monitor query that is mailed out to management for them to open in excel.  I am having issues with the output and date formatting of LAST_TOD in so much as it leaves a blank for the first 09 days of the month in output example below:

In my query I have the following DEFINES

The last one was an example advised by RVH and provides the following output:

Which works for the 06th of June but it gives two lines for the same resource One each for the months that were accessed.

If I remove the last_date define from the output I get what I want. ie One line showing the first and last date of access across the input timeline: 

However I now get the pesky space for 06th June.  I have tried the following DEFINE statements to replicate first_tod_sum and last_tod_sum above but converted to a DATE format as opposed to TOD

I get the following messages when I un-comment them. 

define min_last_date('First') as min(last_date)

define dat_firstuse(11,eudate) as convert(first_tod_sum,tod,date)

Am I to infer that:

a) the MIN command cannot be used with a date format?

b) you cannot convert a previously defined variable to a date format? 

Eamonn

Hi Eamonn

Your DEFINE min_last_day AS MIN(last_date) looks for a field MIN because of the AS keyword.  To calculate the minimum value, remove the AS keyword: DEFINE min_last_day MIN(last_date)

Now, the statistical functions (MIN, MAX, SUM, MEAN, AVG) only work on built-in field names.  Not on defined fields.  Yes, silly, utterly restrictive, but there you have it.  This was built in the early nineties and not upgraded since.

If you want to apply a statistic on a defined field, you must use the statistic modifier on the SUMMARY command, but there are only five.

So summary .... first_tod_sum(min) might do the trick.

Hello, 

following on from this question <(re) formatting the TOD output in zSecure reports | IBM Security Z Security> 

To recap:

I am building an Access monitor query that is mailed out to management for them to open in excel.  I am having issues with the output and date formatting of LAST_TOD in so much as it leaves a blank for the first 09 days of the month in output example below:

In my query I have the following DEFINES

The last one was an example advised by RVH and provides the following output:

Which works for the 06th of June but it gives two lines for the same resource One each for the months that were accessed.

If I remove the last_date define from the output I get what I want. ie One line showing the first and last date of access across the input timeline: 

However I now get the pesky space for 06th June.  I have tried the following DEFINE statements to replicate first_tod_sum and last_tod_sum above but converted to a DATE format as opposed to TOD

I get the following messages when I un-comment them. 

define min_last_date('First') as min(last_date)

define dat_firstuse(11,eudate) as convert(first_tod_sum,tod,date)

Am I to infer that:

a) the MIN command cannot be used with a date format?

b) you cannot convert a previously defined variable to a date format? 

Eamonn