The ORDER BY clause is invalid in views

Question

Post reply

The ORDER BY clause is invalid in views

micky007

SSC Enthusiast

Points: 173

October 5, 2022 at 10:45 am

#4095747

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Change your SELECT to

SELECT TOP 100 PERCENT

with the rest of the query remaining as it is.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101096 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

micky007 SSC Enthusiast Points: 173 More actions · Answer 5

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