|
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,
fp
WHERE t.language = lv_language
AND t.id = fp.tr_id
AND ROWID() <= 1
);
Specifically, the "with" clause?
|
|
Have you tried it already? I have not but I think it
EDIT: Correction based on M G's answer:
So in this case, the subquery nature of the SELECT seems to prevent the usage of a CTE. 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
|
|
look here in the documentation http://dcx.sybase.com/index.html#sa160/en/dbusage/ug-commontblexpr.html futher down there is an example using with and here it states when you can use common table expressions: http://dcx.sybase.com/index.html#1100/en/dbusage_en11/commontblexpr-s-7010660.html http://dcx.sybase.com/index.html#sa160/en/dbusage/commontblexpr-s-7010660.html 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
|
|
A very late addition I just came across today: While you cannot (still with v 17) use common table expressions (CTEs) with UPDATE statements, there's a workaround in case the table to update has a primary key: The INSERT...SELECT statement with the ON EXISTING UPDATE clause can be used to to UPDATEs as well, and here the SELECT statement can contain common table expressions, so basically one can use INSERT MyTable (column...list) ON EXISTING UPDATE [DEFAULTS ON/OFF] WITH MyCTE as (...).... -- using one or more CTEs SELECT...-- query using the CTEs to generate the new values for the table to be updated... I have currently used that approach with a simple ETL situation: A CTE would list current and up-coming row values next to each other (i.e. the result set would show the current "row contents" and the "up-coming row contents after the import" row by row), and after checking the correctness of the "up-coming row contents", just adding the according INSERT clause and reducing the result set to the new contents, the UPDATE was easily done. |