Can't construct a Pareto graph presentation

[shearerg]

Hi,

I need to present basic Pareto graphs of delay codes etc over, for example, weekly periods. I've created a Dynamic SQL View using @PAR1@ to support the selection of an individual weeks worth of data based on a date selected from a date picker. All of this works fine.

However, I haven't been able to order the report output correctly. The output I get when no sorting is attempted is shown below,

ScreenShot117.jpg (29.8 KiB) Viewed 12556 times

but when I attempt to sort the output appropriately

ScreenShot118.jpg (10.85 KiB) Viewed 12556 times

I get a database error:

ScreenShot119.jpg (40.2 KiB) Viewed 12556 times

I have also tried basing the report on a different view design which presents the data already in weekly groups, but the same problem results. Somehow, I think this problem may relate specifically to my use of the Ingres database, but hopefully I'm just doing something stupid.

If anyone can offer me any advice on how to overcome this problem it would be really appreciated, as Pareto style presentation is one of the fairly basic data presentation methods I need to have available.

Regards,
Greg Shearer

PS

Some more detail.

Extracting from the Ingres log, trying to order within the view gives the following. The error is due to the sql subselect structure. When using the subselect structure, Ingres requires 'order by' to be outside the subselect.

!IIapi_query: queryText = Select DAI_.* FROM (select
ds.machine, ds.event, delays.name,
ds.time as duration, ds.incidents, ds.date
from delay_shift ds, delays
where
date_trunc('week', ds.date) = date_trunc('week', date('2010-09-22')) and
delays.event = ds.event and
ds.event <> 94 and ds.event <> 994 and ds.event <> 97 and ds.time <> 0
union
select
d.machine, d.event, delays.name,
d.time, d.incidents, d.date
from delay d, delays
where
date_trunc('week', d.date) = date_trunc('week', date('2010-09-22')) and
delays.event = d.event and
d.event <> 94 and d.event <> 994 and d.event <> 97 and d.time <> 0
order by machine, duration) DAI_ WHERE 1=2

!DBMS Error: (0x000009C5) line 21, Syntax error on 'order'. The correct syntax is:
SELECT [ALL|DISTINCT] target_list
FROM table(s)
[WHERE search_cond]
[GROUP BY col(s)]
[HAVING search_cond]
[UNION subselect]
[ORDER BY col(s)]

I'm trying to get a trace of what happens when sorting is tried within the report ... but having a few technical problems. I'll post later.

[shearerg]

Hi,

I think the information from the log of sorting within the report is useful. The sql generated is:

!IIapi_query: queryText = SELECT DAI_.name, SUM(DAI_.duration) AS F1DAI1 FROM (select
ds.machine, ds.event, delays.name,
ds.time as duration, ds.incidents, ds.date
from delay_shift ds, delays
where
date_trunc('week', ds.date) = date_trunc('week', date('2010-09-22')) and
delays.event = ds.event and
ds.event <> 94 and ds.event <> 994 and ds.event <> 97 and ds.time <> 0
union
select
d.machine, d.event, delays.name,
d.time, d.incidents, d.date
from delay d, delays
where
date_trunc('week', d.date) = date_trunc('week', date('2010-09-22')) and
delays.event = d.event and
d.event <> 94 and d.event <> 994 and d.event <> 97 and d.time <> 0
) DAI_ WHERE ((DAI_.machine='E43')) AND (event <> 94 and
event <> 994 and
event <> 100 and
event <> 101 and
duration <> 0) GROUP BY DAI_.name ORDER BY DAI_.name, SUM(DAI_.duration) DESC

The problem is that DAI is trying to order on the aggregate function SUM(DAI_.duration) instead of the associated alias F1DAI1. When this change is made the sql executes correctly against the database. I'll cross my fingers and hope that this can be easily fixed

Regards,
Greg Shearer

[Mikkel Jensen]

Hi Greg

Thanks for reporting this.

Just a short question before digging into your sql.

Did you try to create the list as a top list? if not please try to set a number in the "show top [ ] Values" on the list options tab.

Top List Definition

Screen shot 2010-09-22 at 9.13.02 AM.png (166.21 KiB) Viewed 12552 times

Regards,
Mikkel Jensen

[shearerg]

Hi Mikkel,

I didn't construct the report as a 'top list', and I will try that in the morning, but I'm pretty confident the problem will remain. In the example you've shown the data still requires sorting and its the way DAI is building the sql order statement, not the underlying structure, which is causing the error.

I'm not sure whether this relates specifically to the Ingres database connection or not, but the 'order by' construction I included in my previous post is certainly incorrect. When I paste the sql directly into interactive sql for the database and replace only the SUM(DAI_.duration) with the correct alias, the sql executes without a problem, so I'm quite confident the sql isn't being constructed correctly within DAI.

A work-around that I think I can use in the short term is, in this example, to construct the complete weekly grouped and sorted view within the database, and access this directly from DAI. This I will probably do, as I'm currently busy trying to demonstrate DAI capabilities to potential users, but I really don't like having to split maintenance of views between the database and DAI.

Regards,
Greg

[shearerg]

Hi Mikkel,

It turns out that the work-around I had planned does not work. Reasons are as follows.

1. Views created within the database do not allow inclusion of an 'order by' clause. Ordering of views is determined by the underlying tables. So, even though I can fully group the data, I cannot order it appropriately for a Pareto chart.

2. I still needed a Dynamic SQL View anyway, in order to allow the use of @PAR1@ for week based data selection from a date picker .... but (as outlined in an earlier post) I can't include an 'order by' clause there as DAI wraps it in a subselect which is not accepted by the database.

3. Attempting to order within the application produces the same error as outlined earlier, as DAI includes the aggregate expression in the 'order by' clause instead of the associated alias.

So, looks like I will be unable to construct a Pareto representation of my data until construction of the 'order by' clause is corrected within DAI. If you have any other suggestions please let me know ... but it would be really good if this could be fixed!

I should add that I know I can also work around the problem, in a fashion, by using 'between' date selection and a direct database view, as shown below. The problem with this approach, from a design perspective, is that it does not allow the report to function as intended within a dashboard that was to contain a mix of single date and week based reports, all driven from a single date picker.

ScreenShot120.jpg (42.66 KiB) Viewed 12549 times

Regards,
Greg Shearer

[Carsten Sørensen]

Hi Greg,

We'll take a look at modifying the INGRESS connection type in Dynamic AI to use the alias instead of the aggregate function - you can be sure that this is not by accident - but something that is apparently different from DB engine to DB engine.

We'll let you know when the update is available.

Best regards,
Carsten

[shearerg]

Hi Carsten,

I expect you are right in seeing this as a DB engine issue, and this is supported by some feedback I obtained from the Ingres forum. I posted a related question there, as I've always been puzzled why Ingres will not allow the use of aggregate function column aliases in 'group by' clauses, but will only allow the use of column aliases in 'order by' clauses.

The use of column names in 'order by' clauses seems natural to me, but it has always puzzled me that aggregate function aliases couldn't be referenced in the group by clause. Turns out that's just the way Ingres does things at the moment! If you're interested, the link to the discussion follows.

http://community.ingres.com/forum/dba-forum/12582-group-order-question.html

Regards,
Greg

[shearerg]

Hi Carsten,

I feel somewhat embarrassed right now as, although the issue described earlier still exists, I have been able to achieve the outcome I was after by using a different view definition. I'm embarrassed because until this morning I was confident I'd tested this view unsuccessfully ... but apparently that wasn't the case, as it is now sorting as required.

So .... now I'm wondering if there is really a problem at all ... perhaps I was previously just using an inappropriate technique to produce the report I was after. Either way, don't put any effort into checking the DAI Ingres interface in the short term, as I can now produce the Pareto style presentation that had been requested!

ScreenShot130.jpg (37.79 KiB) Viewed 12532 times

Regards,
Greg

[Carsten Sørensen]

Hi Greg,

That's great!

You know - there are always issues to look at - if we can move this one back a bit - we'll be happy - thanks for letting us know!!

Best regards,
Carsten