6

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)

Ed Harper
  • 452
  • 5
  • 11
  • `select first field1, field2 from exampleTable where field1 = '1';` --- what this expression is supposed to do? It doesn't pass selected fields to somewhere. –  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? –  Jan 31 '12 at 10:36
  • don't know exactly why, but without `into` selects make no sense –  Jan 31 '12 at 10:47
  • Interesting. FYI, the error is described here http://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? –  Jan 31 '12 at 11:35
  • @zerkms without an into it is selecting to the display/output – JamesRyan Jan 31 '12 at 12:52

2 Answers2

4

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.

JamesRyan
  • 658
  • 3
  • 14
0

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

msi77
  • 1,105
  • 1
  • 7
  • 9