Stuck upgrading from MS SQL

[F.Alves]

I am stuck trying to make this SQL piece work in ASE. It
works fine in MS SQL

--drop table #provider
create table #provider(id_prov int not null primary key,
name varchar(10) null)
insert #provider(id_prov, name) values(1,'provider1')
--drop table #provprefix
create table #provprefix(id_prov int not null,ipaddress
varchar(15) null)
insert #provprefix(id_prov,ipaddress) values(1,'1.1.1.1')
insert #provprefix(id_prov,ipaddress) values(1,'2.2.2.2')
insert #provprefix(id_prov,ipaddress) values(1,'3.3.3.3')
insert #provprefix(id_prov,ipaddress) values(1,'4.4.4.4')
select *, (select top 1 ipaddress from #provprefix p where
p.id_prov=#provider.id_prov order by NEWID()) as ip
from #provider where id_prov=1

The idea is that every time I run the select statement, I
get a different value from the second table. Round-Robin
would be ideal but random is fine.

How would you achieve the same effect (ideally in a
round-robin fashion) in ASE? If I cannot use neither the TOP
nor the Order By words in a sub-query, how can I possibly
solve this?

[Neal Stack [Sybase]]

Hello,

Would something like this work?
1> select top 1 ipaddress as ip, pv.id_prov, pv.name from #provprefix p, #provider pv where pv.id_prov=1 and pv.id_prov = p.id_prov order by newid()
2> go
ip id_prov name
--------------- ----------- ----------
1.1.1.1 1 provider1

(1 row affected)
1> select top 1 ipaddress as ip, pv.id_prov, pv.name from #provprefix p, #provider pv where pv.id_prov=1 and pv.id_prov = p.id_prov order by newid()
2> go
ip id_prov name
--------------- ----------- ----------
2.2.2.2 1 provider1

(1 row affected)
1> select top 1 ipaddress as ip, pv.id_prov, pv.name from #provprefix p, #provider pv where pv.id_prov=1 and pv.id_prov = p.id_prov order by newid()
2> go
ip id_prov name
--------------- ----------- ----------
4.4.4.4 1 provider1

(1 row affected)
1> select top 1 ipaddress as ip, pv.id_prov, pv.name from #provprefix p, #provider pv where pv.id_prov=1 and pv.id_prov = p.id_prov order by newid()
2> go
ip id_prov name
--------------- ----------- ----------
3.3.3.3 1 provider1

Regards,
Neal

[F.Alves]

Dear Neal
That would not work because if you read my example, I need
to do that in a subquery, which is allowed in MS SQL and is
not allowed in ASE. I have to do some heavy reprogramming to
susbtitute one line of MS T-SQL for Sybase SQL. It would
benefit the community if somebody from Sybase, such as
yourself, would champion upgrading ASE to the same level of
sofistication that we as SQL developers enjoy on other
platforms. There are several business issues that could be
addressed in one line of code if ASE would support Top and
Order by in subqueries.
Plus of course there are some powerful functions like CUBE
and ROLLUP that totalize "group by" statements that are
missing in the current ASE 15.0.3. And I am not mentioning
RANK functions, etc. However, all in all, ASE is fantastic
in performance and overall stability, plus it runs in Linux
and several other platforms.
Many thanks for your help.

[Neal Stack [Sybase]]

Hello,

I think there is an outstanding feature request for the CUBE:
451306 - request to support group by rollup/ group by cube

I found one feature request that covers at least one of Microsoft's ranking functions:
460975 - new request is like this ROW_NUMBER() function

(I updated it to include all of Microsoft's ranking functions (RANK, NTILE, DENSE_RANK, ROW_NUMBER)).

Regards,
Neal