Guys,

I'm new with sybase, but I have bit of knowledge of DB2. I know that
exist the "WITH" statement in DB2 to create a temporary table during
the query execution. I would like to know if there is something like
this in sybase.


Look the sample in DB2:


with
temp1 as (select pub_name, max(c_price) as maxprice
from book group by pub_name),
temp2 as (select pub_name, maxprice from temp1
where maxprice = (select max(maxprice) from
temp1) )
select case when t.maxprice = t2.maxprice then '*' else '' END,
k.pub_name, isbn, author, title, c_price
from book k, temp1 t , temp2 t2
where k.pub_name = t.pub_name and
k.c_price = t.maxprice
order by k.pub_name;


Thank you

On Jul 2, 6:45*am, Rodrigo Cesar <rbce... (AT) gmail (DOT) com> wrote:
There are a couple things like it -

a) derived tables

select t.id from (select i.id from syobjects o, sysindexes i hwere
o.id = i.id and i.indid = 4) t where t.id > 99

However, if you have multiple derived tables in the FROM clause, they
can't reference each other as your
temp2 references temp1

b) views - you can create and drop views to help simplify your coding.

create view temp1 as (select pub_name, max(c_price) as maxprice
from book group by pub_name)
go
create view temp2 as (select pub_name, maxprice from temp1
where maxprice = (select max(maxprice) from
temp1) )

go
select case when t.maxprice = t2.maxprice then '*' else '' END,
k.pub_name, isbn, author, title, c_price
from book k, temp1 t , temp2 t2
where k.pub_name = t.pub_name and
k.c_price = t.maxprice
order by k.pub_name
go
drop view temp1
drop view temp2
go