Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

if you execute a statement like this:

select first field1, field2 from exampleTable where field1 = '1';

then you get a warning like this:

The result returned is non deterministic SQLCode=122

So far it's OK, because in this case you need an "order by" clause here.

But when you use the same statement inside of a BEGIN END block putting the values into some declared variables,

BEGIN
declare varField1 varchar;
declare varField2 varchar;

select first field1, field2 into varField1, varField2 
from exampleTable 
where field1 = '1';

END

the warning does not come up! Why?

Is the "order by" clause not needed here? Is this statement suddenly deterministic?...

(using SQL Anywhere 12)

share|improve this question

migrated from stackoverflow.com Jan 31 '12 at 12:16

This question came from our site for professional and enthusiast programmers.

    
select first field1, field2 from exampleTable where field1 = '1'; --- what this expression is supposed to do? It doesn't pass selected fields to somewhere. –  zerkms Jan 31 '12 at 10:31
1  
well, it's a simple select statement... I don't understand your question... ;-) the point is it throws a warning. the lower one, does not. Why? –  axel foley Jan 31 '12 at 10:36
    
don't know exactly why, but without into selects make no sense –  zerkms Jan 31 '12 at 10:47
    
Interesting. FYI, the error is described here dcx.sybase.com/1201/en/saerrors/err122.html –  gbn Jan 31 '12 at 11:07
4  
could it be the tool, that you're using, not being able to collect and show SQL warnings when running within a procedural block? –  Vlad Jan 31 '12 at 11:35
show 1 more comment

2 Answers

I believe that any select statement from base table is non deterministic due to the data which can change between calls.

share|improve this answer
add comment

Rows are not returned in any specific order in SQL (in reality they are likely to be in the same order most of the time) so without an ORDER BY any of the rows could be returned first. So it is non deterministic because this first record can change each time you run the query.

Wrapping it in a block doesn't make the answer deterministic, it just hides the warning.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.