SQL Server 2005: I need help with this SQL Query

I've been trying a lot of different ways but I have no idea how to get it to run...

Query:
create view revenue[STREAM_ID] (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= '1996-01-01'
and l_shipdate < dateadd(month, 3, '1996-01-01')
group by
l_suppkey;
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue[STREAM_ID]
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue[STREAM_ID]
)
order by
s_suppkey;
drop view revenue[STREAM_ID];


Error I'm getting:
Msg 102, Level 15, State 1, Procedure revenue, Line 1
Incorrect syntax near 'STREAM_ID'.
Msg 102, Level 15, State 1, Procedure revenue, Line 31
Incorrect syntax near 'STREAM_ID'.

Any suggestions?

That is interesting syntax. What DBMS is this for, and are you sure you didn't want a stored procedure, not a view?

It's for SQL Server 2005, I'm not sure, all I was asked was to get it to run, I've already gotten 21 running out of 22, this is the only one I'm clueless about

I got it... I was doing it right, I just needed to add a go... that was dumb hehe

create view revenue (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= '1996-01-01'
and l_shipdate < dateadd(month, 3, '1996-01-01')
group by
l_suppkey;
go

select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue
)
order by
s_suppkey;
drop view revenue;

/* Changes made
Removed [STREAM_ID]

l_shipdate >= date '1996-01-01
to
l_shipdate >= '1996-01-01

added a go after l_suppkey;
*/

Thanks :D

Removed [STREAM_ID]

That was the part I found so interesting. :D

And what i find interesting, is why you create a view and drop it after you have ran it once