Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved SQL Anywhere 17 Mystery Error

  1. How-to
0
Votes
Undo
  1. Jeff Wayt
    Jeff Wayt
  2. PowerBuilder
  3. Wednesday, 20 March 2024 15:03 PM UTC

SQL Anywhere 17 gives this error but I don't see any user or builtin functions. I removed aggregate functions and IsNull() and still get the complaint. What offends?

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 3, column 1

 

Create materialized view DBA.PCC_Option_DVTRisk_Mview  as
SELECT 
        currentPCC.pcc_id,
        currentPCC.product_family_option_id, 
        currentPCC.DVT_Status AS DVT_Risk_Factor,

        currentPCC.Built AS Same_Rev_Built, 
        currentPCC.DVT_Status AS DVT_Status,
        currentPCC.Shipped AS Same_Rev_Shipped, 

        otherRevPCC.Built AS Other_Rev_Built, 
        otherRevPCC.DVT_status AS Other_Rev_DVTd,
        otherRevPCC.Shipped AS Other_Rev_Shipped

FROM 
(
        SELECT DBA.PCC.pcc_id, 
                DBA.product_families.product_code, 
                DBA.product_families.rev_char, DBA.PCC.option_code,
                DBA.product_family_options.short_name,
                product_family_option_values.product_family_option_id, 
                 DBA.product_family_option_values."value",
                CASE DVT_Status WHEN 'PASSED' THEN 0 WHEN 'FAILED' THEN 3 ELSE 1 END DVT_status,
        built, shipped
        FROM     DBA.product_families, DBA.product_family_options, DBA.PCC, DBA.product_family_option_values
                LEFT OUTER JOIN product_option_value_list ON 
                        product_family_option_values.product_family_option_id = DBA.product_option_value_list.product_family_option_id AND
                        DBA.product_family_option_values."value" = DBA.product_option_value_list."value"
                LEFT OUTER JOIN product_instance ON product_option_value_list.product_id = product_instance.product_id
        WHERE  DBA.product_families.product_family_id = DBA.product_family_options.product_family_id AND DBA.product_family_options.product_family_option_id = DBA.product_family_option_values.product_family_option_id AND 
                                          DBA.product_family_option_values.pcc_id = DBA.PCC.pcc_id AND (DBA.product_family_options.Mfg_property = 0 AND product_family_options.Active = 1) 

) currentPCC

LEFT JOIN (
        SELECT DBA.product_option_value_list.product_code,
                DBA.product_family_options.short_name, DBA.product_families.rev_char, 
        DBA.product_family_option_values."value", 
        CASE DVT_Status WHEN 'PASSED' THEN 0 WHEN 'FAILED' THEN 3 ELSE 1 END DVT_status,
                built, shipped
        FROM     DBA.product_families, DBA.product_family_options, 
        DBA.product_family_option_values, DBA.product_option_value_list
        WHERE  DBA.product_families.product_family_id = DBA.product_family_options.product_family_id 
                AND product_option_value_list.product_code = DBA.product_families.product_code        
                AND DBA.product_family_options.product_family_option_id = DBA.product_family_option_values.product_family_option_id 
                AND DBA.product_family_options.Mfg_property = 0
                AND DBA.product_family_option_values."value" = DBA.product_option_value_list."value"

) otherRevPCC
        ON currentPCC.short_name = otherRevPCC.short_name AND currentPCC."value" = otherRevPCC."value"
        AND currentPCC.product_code = otherRevPCC.product_code 
        AND currentPCC.rev_char <> otherRevPCC.rev_char 

CHECK IMMEDIATE REFRESH
  1. SQL Anywhere
Internal Browser very slow
SMTPClient OnSendFinished Event
Responses (5)
Jeff Wayt
Jeff Wayt Accepted Answer Pending Moderation
  1. Thursday, 21 March 2024 11:01 AM UTC
  2. PowerBuilder
  3. # 1

I did check underlying tables. There are no functions, per se, but most of these tables have a primary key integer with autoincrement. Would that count?

If this is a barrier, I can see why some have abandoned Materialized Views as a solution. Too many constraints to be practical. Does anyone have Materialized Views with aggregate functions like MAX or MIN? My live view had them and I removed them to see if that was the issue, but the error message persists.

The error message is as useful as a Check Engine light.

Thank you for your suggestion. <3

Comment
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Thursday, 21 March 2024 11:42 AM UTC
  2. PowerBuilder
  3. # 2

What I can say is that there is a specific topic in sql anywhere documentation about materialized views restrictions. There you can see that:

When creating a materialized view, the definition for the materialized view cannot contain:

  1. references to other views, materialized or not
  2. references to remote or temporary tables
  3. variables such as CURRENT USER; all expressions must be deterministic
  4. calls to stored procedures, user-defined functions, or external functions
  5. Transact-SQL outer joins
  6. FOR XML clauses

I can say that your statements seems to be ok with points 1, 2, 5, 6. Also you did stated that you removed functions, so you must also be ok with point 4. For point 3 you may need to check (even in tables used in your query, not only for functions, but also for such variables? Don't know if autoincrement is causing the issue, but it would be easy to check).

Also, it is mentioned that:

The following database options must have the specified settings when a materialized view is created; otherwise, an error is returned. These database option values are also required for the view to be used by the optimizer:

  • ansinull=On
  • conversion_error=On
  • divide_by_zero_error=On
  • sort_collation=Internal
  • string_rtruncation=On

That's all what I can tell based on the given information.

Andreas.

Comment
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Thursday, 21 March 2024 10:40 AM UTC
  2. PowerBuilder
  3. # 3

Hi.

The problem may be on the tables used by your materialized view. Check the tables syntax. Are you using any defaults that could have dependencies on functions?

Andreas.

Comment
There are no comments made yet.
Jeff Wayt
Jeff Wayt Accepted Answer Pending Moderation
  1. Thursday, 21 March 2024 12:10 PM UTC
  2. PowerBuilder
  3. # 4

Andreas, thank you for your thorough answer. I did have to set string_rtruncation=On and there were no other errors on settings.

The underlying tables are very plain definitions. No functions like default getdate() or the like. No specialized check constraints.

What a nightmare of error feedback, not a useful line number to inspect. It is an object lesson for what not to do in application feedback design.

 

Comment
Andreas Mykonios
  1. Andreas Mykonios
  2. Thursday, 21 March 2024 13:08 PM UTC
Were able to solve the issue? Or you still cannot create the view?

Andreas.
  1. Helpful
Jeff Wayt
  1. Jeff Wayt
  2. Thursday, 21 March 2024 13:10 PM UTC
Not yet.
  1. Helpful
There are no comments made yet.
André Monz
André Monz Accepted Answer Pending Moderation
  1. Thursday, 21 March 2024 13:52 PM UTC
  2. PowerBuilder
  3. # 5

Hi,

maybe you find here more help
https://sqlanywhere-forum.sap.com/

hth

André

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.