Skip to Content
Author's profile photo Andrew Melkonyan

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>

  1. Depending on how many conditions are in the where clause
  2. Depending whether exists is present
  3. 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

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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?