Union clause and order by error message

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Union clause and order by error message

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-20 : 07:41:51

Hello

I have created the two queries below, and want to union the results into one results set. however, I keep getting the error message:


Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.

But have I not done that in the query below? Help please........

SELECT EBillsBounced = 'For SVD', [Count]= COUNT(DISTINCT dbo.BounceBack.EbillID) 
FROM dbo.Cycle 
INNER JOIN dbo.CycleInstance ON dbo.Cycle.CycleID = dbo.CycleInstance.CycleID 
INNER JOIN dbo.EBill ON dbo.CycleInstance.CycleInstanceID = dbo.EBill.CycleInstanceID 
INNER JOIN dbo.BounceBack ON dbo.EBill.EBillID = dbo.BounceBack.EbillID 
CROSS JOIN dbo.ExtractBounceBackInstance
WHERE (dbo.BounceBack.BounceDate >
                          	(
				SELECT TOP 1 StartDateTime
                           	FROM ExtractBounceBackInstance
                            	WHERE ScheduleID < 	(
							SELECT MAX(ScheduleID)
                                                        FROM ExtractBounceBackInstance
							)
                            	ORDER BY ScheduleID DESC
				) 
AND dbo.BounceBack.BounceDate <=
                          	(
				SELECT StartDateTime
                            	FROM ExtractBounceBackInstance
                            	WHERE ScheduleID =	(
							SELECT MAX(ScheduleID)
                                                         FROM ExtractBounceBackInstance)
							)
AND dbo.Cycle.GenerateSVD = 1)

UNION 

SELECT EBillsBounced = 'For Text Journal', [Count]= COUNT(DISTINCT dbo.BounceBack.EbillID) 
FROM dbo.Cycle 
INNER JOIN dbo.CycleInstance ON dbo.Cycle.CycleID = dbo.CycleInstance.CycleID 
INNER JOIN dbo.EBill ON dbo.CycleInstance.CycleInstanceID = dbo.EBill.CycleInstanceID 
INNER JOIN dbo.BounceBack ON dbo.EBill.EBillID = dbo.BounceBack.EbillID 
CROSS JOIN dbo.ExtractBounceBackInstance
WHERE (dbo.BounceBack.BounceDate >
                          	(
				SELECT TOP 1 StartDateTime
                           	FROM ExtractBounceBackInstance
                            	WHERE ScheduleID < 	(
							SELECT MAX(ScheduleID)
                                                        FROM ExtractBounceBackInstance
							)
                            	ORDER BY ScheduleID DESC
				) 
AND dbo.BounceBack.BounceDate <=
                          	(
				SELECT StartDateTime
                            	FROM ExtractBounceBackInstance
                            	WHERE ScheduleID =	(
							SELECT MAX(ScheduleID)
                                                         FROM ExtractBounceBackInstance)
							)
AND dbo.Cycle.GenerateTextJournal = 1)

ORDER BY EBillsBounced, [Count]

Hearty head pats

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-20 : 09:18:03

Yikes! I have no idea what you are trying to calculate there, but I can guarantee that there is an easier and more efficent way if you give us relevant details, table structures, sample data, etc.

As for your current situation, if you are having trouble with ORDER BY clauses when using UNION, just wrap the whole SELECT in a derived table and do the ordering outside of it. i.e.,


SELECT tmp.* 
FROM
  (your big sql statement shown above, here) tmp
ORDER BY tmp.BillsBounced, tmp.Count

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-01-20 : 10:00:57

Thanks for replying. If you can suggest a better and more efficient way of writing the query, I would greatly appreciate it. I shall try and explain what I am doing (and it is not particularly complex), so hopefully you can give me a better way of writing it:

Tables
There are other tables, but for simplicity, I shall include only those that are relevant to the problem.


CREATE TABLE [dbo].[Cycle] (
	[CycleID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[CycleCode] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ClientCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[GenerateSVD] [bit] NOT NULL ,
	[GenerateTextJournal] [bit] NOT NULL 
) ON [CPWEBilling_Reporting_data]
GO

CREATE TABLE [dbo].[CycleInstance] (
	[CycleInstanceID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[CycleID] [smallint] NOT NULL ,
	[CycleInstanceDate] [datetime] NOT NULL 
) ON [CPWEBilling_Reporting_data]
GO

CREATE TABLE [dbo].[EBill] (
	[EBillID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] [bigint] NOT NULL ,
	[InvoiceNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CycleInstanceID] [bigint] NOT NULL 
) ON [CPWEBilling_Reporting_data]
GO

CREATE TABLE [dbo].[BounceBack] (
	[BounceBackID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[EbillID] [bigint] NOT NULL ,
	[BounceDate] [datetime] NOT NULL ,
	[BounceReasonID] [int] NOT NULL 
) ON [CPWEBilling_Reporting_data]
GO

CREATE TABLE [dbo].[ExtractBounceBackInstance] (
	[ScheduleID] [bigint] NOT NULL ,
	[StartDateTime] [datetime] NOT NULL 
) ON [CPWEBilling_Reporting_data]

ExtractBounceBackInstance does not have any relationships with any tables, it is used to record when the program is run against the database

Requirement:
A Program is to be run against the database to extract all the EBills that have bounced and not reached the recipient within a certain timeframe

It pulls out the customer information relative to the EBill and inserts their details into either an SVD or TEXT file

The Cycle table determines what format the customer details should be output in:


CycleID CycleCode    ClientCode           GenerateSVD GenerateTextJournal 
------- ------------ -------------------- ----------- ------------------- 
0       CPW0113      CPW001BTC            0           1
25      CPW0105      CPW005VTM            0           1
33      CPF0107      CPW007FIX            1           0
41      CPW0101      CPW001BTC            0           1

Purpose of the query
Generate a report to count the number of Ebills that have bounced (count each one only the one time) and have been inserted into either the SVD or Text journal

This is where the WHERE clause gets a bit tricky because the dates that I am using are from when the application was last run upto to the current run. The scheduleID increments for each time the app is run.

Example
ExtractBounceBacksInstance
ScheduleID StartDateTime
1 1 Jan 2006
2 8 Jan 2006
3 15 Jan 2006

The program is executed, and it inserts a datetime stamp into the StartDateTime column. In this example, we are currently executing under ScheduleID 3.

StartDateTime = The dateTime of when the app was last run


(
				SELECT TOP 1 StartDateTime
                           	FROM ExtractBounceBackInstance
                            	WHERE ScheduleID < 	(
							SELECT MAX(ScheduleID)
                                                        FROM ExtractBounceBackInstance
							)
                            	ORDER BY ScheduleID DESC
				)

SO in this scenario, the query would return ScheduleID 2

EndDateTime = The DateTimeStamp inserted during the current execution of the program


(
				SELECT StartDateTime
                            	FROM ExtractBounceBackInstance
                            	WHERE ScheduleID =	(
							SELECT MAX(ScheduleID)
                                                         FROM ExtractBounceBackInstance)
							)

Oh, and I know why it was throwing a wobbly - it didn't like the Order By clause if the nested select statement.

Sooooo, any better ways of doing this? The output at the moment looks like this:


EBillsBounced Count 
------------ ---------------- 
SVD          4
Text Journal 2

Hearty head pats

shijobaby
Starting Member

44 Posts

Posted - 2009-08-19 : 05:42:35

Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009/08/order-by-items-must-appear-in-select_19.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources