Why I cannot use this?

declare @ln_x int
select @ln_x = x.col from ( select max(id) as col from table ) x

Server Message: Number 141, Severity 15
Server 'U00025_D', Line 2:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

but this works...

select x.col from ( select max(id) as col from table ) x

This example is trivial but I would like to use these constructions in my stored procedures...

e.g.

select .... from ( select ... from table ... ) a , table b, ( select ... from table ) c
where a.id = b.id
and c.id = b.id
and ...

or should I avoid these constlructions and use somthing else? Temporary tables (but if I am in transactions?)...

I am unable to recreate your problem

> declare @ln_x int
> select @ln_x = x.col from ( select max(id) as col from sysobjects ) x
> select lnx=@ln_x

Server Message: Number 141, Severity 15
Server 'U00025_D', Line 2:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


> select x.col from ( select max(id) as col from sysobjects ) x

col
-----------
2147460116



> select @@version

Adaptive Server Enterprise/12.5.3/EBF 13337 ESD#7/P/ia64/HP-UX B.11.23/ase1253/1951/64-bit/OPT/Fri Mar 24 00:47:27 2006

Successful on ASE 15.0.3 and 12.5.4
I don't have an older version to test on

Tested it on ASE12.5.0.3 and 12.5.3. The first gives an nicorrect syntaxt near select and ), the last gives Musil David's error.

Thanks... I ll upgrade version...

One more question what about effectivity of these kind of commands, are these more effective than

a ) division in more commands working with temporary tables...

b) division in more commands working with db tables...