Results 1 to 6 of 6

Thread: Virtual tables?

  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: Virtual tables?

    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?)...

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I am unable to recreate your problem
    Code:
    1> declare @ln_x int
    2> select @ln_x = x.col from ( select max(id) as col from sysobjects ) x
    3> select lnx=@ln_x
    4> go
    (1 row affected)
     lnx         
     ----------- 
      2144007638 
    
    (1 row affected)

  3. #3
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    > 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

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Successful on ASE 15.0.3 and 12.5.4
    I don't have an older version to test on

  5. #5
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    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.
    I'm not crazy, I'm an aeroplane!

  6. #6
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •