ORDER by items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Question

1.00/5 (1 vote)

See more:

Hello, I have error when I try order by, I am using union

* ( i try to use order by date mm and YYYY to sort date from oldest to earlest)

What I have tried:

SELECT
[Spare6]
,[Spare5]
,[Spare4]
,[Spare3]
,[Spare2]
,[Spare1]
,[details]
,[note]
,[documents]
,[termsofpaymneten]
,[termsofpaymnet]
,[aprtmentstatusen]
,[aprtmentstatus]
,convert(varchar,amountdateen,103) as amountdateen
,convert(varchar,amountdate,103) as amountdate
,[amountleft]
,[amountpaid]
,[rent]
,[noofpayemtsen]
,[noofpayemts]
,[sourceen]
,[source]
,[iddateen]
,[iddate]
,[idnumber]
,convert(varchar, contractenen, 103) as contractenen
,convert(varchar, contracten, 103) as contracten
,convert (varchar,contractstarten ,103)as contractstarten
,convert (varchar,contractstart ,103)as contractstart
,[contractnoen]
,[contractno]
,[nationalityen]
,[nationality]
,[secondpartyen]
,[secondparty]
,[firstpartyen]
,[firstparty]
,[apartemetnnoen]
,[apartemetnno]
,[Bulidingnoen]
,[Bulidingno]
,[No] FROM [All_Vech].[dbo].[Bulding_Safa]
where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103)

UNION

SELECT
[Spare6]
,[Spare5]
,[Spare4]
,[Spare3]
,[Spare2]
,[Spare1]
,[details]
,[note]
,[documents]
,[termsofpaymneten]
,[termsofpaymnet]
,[aprtmentstatusen]
,[aprtmentstatus]
,convert(varchar,amountdateen,103) as amountdateen
,convert(varchar,amountdate,103) as amountdate
,[amountleft]
,[amountpaid]
,[rent]
,[noofpayemtsen]
,[noofpayemts]
,[sourceen]
,[source]
,[iddateen]
,[iddate]
,[idnumber]
,convert(varchar, contractenen, 103) as contractenen
,convert(varchar, contracten, 103) as contracten
,convert (varchar,contractstarten ,103)as contractstarten
,convert (varchar,contractstart ,103)as contractstart
,[contractnoen]
,[contractno]
,[nationalityen]
,[nationality]
,[secondpartyen]
,[secondparty]
,[firstpartyen]
,[firstparty]
,[apartemetnnoen]
,[apartemetnno]
,[Bulidingnoen]
,[Bulidingno]
,[No] FROM [All_Vech].[dbo].[Bulding_Sharfya]
where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103)

UNION

SELECT
[Spare6]
,[Spare5]
,[Spare4]
,[Spare3]
,[Spare2]
,[Spare1]
,[details]
,[note]
,[documents]
,[termsofpaymneten]
,[termsofpaymnet]
,[aprtmentstatusen]
,[aprtmentstatus]
,convert(varchar,amountdateen,103) as amountdateen
,convert(varchar,amountdate,103) as amountdate
,[amountleft]
,[amountpaid]
,[rent]
,[noofpayemtsen]
,[noofpayemts]
,[sourceen]
,[source]
,[iddateen]
,[iddate]
,[idnumber]
,convert(varchar, contractenen, 103) as contractenen
,convert(varchar, contracten, 103) as contracten
,convert (varchar,contractstarten ,103)as contractstarten
,convert (varchar,contractstart ,103)as contractstart
,[contractnoen]
,[contractno]
,[nationalityen]
,[nationality]
,[secondpartyen]
,[secondparty]
,[firstpartyen]
,[firstparty]
,[apartemetnnoen]
,[apartemetnno]
,[Bulidingnoen]
,[Bulidingno]
,[No] FROM [All_Vech].[dbo].[Commercial_Building]
where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103)

UNION

SELECT
[Spare6]
,[Spare5]
,[Spare4]
,[Spare3]
,[Spare2]
,[Spare1]
,[details]
,[note]
,[documents]
,[termsofpaymneten]
,[termsofpaymnet]
,[aprtmentstatusen]
,[aprtmentstatus]
,convert(varchar,amountdateen,103) as amountdateen
,convert(varchar,amountdate,103) as amountdate
,[amountleft]
,[amountpaid]
,[rent]
,[noofpayemtsen]
,[noofpayemts]
,[sourceen]
,[source]
,[iddateen]
,[iddate]
,[idnumber]
,convert(varchar, contractenen, 103) as contractenen
,convert(varchar, contracten, 103) as contracten
,convert (varchar,contractstarten ,103)as contractstarten
,convert (varchar,contractstart ,103)as contractstart
,[contractnoen]
,[contractno]
,[nationalityen]
,[nationality]
,[secondpartyen]
,[secondparty]
,[firstpartyen]
,[firstparty]
,[apartemetnnoen]
,[apartemetnno]
,[Bulidingnoen]
,[Bulidingno]
,[No] FROM [All_Vech].[dbo].[Mall_Reem]
where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103)

UNION
SELECT
[Spare6]
,[Spare5]
,[Spare4]
,[Spare3]
,[Spare2]
,[Spare1]
,[details]
,[note]
,[documents]
,[termsofpaymneten]
,[termsofpaymnet]
,[aprtmentstatusen]
,[aprtmentstatus]
,convert(varchar,amountdateen,103) as amountdateen
,convert(varchar,amountdate,103) as amountdate
,[amountleft]
,[amountpaid]
,[rent]
,[noofpayemtsen]
,[noofpayemts]
,[sourceen]
,[source]
,[iddateen]
,[iddate]
,[idnumber]
,convert(varchar, contractenen, 103) as contractenen
,convert(varchar, contracten, 103) as contracten
,convert (varchar,contractstarten ,103)as contractstarten
,convert (varchar,contractstart ,103)as contractstart
,[contractnoen]
,[contractno]
,[nationalityen]
,[nationality]
,[secondpartyen]
,[secondparty]
,[firstpartyen]
,[firstparty]
,[apartemetnnoen]
,[apartemetnno]
,[Bulidingnoen]
,[Bulidingno]
,[No] FROM [All_Vech].[dbo].[MALL_Residential ]
where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103)
UNION

SELECT
[Spare6]
,[Spare5]
,[Spare4]
,[Spare3]
,[Spare2]
,[Spare1]
,[details]
,[note]
,[documents]
,[termsofpaymneten]
,[termsofpaymnet]
,[aprtmentstatusen]
,[aprtmentstatus]
,convert(varchar,amountdateen,103) as amountdateen
,convert(varchar,amountdate,103) as amountdate
,[amountleft]
,[amountpaid]
,[rent]
,[noofpayemtsen]
,[noofpayemts]
,[sourceen]
,[source]
,[iddateen]
,[iddate]
,[idnumber]
,convert(varchar, contractenen, 103) as contractenen
,convert(varchar, contracten, 103) as contracten
,convert (varchar,contractstarten ,103)as contractstarten
,convert (varchar,contractstart ,103)as contractstart
,[contractnoen]
,[contractno]
,[nationalityen]
,[nationality]
,[secondpartyen]
,[secondparty]
,[firstpartyen]
,[firstparty]
,[apartemetnnoen]
,[apartemetnno]
,[Bulidingnoen]
,[Bulidingno]
,[No] FROM [All_Vech].[dbo].[Shop_Reem]
where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103)
ORDER BY Month(amountdateen), YEAR(amountdateen) -------------------------------------> error is here
END

Posted 14-Aug-17 1:43am

Learn.net37

Updated 14-Aug-17 2:43am

Add a Solution

Comments

CHill60 14-Aug-17 8:31am

The error seems quite clear ... you don't have Month(amountdateen) nor YEAR(amountdateen) in your select list.
On another point you should not be converting the dates.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-08-14T02:43:00

You appear to have at least 6 tables with exactly the same schema. This is usually a sure sign that your database design needs some attention.

From what I can see all of this information could be (and should be) stored in a single table. Add another column that indications the "BuildingType" - it will contain values such as "Safa", "Sharfya", "Commercial_Building" - or even better be a foreign key to a table containing details of the building types.

Secondly you should not use

where CONVERT(date,amountdateen,103) < CONVERT(date ,GETDATE(),103)

Use DATEDIFF[^]. You should also not be converting dates before passing information back to the calling program - allow the GUI layer to interpret a DATE type instead of a string that can be ambiguous.

To get over your problem ~~you could just include Month(amountdateen) and YEAR(amountdateen) in your SELECT lists but it would be far better to address the issues with your database design.~~ ORDER by amountdateen DESC