Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Sybase SQL Anywhere 12.0.1.4127

This script to reproduce the problem:

CREATE TABLE "DBA"."TEST" (
        "ID" INTEGER NOT NULL,
        "FIELD1" CHAR(10) NULL,
        "FIELD2" CHAR(10) NULL,
        PRIMARY KEY ( "ID" )
)
go

CREATE FUNCTION "DBA"."MYFUNC1"(in cVar char(10)) 
RETURNS char(10)
DETERMINISTIC
BEGIN
 DECLARE cRetOut char(10);
 SET cRetOut = cVar;
 RETURN cRetOut;
END
go

Execute SQL-query

select distinct
 ID,
 FIELD1,
 FIELD2
from dba.TEST
order by dba.MYFUNC1(FIELD2)

why the error occurs:

Could not execute statement. Function or column reference to 'dba' in the ORDER BY clause is invalid SQLCODE=-854, ODBC 3 State="42000"

although if you run the same query, but in the section "order by", specify the UDF without owner, there is no error:

select distinct
 ID,
 FIELD1,
 FIELD2
from dba.TEST
order by MYFUNC1(FIELD2)

At the same time in Sybase SA 8.0.3.5594 both SQL-query executed without error.

asked 10 Sep '14, 06:56

Stalker's gravatar image

Stalker
515293151
accept rate: 11%

1

FWIW 16.0.0.1915 has the same symptom... it's hard to imagine an explanation that does not contain the word "bug" :)

alt text

(10 Sep '14, 07:28) Breck Carter

So that this error will be corrected ?

(19 Sep '14, 08:32) Stalker

Thank you for the bug report. I have opened this as CR #771044 with our development group.

permanent link

answered 19 Sep '14, 12:54

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

It turned out there has an update that fixes this bug ? What is his number (build) ?

permanent link

answered 27 May '15, 10:46

Stalker's gravatar image

Stalker
515293151
accept rate: 11%

CR#771044 was fixed in 16.0.0.2017 and 12.0.1.4165. 16.0 SP21 and greater should have this fix, as well as 12.0 SP81 and later (for Windows).

(27 May '15, 13:02) Mikel Rychliski
    ================(Build #4165  - Engineering Case #771044)================

    The server may have incorrectly returned the error: 'Function or column reference 
    to '<user-name>' in the ORDER BY clause is invalid', if a select statement 
    used a user-defined function with an owner name in the ORDER BY clause; e.g. 
    ORDER BY <user-name>.<function-name(...). this="" has="" been="" fixed.="" <="" pre="">
(27 May '15, 13:22) Breck Carter

Can You give a link to this fix (EBF) or a link to SQLA1201_Developer.ehe which includes this fix ?

permanent link

answered 28 May '15, 07:49

Stalker's gravatar image

Stalker
515293151
accept rate: 11%

The good news: SQL Anywhere can show you how to download that EBF:
Sybase Central -> Help -> SQL Anywhere 12 -> Check for Updates

The bad news: you'll need a valid login (S-ID) for the SAP Service Marketplace.
Lacking that I don't know an alternative.

(28 May '15, 10:46) Reimer Pods
Replies hidden

Check for Updates

Though that seems often not to point out to the newest EBFs... E.g. I have 12.0.1.4216 (SP86) installed on my box and the Check tells me that there is no newer version, although 12.0.1.4231 (SP88) for that platform (Windows) has been available since early March 2015...

(28 May '15, 10:56) Volker Barth

The fact that the problem is that I do not support - very much it is expensive.

Maybe then You (developers ASA) can issue an updated release SQLA1201_Developer.exe which will include the latest fixes ?

P.S. The current release includes SQLA1201_Developer.exe only EBF 12.0.1.4134

permanent link

answered 29 May '15, 10:21

Stalker's gravatar image

Stalker
515293151
accept rate: 11%

converted 02 Jun '15, 10:55

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:

×438
×95
×5

question asked: 10 Sep '14, 06:56

question was seen: 4,905 times

last updated: 29 May '15, 10:21