|
I am quite willing to omit 'rowid' from my materialized view... in fact, I have never even considered including rowid, but even when I obey the rules, I get this in SQL Anywhere 16 16.0.0.2344... Could not execute statement. Syntax error near 'rowid' -- Materialized view definition must not use the following construct: 'User defined or builtin functions' SQLCODE=-1031, ODBC 3 State="HY000" Line 1, column 1 CREATE MATERIALIZED VIEW rroad_mv_alert_union AS
SELECT 'Alert' AS record_type,
rroad_alert.sampling_id AS sampling_id,
rroad_sampling_options.selected_tab AS selected_tab,
rroad_sampling_options.selected_name AS selected_name,
rroad_alert.sample_set_number AS sample_set_number,
rroad_alert.alert_number AS alert_number,
rroad_alert.alert_in_effect_at AS recorded_at,
rroad_alert.alert_description AS alert_description,
rroad_alert.email_status AS email_status,
IF rroad_all_clear.alert_is_clear IS NOT NULL
THEN rroad_all_clear.alert_is_clear
ELSE IF rroad_alert_cancelled.alert_is_cancelled IS NULL
THEN rroad_alert_cancelled.alert_is_cancelled
ELSE 'N'
END IF
END IF AS alert_is_clear_or_cancelled
FROM rroad_alert
INNER JOIN rroad_sampling_options
ON rroad_sampling_options.sampling_id = rroad_alert.sampling_id
LEFT OUTER JOIN ( SELECT all_clear_occurrence,
sampling_id,
sample_set_number,
alert_number,
alert_all_clear_at,
alert_in_effect_at,
alert_description,
email_status,
'Y' AS alert_is_clear
FROM rroad_all_clear
) AS rroad_all_clear
ON rroad_all_clear.sampling_id = rroad_alert.sampling_id
AND rroad_all_clear.alert_number = rroad_alert.alert_number
AND rroad_all_clear.alert_in_effect_at = rroad_alert.alert_in_effect_at
LEFT OUTER JOIN ( SELECT alert_cancelled_occurrence,
sampling_id,
sample_set_number,
alert_number,
alert_all_clear_at,
alert_in_effect_at,
alert_description,
email_status,
'Y' AS alert_is_cancelled
FROM rroad_alert_cancelled
) AS rroad_alert_cancelled
ON rroad_alert_cancelled.sampling_id = rroad_alert.sampling_id
AND rroad_alert_cancelled.alert_number = rroad_alert.alert_number
AND rroad_alert_cancelled.alert_in_effect_at = rroad_alert.alert_in_effect_at
UNION ALL
SELECT 'All Clear' AS record_type,
rroad_all_clear.sampling_id AS sampling_id,
rroad_sampling_options.selected_tab AS selected_tab,
rroad_sampling_options.selected_name AS selected_name,
rroad_all_clear.sample_set_number AS sample_set_number,
rroad_all_clear.alert_number AS alert_number,
rroad_all_clear.alert_all_clear_at AS recorded_at,
rroad_all_clear.alert_description AS alert_description,
rroad_all_clear.email_status AS email_status,
'Y' AS alert_is_clear_or_cancelled -- FIXED
FROM rroad_all_clear
INNER JOIN rroad_sampling_options
ON rroad_sampling_options.sampling_id = rroad_all_clear.sampling_id
UNION ALL
SELECT 'Cancelled' AS record_type,
rroad_alert_cancelled.sampling_id AS sampling_id,
rroad_sampling_options.selected_tab AS selected_tab,
rroad_sampling_options.selected_name AS selected_name,
rroad_alert_cancelled.sample_set_number AS sample_set_number,
rroad_alert_cancelled.alert_number AS alert_number,
rroad_alert_cancelled.alert_all_clear_at AS recorded_at,
rroad_alert_cancelled.alert_description AS alert_description,
rroad_alert_cancelled.email_status AS email_status,
'Y' AS alert_is_clear_or_cancelled -- FIXED
FROM rroad_alert_cancelled
INNER JOIN rroad_sampling_options
ON rroad_sampling_options.sampling_id = rroad_alert_cancelled.sampling_id
UNION ALL
SELECT 'Placeholder' AS record_type,
rroad_sampling_options.sampling_id AS sampling_id,
rroad_sampling_options.selected_tab AS selected_tab,
rroad_sampling_options.selected_name AS selected_name,
0 AS sample_set_number,
0 AS alert_number,
'1900-01-01' AS recorded_at,
'' AS alert_description,
'' AS email_status,
'?' AS alert_is_clear_or_cancelled
FROM rroad_sampling_options
WHERE NOT EXISTS ( SELECT 1
FROM rroad_alert
WHERE rroad_alert.sampling_id = rroad_sampling_options.sampling_id )
AND NOT EXISTS ( SELECT 1
FROM rroad_all_clear
WHERE rroad_all_clear.sampling_id = rroad_sampling_options.sampling_id )
AND NOT EXISTS ( SELECT 1
FROM rroad_alert_cancelled
WHERE rroad_alert_cancelled.sampling_id = rroad_sampling_options.sampling_id )
CHECK IMMEDIATE REFRESH;
|
|
When I was experimenting with materialised views, I got caught out by an underlying table having a COMPUTE (or was it DEFAULT?) clause that used a function - could that be your problem? Eventually I gave up on materialized views as the restrictions were too onerous to make it useful for us - we were trying to optimise reporting access a lot of underlying complex stuff which included user-defined functions etc. Ended up with a DIY system based on triggers, events and global shared temp tables which was much more flexible if harder to implement. 1
There were a lot of those (simple function calls) but none remain, and never rowid. I agree with you about giving up on materialized views... over the years I have made good-faith efforts to use them only to be met with failure... if the docs existed on paper I'd be ripping out those pages :) > DIY system based on triggers Me too!
(21 Sep '17, 09:55)
Breck Carter
Replies hidden
Yes it's a shame. I don't know whether these restrictions are generally the case with other RDMSs. A quick look at Oracle suggests that, for example, only /non-repeatable/ built-in or user defined functions (eg something that uses CURRENT DATE) would be disallowed.
(21 Sep '17, 15:32)
Justin Willey
I wonder whether this has to do with automatic view matching (a use case I was not originally aware of until Glenn explained it here), and I would like to know whether you are planning to view the materialized view explicitly (say, as source for a report/statistics), or expect the engine to use it automatically?
(21 Sep '17, 17:10)
Volker Barth
1
I was planning to use the materialized view explicitly. I don't trust explicit view matching for the following reason: plans are determined on-the-fly and there's nothing stopping SQL Anywhere from sometimes using it and sometimes not, possibly depending on how many plans are considered for any given query execution. All of this is moot, since I have never been able to create a non-trivial materialized view, and probably never will.
(22 Sep '17, 08:14)
Breck Carter
2
FWIW, in this particular case, the alternative to the materialized view was much simpler: A single derived column was added to a parent table, and maintained by INSERT, UPDATE triggers on child tables. This new column made a complex Subquery From Hell entirely unnecessary (and hence, made a complex materialized view moot). In conclusion... this materialized view was the Lazy Way Out, and I'm glad it didn't work :)
(22 Sep '17, 08:22)
Breck Carter
Like Breck, I had hoped to use materialized views explicitly, really as a form of limited data warehousing of lightly de-normalised data for reporting.
(22 Sep '17, 11:02)
Justin Willey
|
