Ordering by column position generates error if called from within SP – ASE 16.0.1.3
The following code:
create table test1 (a int, b int, c int)
create table test2 (a int, b int, c int)
create proc sp_test
as
begin
select a.a, a.b from test1 a where a.a > 1 and a.a is not null
and exists (select 1 from test2 b where b.a = a.a)
order by 1, 2
return 0
end
will work on ASE versions 15, 16.0.1.2, 16.0.2.2 but not on 16.0.1.3:
Msg 207, Level 16, State 4:
Server ‘XXX’, Procedure ‘sp_test’, Line 4:
Invalid column name ‘a’.
Msg 207, Level 16, State 4:
Server ‘XXX’, Procedure ‘sp_test’, Line 4:
Invalid column name ‘a’.
1>
- Depending on how many conditions are in the where clause
- Depending whether exists is present
- Depending it sorting is done by position
Did anyone come across this?
The same statement run from outside SP will work correctly:
select a.a, a.b from test1 a where a.a > 1 and a.a is not null
and exists (select 1 from test2 b where b.a = a.a)
order by 1, 2
a b
———– ———–
(0 rows affected)
Took me a while to understand what the problem is….
HTH,
Andrew
Andrew,
I would simplify the stored procedure and use the brute force approach to refferencing the table name. In other words, drop the aliases. E.g.
create proc sp_test
as
begin
select test1.a, test1.b from test1 where test1.a > 1 and test1.a is not null
and exists (select 1 from test2 where test2.a = test1.a)
order by 1, 2
return 0
end
Let us know if that works.
Regards,
Jean-Pierre
P.S. Why did you post this as a BLOG?