I am trying to create a thematic Pie Chart from a view within Oracle, but it does not appear to be working for some views. And example of a view where it does work is below:
CREATE OR REPLACE FORCE VIEW V_DEFECT_DRAFT_RPT_COUNT AS SELECT link_id, COUNT(*) total_surveys, COUNT(report_draft_actual) completed, COUNT(*) - COUNT(report_draft_actual) not_completed FROM eb_defect_docs GROUP BY link_id;
An example where it doesn't work is:
CREATE OR REPLACE VIEW V_DEFECT_LETTER_ACTUAL AS SELECT LINK_ID, SUM(DECODE(LETTER_ACTUAL,'',1,NULL)) NO_VALUE, SUM(DECODE(LETTER_ACTUAL,'12-SEP-11 00.00.00',1,NULL)) COLS, SUM(DECODE(LETTER_ACTUAL,'02-SEP-11 00.00.00',1,NULL)) COLSS, SUM(DECODE(LETTER_ACTUAL,'08-SEP-11 00.00.00',1,NULL)) COLSSS, SUM(DECODE(LETTER_ACTUAL,'07-NOV-11 00.00.00',1,NULL)) COLN, SUM(DECODE(LETTER_ACTUAL,'05-SEP-11 00.00.00',1,NULL)) COLSSSS FROM EB_DEFECT_DOCS GROUP BY LINK_ID
The difference appears to be the use of DECODE function (or CASE, same result). I have also tried variations using COUNT and SUM, but none of these seem to make any difference. Is there a restriction on the use of SQL in views for pie charts, or is is some other problem?
Regards
Dan
Just a quick additional note, I wasn't being entirely truthful when I said it doesn't work, the second view works but only ever returns the NO_VALUES column in the example - this is regardless of position in the SQL statement.
Further update, found that the problem is to do with the use of dates (should have guessed). Although the view itself is based on numerical columns, because the underlying data is derived from dates for some reason this causes the problem. This is why the NO_VALUES column works and the others do not, additionally if I create the output as a table or materialised view instead of a standard view the problem is resolved.
Just remember a materialized view is not the same as a view. If you are concerned about the differences, please read up on the uses of each before implementing one or the other. If you understand their differences and still want to use a materialized view, good for you.
Jerry
When you create a table or a materialized view, do those columns get created as dates or as another datatype? I suspect GeoWebPublisher may not be able to work with date columns. Otherwise I see no explanation why tables would work but not views.
Hope this helps,
Martin
Yeah I realise that a materialised view is not the same as a view, it is why I tried it, to force it to lose the date references that the view persisted. Not saying I would want to use materialised views going forward, it was only ever meant as a test.