Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jul 2006
    Posts
    124
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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:
    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
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    That is interesting syntax. What DBMS is this for, and are you sure you didn't want a stored procedure, not a view?

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Regular Coder
    Join Date
    Jul 2006
    Posts
    124
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  • #4
    Regular Coder
    Join Date
    Jul 2006
    Posts
    124
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I got it... I was doing it right, I just needed to add a go... that was dumb hehe

    Code:
    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

  • #5
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Removed [STREAM_ID]

    That was the part I found so interesting.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    And what i find interesting, is why you create a view and drop it after you have ran it once
    I am the luckiest man in the world


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •