Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
0 Kudos
389

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

1 Comment
Labels in this area