12.0.1.3851

Sybase Central - Tools - SQL Anywhere 12 - Compare Databases...

An unknown error has occurred.
Unrecognized sql in CREATE TABLE statement: "end)
   ,"canarian_query_elapsed_msec"    bigint NOT NULL COMPUTE (case when ABS(DATEDIFF(year,canarian_query_started_at,canarian_query_finished_at)) >= 4083 then
  cast(DATEDIFF(hour,canarian_query_started_at,canarian_query_finished_at) as bigint)*60*60*1000
when ABS(DATEDIFF(year,canarian_query_started_at,canarian_query_finished_at)) >= 68 then
  cast(DATEDIFF(minute,canarian_query_started_at,canarian_query_finished_at) as bigint)*60*1000
when ABS(DATEDIFF(day,canarian_query_started_at,canarian_query_finished_at)) >= 24 then
  cast(DATEDIFF(second,canarian_query_started_at,canarian_query_finished_at) as bigint)*1000
else DATEDIFF(millisecond,canarian_query_started_at,canarian_query_finished_at)
end)
   ,"sample_started_at"              timestamp NOT NULL DEFAULT '1900-01-01'
   ,"sample_finished_at"             timestamp NOT NULL DEFAULT '1900-01-01'
   ,"canarian_query_started_at"      timestamp NOT NULL DEFAULT '1900-01-01'
   ,"canarian_query_finished_at"     timestamp NOT NULL DEFAULT '1900-01-01'
   ,"datediff_msec_between_target_and_local" bigint NOT NULL DEFAULT 0
   ,PRIMARY KEY ("sample_set_number" ASC) 
)
"
Unrecognized sql in CREATE TABLE statement: "end)
   ,"canarian_query_elapsed_msec"    bigint NOT NULL COMPUTE (case when ABS(DATEDIFF(year,canarian_query_started_at,canarian_query_finished_at)) >= 4083 then
  cast(DATEDIFF(hour,canarian_query_started_at,canarian_query_finished_at) as bigint)*60*60*1000
when ABS(DATEDIFF(year,canarian_query_started_at,canarian_query_finished_at)) >= 68 then
  cast(DATEDIFF(minute,canarian_query_started_at,canarian_query_finished_at) as bigint)*60*1000
when ABS(DATEDIFF(day,canarian_query_started_at,canarian_query_finished_at)) >= 24 then
  cast(DATEDIFF(second,canarian_query_started_at,canarian_query_finished_at) as bigint)*1000
else DATEDIFF(millisecond,canarian_query_started_at,canarian_query_finished_at)
end)
   ,"sample_started_at"              timestamp NOT NULL DEFAULT '1900-01-01'
   ,"sample_finished_at"             timestamp NOT NULL DEFAULT '1900-01-01'
   ,"canarian_query_started_at"      timestamp NOT NULL DEFAULT '1900-01-01'
   ,"canarian_query_finished_at"     timestamp NOT NULL DEFAULT '1900-01-01'
   ,"datediff_msec_between_target_and_local" bigint NOT NULL DEFAULT 0
   ,PRIMARY KEY ("sample_set_number" ASC) 
)
"

Here is the original source code used to create the offending table:

   CREATE TABLE rroad_sample_set (
      sampling_id                              UNSIGNED INTEGER NOT NULL, -- FK relationship not maintained: REFERENCES rroad_sampling_options ( sampling_id ),
      sample_set_number                        UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT,
      sample_lost                              VARCHAR ( 1 ) NOT NULL DEFAULT 'N',
      connected_ok                             VARCHAR ( 1 ) NOT NULL DEFAULT 'Y', -- set to N for a lost sample
      exception_description                    VARCHAR ( 32767 ) NOT NULL DEFAULT '',
      sample_elapsed_msec                      BIGINT NOT NULL COMPUTE ( 
                                                  CASE 
                                                     WHEN ABS ( DATEDIFF ( YEAR, sample_started_at, sample_finished_at ) ) >= 4083
                                                        THEN CAST ( DATEDIFF ( HOUR, sample_started_at, sample_finished_at ) AS BIGINT ) * 60 * 60 * 1000
                                                     WHEN ABS ( DATEDIFF ( YEAR, sample_started_at, sample_finished_at ) ) >= 68
                                                        THEN CAST ( DATEDIFF ( MINUTE, sample_started_at, sample_finished_at ) AS BIGINT ) * 60 * 1000
                                                     WHEN ABS ( DATEDIFF ( DAY, sample_started_at, sample_finished_at ) ) >= 24
                                                        THEN CAST ( DATEDIFF ( SECOND, sample_started_at, sample_finished_at ) AS BIGINT ) * 1000
                                                     ELSE DATEDIFF ( MILLISECOND, sample_started_at, sample_finished_at )
                                                  END ),
      canarian_query_elapsed_msec              BIGINT NOT NULL COMPUTE ( 
                                                  CASE 
                                                     WHEN ABS ( DATEDIFF ( YEAR, canarian_query_started_at, canarian_query_finished_at ) ) >= 4083
                                                        THEN CAST ( DATEDIFF ( HOUR, canarian_query_started_at, canarian_query_finished_at ) AS BIGINT ) * 60 * 60 * 1000
                                                     WHEN ABS ( DATEDIFF ( YEAR, canarian_query_started_at, canarian_query_finished_at ) ) >= 68
                                                        THEN CAST ( DATEDIFF ( MINUTE, canarian_query_started_at, canarian_query_finished_at ) AS BIGINT ) * 60 * 1000
                                                     WHEN ABS ( DATEDIFF ( DAY, canarian_query_started_at, canarian_query_finished_at ) ) >= 24
                                                        THEN CAST ( DATEDIFF ( SECOND, canarian_query_started_at, canarian_query_finished_at ) AS BIGINT ) * 1000
                                                     ELSE DATEDIFF ( MILLISECOND, canarian_query_started_at, canarian_query_finished_at )
                                                  END ),
      sample_started_at                        TIMESTAMP NOT NULL DEFAULT '1900-01-01',
      sample_finished_at                       TIMESTAMP NOT NULL DEFAULT '1900-01-01',
      canarian_query_started_at                TIMESTAMP NOT NULL DEFAULT '1900-01-01',
      canarian_query_finished_at               TIMESTAMP NOT NULL DEFAULT '1900-01-01',
      datediff_msec_between_target_and_local   BIGINT NOT NULL DEFAULT 0,
      PRIMARY KEY ( sample_set_number ) );

asked 28 Feb '13, 10:47

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 28 Feb '13, 10:53


The problem here is the multi-line COMPUTE() expression with nested parentheses. The expression is perfectly valid, but the Compare Database window can't parse it. For now, I've added a fix to version 16.0.0 (the fix will appear in the first EBF) to skip such CREATE TABLE statements.

A complete fix to actually parse the statement and compare it with the corresponding CREATE TABLE statement in the other database requires more significant changes, so these changes will come in a later EBF.

permanent link

answered 12 Mar '13, 21:29

Rich%20Jones's gravatar image

Rich Jones
35146
accept rate: 66%

1

This is now fully fixed in version 16.0.0. The full fix (which correctly parses COMPUTE expressions containing nested parentheses, rather than ignoring them) will appear in the second EBF.

The bug number is 738312.

(25 Apr '13, 23:25) Rich Jones
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106

question asked: 28 Feb '13, 10:47

question was seen: 1,862 times

last updated: 26 Apr '13, 07:55