I need help with this SQL Query

Discussion in 'OT Technology' started by morgue, May 3, 2007.

  1. morgue

    morgue New Member

    Joined:
    Jul 10, 2004
    Messages:
    8,278
    Likes Received:
    0
    I've been trying a lot of different ways but I have no idea how to get it to run...

    Query:
    Code:
    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?
     
  2. morgue

    morgue New Member

    Joined:
    Jul 10, 2004
    Messages:
    8,278
    Likes Received:
    0
    Got it
    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;
    */
     
  3. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    fawk that is impossible to read like that. :ugh:
     

Share This Page