Syntax error near 'rowid' -- Materialized view definition

[Breck_Carter]

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;

[justin_willey]

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.

[Breck_Carter]

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!

[justin_willey]

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.

[VolkerBarth]

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?

[Breck_Carter]

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.

[Breck_Carter]

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 🙂

[justin_willey]

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.