Usage and Examples of Asterisks in select Statements

SAP ASE automatically replaces asterisks in queries with actual column names before saving new stored procedures, views, and triggers.

This replacement persists even if you alter a table to add columns. SAP ASE does not allow more than one column, however, when the replacement of the asterisk introduces an additional column. This incorrect behavior persists until you drop and re-create the text. For example:

1> create table t1(c1 int, c2 int)
2> go
1> create table t2(c1 int)
2> go
1> create proc p1
2> as
3> select * from t1 where c1 in (select * from t2)
4> go
1> exec p1
2> go
 c1          c2
 ----------- ----------- 

(0 rows affected)
(return status = 0)

1> sp_helptext p1
2> go
# Lines of Text 
 --------------- 
               2

(1 row affected)
text
------------------------------------------------------------------------------------------------------------
create proc p1
as/* SAP ASE has expanded all '*' elements in the following statement */

select t1.c1, t1.c2
    from t1 where c1 in (select t2.c1 from t2)

(2 rows affected)
(return status = 0)
1> alter table t2 add c2 int null
2> go
1> exec p1
2> go
 c1          c2
 ----------- -----------

(0 rows affected)
(return status = 0)

1> exec p1 with recompile
2> go
 c1          c2
 ----------- -----------

(0 rows affected)
(return status = 0)
1> drop proc p1
2> go
1> create proc p1
2> as
3> select * from t1 where c1 in (select * from t2)
4> go
Msg 299, Level 16, State 1:
Procedure 'p1', Line 4:
The symbol '*' can only be used for a non-EXISTS 
subquery select list when the subquery is on a single 
table with a single column.

SAP ASE expects the asterisk to resolve to a single column, and generates an error when it encounters more than one column after it converts the asterisk.

Examples

Examples of using an asterisk in nested select statements.

This example deletes any discount from stores that have no sales, or that have discounts greater than 10:

create view store_with_nosales(stor_id)
as
select stores.stor_id
from stores left join sales 
     on stores.stor_id = sales.stor_id 
where sales.stor_id IS NULL
go

delete from discounts
where (stor_id in (select * 
          from store_with_nosales)
     or discount > 10.0)
go

This example returns an error because there is more than one column in the join between stores and sales:

create view store_with_nosales(stor_id)
as
select stores.stor_id
from stores left join sales 
     on stores.stor_id = sales.stor_id 
where (stor_id in (select * 
       from stores left join sales 
            on stores.stor_id = sales.stor_id 
        where sales.stor_id IS NULL)
  or discount > 10.0)
go

delete from discounts
where (stor_id in (select * 
          from store_with_nosales)
     or discount > 10.0)
go

Msg 299, Level 16, State 1:
Line 1:
The symbol '*' can only be used for a subquery select
list when the subquery is introduced with EXISTS or NOT 
EXISTS or the subquery references a single table and 
column.