The ORDER BY clause is invalid in views

  • Hi,

    I'm trying to run the below SQL code but I'm getting this error:

    An error occurred while checking the query syntax. Errors: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

    I'm still learning SQL so any help would be much appreciated. Thank you 🙂

    select
    ar.Account_B__c as Account_ID,
    a.SalesOrg__c as SalesOrg,
    a.AccountNumber AS Account_Number,
    MAX(CASE WHEN (ar.Relationship__c = 'RG') THEN '1' ELSE '0' END) AS Payer,
    MAX(CASE WHEN (ar.Relationship__c = 'RE') THEN '1' ELSE '0' END) AS Billto,
    MAX(CASE WHEN (ar.Relationship__c = 'WE') THEN '1' ELSE '0' END) AS Shipto,
    MAX(CASE WHEN (ar.Relationship__c = 'AG') THEN '1' ELSE '0' END) AS Soldto
    from ENT.Account_Relationship__c_Salesforce ar
    join ENT.Account_Salesforce a on a.Id = ar.Account_B__c
    WHERE
    a.Deletion_Flag_All_Areas__c = '0'
    AND a.AccountNumber != ''
    GROUP BY a.AccountNumber
    ORDER BY a.AccountNumber
  • Change your SELECT to

    SELECT TOP 100 PERCENT

    with the rest of the query remaining as it is.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I also suggest you start using <> rather than != for 'not equal to'. <> is a more widely accepted SQL standard than !=, as far as I know.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    I also suggest you start using <> rather than != for 'not equal to'. <> is a more widely accepted SQL standard than !=, as far as I know.

    Optimizer will change it to <>. So, save the optimizer a little work.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • You're also going to need to do something about the:

    ar.Account_B__c as Account_ID,

    a.SalesOrg__c as SalesOrg,

    columns.  They are not in the GROUP BY and they are not within an aggregate function, so they will cause a problem.

    If those values don't change for AccountNumber, you can add MAX() around them as well.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • Hi All,

    Thanks for your responses. I've amended my query and now got it working fine 🙂

    select
    ar.Account_A__c as Account_ID,
    a.SalesOrg__c as SalesOrg,
    a.AccountNumber AS Account_Number,
    MAX(CASE WHEN (ar.Relationship__c = 'RG') THEN '1' ELSE '0' END) AS Payer,
    MAX(CASE WHEN (ar.Relationship__c = 'RE') THEN '1' ELSE '0' END) AS Billto,
    MAX(CASE WHEN (ar.Relationship__c = 'WE') THEN '1' ELSE '0' END) AS Shipto,
    MAX(CASE WHEN (ar.Relationship__c = 'AG') THEN '1' ELSE '0' END) AS Soldto
    from ENT.Account_Relationship__c_Salesforce ar
    join ENT.Account_Salesforce a on a.Id = ar.Account_A__c
    WHERE
    a.Deletion_Flag_All_Areas__c = '0'
    AND a.AccountNumber != ''
    GROUP BY a.AccountNumber, a.SalesOrg__c, ar.Account_A__c

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply