Skip to Content

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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Jean-Pierre Dareys

    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?

    (0) 

Leave a Reply