Lets say I have a simple proxy table:

CREATE EXISTING TABLE prx AT 'remote_server;;dba;tbl';

If I filter it with SIMILAR TO:

SELECT * FROM prx WHERE prx.col SIMILAR TO 'a'

I get following error on SA version 17.0.9.4897:

Could not execute statement.
Server '': e:\1709_rc2\sqla\src\db\engine\omni\qdexpr.c:1622
Query
Decomposition:  Unknown Node Type
SQLCODE=-660, ODBC 3 State="HY000"
Line 1, column 1

If I change SIMILAR TO to LIKE I do not get the error. Same error on version 16.0.0.2614. Any ideas?

asked 21 Mar '19, 09:16

Valdas's gravatar image

Valdas
381161929
accept rate: 83%

So what's your remote server class - another SQL Anywhere server? (The proxy location with semicolon as delimiter puzzles me...)

FWIW, you can "debug" remote data access with the help of the "cis_option" option. I guess that a LIKE condition is passed through to the remote server, a "SIMILAR TO" expression probably not...

(22 Mar '19, 04:24) Volker Barth

I'm guessing you are using IQ, and it doesn't support SIMILAR TO (at least, IQ 15.2 doesn't): Sybase IQ 15.2 > Reference: Building Blocks, Tables, and Procedures > SQL Language Elements

In other words, SQL Anywhere is pushing the entire query over to IQ, where LIKE will work but not SIMILAR TO.

If the table isn't too big, and/or it's static, one workaround would be to copy (INSERT SELECT) the entire contents into a local (non-proxy) SQL Anywhere table and then do the SIMILAR TO.

Or, pull SOME of the table across (keep the other predicates in the WHERE but remove SIMILAR TO) and then do the SIMILAR TO.

(22 Mar '19, 06:38) Breck Carter
Be the first one to answer this question!
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:

×260
×240
×69

question asked: 21 Mar '19, 09:16

question was seen: 1,066 times

last updated: 22 Mar '19, 06:43