Does SQLAnywhere 11 or 12 support this syntax:

UPDATE gtt_sav_query_detail
      SET status_code_description =
         (WITH fp AS
               (SELECT s.translation_id        AS tr_id
                  FROM gtt_sav_query_detail r,
                       s1_settlement_status s
                 WHERE s.status_code = r.status_code
                   AND s.settlement_type = r.settlement_type
                   AND (s.print_check = r.gs_print_flag OR
                        s.print_check IS NULL
                   AND (s.check_void_flag = r.check_void_flag OR
                        s.check_void_flag IS NULL
                   AND (s.payment_method = r.payment_method OR
                        s.payment_method IS NULL
                   AND ((s.check_no = 'Y' AND
                         r.check_no IS NOT NULL
                        ) OR
                        (s.check_no = 'N' AND
                         r.check_no IS NULL
                        ) OR
                        (s.check_no IS NULL)
                   AND ((s.amount = 'Y' AND
                         COALESCE(r.amount,0) > 0
                        ) OR
                        (s.amount = 'N' AND
                         COALESCE(r.amount,0) = 0
                        ) OR
                        (s.amount IS NULL)
          SELECT t.description
               FROM a1_translation t,
              WHERE t.language = lv_language
             AND = fp.tr_id
             AND ROWID() <= 1
Specifically, the "with" clause?

asked 18 Feb '14, 11:50

Murray%20Sobol's gravatar image

Murray Sobol
accept rate: 0%

edited 18 Feb '14, 12:42

Mark%20Culp's gravatar image

Mark Culp

Have you tried it already?

I have not but I think it should be is not allowed because

  1. according to the UPDATE syntax, SET column = expresssion allows a subquery as expression,
  2. a subquery as search condition is a SELECT statement which mustn't return more than one column and row,
  3. and a SELECT statement itself may use a WITH clause to specify a common table expression (CTE).

EDIT: Correction based on M G's answer:

  1. Unfortunately, there's a further condition for CTEs:
    Common table expressions are permitted within top-level SELECT statements, but not within subqueries.

So in this case, the subquery nature of the SELECT seems to prevent the usage of a CTE.
That should explain the error message you are getting.

permanent link

answered 18 Feb '14, 15:37

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 19 Feb '14, 06:47

I have tried it but it gives this error:

Could not execute statement. Syntax error near 'WITH' on line 3 SQLCODE=-131, ODBC 3 State="42000" Line 1, column 4 It points to the "with" part of the SQL.

(18 Feb '14, 16:06) Murray Sobol

The links you provided (in paragraph 1 and 3) does not work. You end up in the sybase documentation but the page only shows

"Page does not exist (404)" and that you should go back to the DocCommentXchange home page.

(19 Feb '14, 02:28) M G
Replies hidden

Thanks for the pointer - in both cases I happened to omit the final "l" from the URL - seems to by my typical cut & paste error:)

(19 Feb '14, 03:37) Volker Barth
permanent link

answered 19 Feb '14, 02:53

M%20G's gravatar image

accept rate: 42%

edited 19 Feb '14, 03:01

Well hidden! :)

Real people say "WITH clause" or "WITH view", and never think in terms of "common table expression" :)

(19 Feb '14, 06:40) Breck Carter

I assure you I am a real person, although I have much more experience with Oracle than SQL Anywhere....

(19 Feb '14, 09:15) M G
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 18 Feb '14, 11:50

question was seen: 13,404 times

last updated: 19 Feb '14, 09:15