locked
ROW_NUMBER() Function not work and give me error why RRS feed

  • Question

  • Hi guys I have sql server 2005 

    when i make new view object then write this query give me this message error 

    The OVER SQL Construct or Statement is not supported

    How to solve this problem 

    The Main query that work successfully before add ROW_Number() function is

    SELECT dbo.[Jeddah-Live$Sales Line].[Document No_] AS 'رقم الطلب', dbo.[Jeddah-Live$Sales Header].[Bill-to Name] AS 'العميل', 
    dbo.[Jeddah-Live$Sales Line].Area AS 'نوع الصبه', dbo.[Jeddah-Live$Sales Line].Description AS 'البيان', 
    dbo.[Jeddah-Live$Sales Header].[Pump No_] AS 'المضخه', CAST(ROUND(dbo.[Jeddah-Live$Sales Line].Quantity, 0, 1) AS int) AS 'المطلوب', 
    CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Quantity Shipped], 0, 1) AS int) AS 'المصبوب', 
    CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity], 0, 1) AS int) AS 'المتبقى '
    FROM dbo.[Jeddah-Live$Sales Header] INNER JOIN
    dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].No_ = dbo.[Jeddah-Live$Sales Line].[Document No_] AND 
    dbo.[Jeddah-Live$Sales Header].[Sell-to Customer No_] = dbo.[Jeddah-Live$Sales Line].[Sell-to Customer No_]

    Now how to add ROW_NUMBER() FUNCTION WITHOUT ERROR


    Monday, March 16, 2015 7:00 AM

Answers

  • Thank you for reply

    I write query above in sql query 

    and it show data but after you click the view created then select modify 

    it give me error above again

    The OVER SQL Construct or Statement is not supported

    but it show result

    Myquestion is

    if call view from interface c# code it will work 

    or it will give me error message The OVER SQL Construct or Statement is not supported

    Again its because you're using the view editor. View editor doesnt recognize most of new commands

    so the way to do this is as below

    1. Connect to database in SSMS

    2. Expand database -> Views

    3. Right click on your view and choose option Script As -> DROP And CREATE View to -> New query window

    4. The view will get loaded to a new query window. You can modify the code to add your logic using ROW_NUMBER etc in this. Click execute to modify view definition with the new code


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Eric__Zhang Tuesday, March 17, 2015 5:35 AM
    • Marked as answer by Eric__Zhang Monday, March 23, 2015 7:09 AM
    Monday, March 16, 2015 8:15 AM
  • if your code works/compiles well in SSMS Query Analyzer, then it will also work from any front end app.

    Do not depend on the GUI tools to create your SQL queries, write them on your own in SSMS Query Analyzer.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    • Proposed as answer by Eric__Zhang Tuesday, March 17, 2015 5:34 AM
    • Marked as answer by Eric__Zhang Monday, March 23, 2015 7:09 AM
    Monday, March 16, 2015 8:51 AM

All replies

  • It may be because of limitation of Query Designer.

    It is good to write a separate query in general Query Editor itself. I think you are trying to Create a view so you can execute below script to create it. Replace Schema and View name in first line.

    CREATE VIEW YourSchema.YourViewName
    AS
    SELECT dbo.[Jeddah-Live$Sales Line].[Document No_] AS 'رقم الطلب'
    	,dbo.[Jeddah-Live$Sales Header].[Bill-to Name] AS 'العميل'
    	,dbo.[Jeddah-Live$Sales Line].Area AS 'نوع الصبه'
    	,dbo.[Jeddah-Live$Sales Line].Description AS 'البيان'
    	,dbo.[Jeddah-Live$Sales Header].[Pump No_] AS 'المضخه'
    	,CAST(ROUND(dbo.[Jeddah-Live$Sales Line].Quantity, 0, 1) AS INT) AS 'المطلوب'
    	,CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Quantity Shipped], 0, 1) AS INT) AS 'المصبوب'
    	,CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity], 0, 1) AS INT) AS 'المتبقى '
    FROM dbo.[Jeddah-Live$Sales Header]
    INNER JOIN dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].No_ = dbo.[Jeddah-Live$Sales Line].[Document No_]
    	AND dbo.[Jeddah-Live$Sales Header].[Sell-to Customer No_] = dbo.[Jeddah-Live$Sales Line].[Sell-to Customer No_]


    Cheers,
    Vaibhav Chaudhari


    Monday, March 16, 2015 7:07 AM
  • This is well known issue with designer support in 2005. Please do it as procedure and verify.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]

    • Proposed as answer by disssss Monday, March 16, 2015 7:52 PM
    Monday, March 16, 2015 7:09 AM
  • Thank you for reply

    I don't have any problem in executing query above

    The problem only happen when add ROW_NUMBER() function  

    Monday, March 16, 2015 7:16 AM
  • Thank you for reply

    I don't have any problem in executing query above

    The problem only happen when add ROW_NUMBER() function  

    In the provided code, add your column which uses ROW_NUMBER() function and execute it.

    If you get any error, please post it.


    Cheers,
    Vaibhav Chaudhari

    Monday, March 16, 2015 7:18 AM
  • Thank you for reply

    I write query above in sql query 

    and it show data but after you click the view created then select modify 

    it give me error above again

    The OVER SQL Construct or Statement is not supported

    but it show result

    Myquestion is

    if call view from interface c# code it will work 

    or it will give me error message The OVER SQL Construct or Statement is not supported

    Monday, March 16, 2015 7:42 AM
  • Thank you for reply

    I write query above in sql query 

    and it show data but after you click the view created then select modify 

    it give me error above again

    The OVER SQL Construct or Statement is not supported

    but it show result

    Myquestion is

    if call view from interface c# code it will work 

    or it will give me error message The OVER SQL Construct or Statement is not supported

    If CREATE VIEW statement is executed without error and also if you see the result if you execute
    SELECT * FROM YourView, then you are good to go.

    Your C# code will also work if your SQL server connection is correct which points to correct DB.

    (You may get error if you right click the created view and open it in Designer as this is issue with Query Designer as others also said)


    Cheers,
    Vaibhav Chaudhari


    Monday, March 16, 2015 7:56 AM
  • Thank you for reply

    I write query above in sql query 

    and it show data but after you click the view created then select modify 

    it give me error above again

    The OVER SQL Construct or Statement is not supported

    but it show result

    Myquestion is

    if call view from interface c# code it will work 

    or it will give me error message The OVER SQL Construct or Statement is not supported

    Again its because you're using the view editor. View editor doesnt recognize most of new commands

    so the way to do this is as below

    1. Connect to database in SSMS

    2. Expand database -> Views

    3. Right click on your view and choose option Script As -> DROP And CREATE View to -> New query window

    4. The view will get loaded to a new query window. You can modify the code to add your logic using ROW_NUMBER etc in this. Click execute to modify view definition with the new code


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Eric__Zhang Tuesday, March 17, 2015 5:35 AM
    • Marked as answer by Eric__Zhang Monday, March 23, 2015 7:09 AM
    Monday, March 16, 2015 8:15 AM
  • The answer is simple: the Query/View Designer is a piece of crap. Stay away from it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 16, 2015 8:41 AM
  • if your code works/compiles well in SSMS Query Analyzer, then it will also work from any front end app.

    Do not depend on the GUI tools to create your SQL queries, write them on your own in SSMS Query Analyzer.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    • Proposed as answer by Eric__Zhang Tuesday, March 17, 2015 5:34 AM
    • Marked as answer by Eric__Zhang Monday, March 23, 2015 7:09 AM
    Monday, March 16, 2015 8:51 AM