question

Saga-3468 avatar image
0 Votes"
Saga-3468 asked GuoxiongYuan-7218 commented

Order individual query results in UNION ALL query

Hi all,

I am using SQL Server 2000 (v8) and need to generate a query that will return results from different queries where each result set is sorted. Below is an illustrative example of what I am looking for.

Scripts to create table and data are at the end.

A table contains sales information. I need the Product Id and the amount of sale for that item. That is, I need the query to bring back two columns. The table contains sales transactions in three stages of completion:

0: Sale just made, waiting for approval.
1: Sale has been approved
2: Sale is complete

I am interested only in stages 0 and 1.

The report must contain sales information for a given sales rep and be divided into two sections. The first for new sales (status = 0) and a second for approved sales (Status = 1). A total amount must follow each section. The detail must be ordered by product id.

Here is the catch: The reporting mechanism accepts a data set with two result sets. The first result set contains the header information and the second one contains the report's body. Up to now, this has worked fine because all the reports I've done have only one section, so I have been able to create one query for these.

So far, I have the following query:

 select ProdId,Amount from Sales
 where
 PerId = 12 and
 Status = 0
    
 union all
    
 select 0,SUM(Amount) from Sales where
 PerId = 12 and
 Status = 0
    
 union all
    
 select ProdId,Amount from Sales
 where
 PerId = 12 and
 Status = 1
    
 union all
    
 select 0, SUM(Amount) from Sales where
 PerId = 12 and
 Status = 1


For testing, I have hard coded the sales rep id (12), but the final script needs to accept this as a parameter. Since I plan to put this script inside a stored procedure, I don't see a problem with it.

The above example works, but does not sort by product id. I added the sort with an ORDER BY statement at the end of the query, but the sort affects the results as a whole, and not by section as I need it. Adding ORDER BY at the end of each query is not allowed.

I also tried creating views for the two detail queries, adding an ORDER BY to the view, but that doesn't work either for reasons that I discovered after a brief investigation. I also tried sorting by Status and ProdId, but the UNION does not like it if this column is not included in the SELECT statement, something that I don't want.

I also found another possible solution by generating a temp table on the fly instead of explicitly specifying a table in the FROM clause, but alas, the SQL Server that is in use does not support that.

Given what is mentioned here, what options do I have? Any orientation and/or feedback is welcomed and appreciated. Thank you, Saga


Scripts follow

 CREATE TABLE Sales (
     Id int IDENTITY(1,1),
     PerId int,
     ProdId int,
     SaleDate smalldatetime,
     Amount money,
     Status smallint
     );
    
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (7,5,'2019-03-19',15700.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (11,9,'2019-03-13',15700.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,11,'2019-03-15',15700.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,6,'2018-06-17',18500.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (7,3,'2018-06-04',18500.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (8,9,'2019-08-08',18566.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,8,'2019-08-05',18566.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (9,4,'2020-01-20',18925.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (5,6,'2020-01-22',18925.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (5,6,'2019-05-09',22300.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (9,8,'2019-10-07',22300.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (8,5,'2019-05-08',22300.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,10,'2019-05-05',22300.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (11,12,'2019-10-03',22300.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,12,'2019-05-04',22300.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,13,'2019-10-04',22300.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,9,'2018-08-01',24000.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (10,6,'2018-08-02',24000.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (8,4,'2018-08-04',24000.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (5,3,'2018-03-09',25499.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (11,5,'2018-03-03',25499.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,4,'2018-03-05',25499.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,2,'2018-03-04',25499.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,4,'2019-05-10',27900.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (10,7,'2019-05-11',27900.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (9,5,'2019-05-12',27900.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (8,4,'2020-02-25',28915.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,8,'2020-02-22',28915.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,9,'2019-03-03',32299.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (9,2,'2019-03-06',32299.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (10,4,'2019-03-05',32299.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (5,7,'2018-11-21',36000.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (7,8,'2020-02-13',55622.00,0)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (12,8,'2020-02-08',55622.00,1)
 insert into Sales (PerId,ProdId,SaleDate,Amount,Status) values (13,2,'2020-02-09',55622.00,0)   


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 commented

Try this:

 DECLARE @PerId int = 12;
    
 SELECT ProdId, Amount
 FROM (
     SELECT ProdId, Amount, 0 AS [Status], 0 AS SortNumber 
     FROM [dbo].[Sales] 
     WHERE [Status] = 0 AND PerId = @PerId
     UNION ALL
     SELECT 0 AS ProdId, SUM(Amount), 0 AS [Status], 1 AS SortNumber 
     FROM [dbo].[Sales] 
     WHERE [Status] = 0 AND PerId = @PerId
     UNION ALL
     SELECT ProdId, Amount, 1 AS [Status], 0 AS SortNumber 
     FROM [dbo].[Sales] 
     WHERE [Status] = 1 AND PerId = @PerId
     UNION ALL
     SELECT 0 AS ProdId, SUM(Amount), 1 AS [Status], 1 AS SortNumber  
     FROM [dbo].[Sales] 
     WHERE [Status] = 1 AND PerId = @PerId
 ) AS temp
 ORDER BY [Status], SortNumber, ProdId;
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you.

Good news: It worked perfectly on my test SQL Server.

Bad news: On the production server:

Msg 139, Level 15, State 1, Line 1
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 7
Must declare the variable '@PerId'.
Msg 137, Level 15, State 2, Line 11
Must declare the variable '@PerId'.
Msg 137, Level 15, State 2, Line 15
Must declare the variable '@PerId'.
Msg 137, Level 15, State 2, Line 19
Must declare the variable '@PerId'.


0 Votes 0 ·

Update: I changed the declaration as shown below and now it works great! Again, thanks!

 DECLARE @PerId int;
        
  set @PerId = 12


0 Votes 0 ·

Good to hear it helped. Sorry, I forgot that the SQL 2000 does not support the direct initialization in the declaration of the variable.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered Saga-3468 commented

Hi @Saga-3468

In statements containing UNION ALL, ORDER BY is only allowed to appear after the last statement, and not in the middle SELECT statement. In addition, SQL Server does not allow the definition of ORDER BY clauses in the view. In these cases, we can use cte.
Please also check:

     DECLARE @PerId int = 12;
        
     ;WITH cte
     AS(SELECT ProdId,Amount,[Status] FROM Sales
     WHERE PerId = @PerId and [Status] = 0
     UNION ALL
     SELECT 0,SUM(Amount),0 as [Status] FROM Sales 
     WHERE PerId = @PerId and [Status] = 0
     UNION ALL
     SELECT ProdId,Amount,[Status] FROM Sales
     WHERE PerId =@PerId and [Status] = 1
     UNION ALL
     SELECT 0, SUM(Amount),1 as [Status] FROM Sales 
     WHERE PerId =@PerId and [Status] = 1)
        
     SELECT ProdId, Amount FROM cte
     ORDER BY [Status],
     CASE WHEN ProdId=0 THEN (SELECT MAX(ProdId)+1 FROM Sales )END,ProdId

Output:
134541-image.png


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.




image.png (5.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you!

Good news: Worked great on my test SQL Server.

Bad news: On the production server, I changed the declaration as shown below and got the following error:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WITH'.

Edit to original code:

 DECLARE @PerId int;
        
  set @PerId = 12

Other than the above edit, I ran the code as was posted in the message above.

0 Votes 0 ·
OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered Saga-3468 commented

I am using SQL Server 2000 (v8)

I hope you know that version 2000 is out-of-support since more then a very long time?

from different queries where each result set is sorted

ORDER BY is a post opperation and apply the the complete resultset.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, thanks for following up.

Yup, I know (sigh) Organization is hosting data from a legacy system on the SQL Server 2000 that I referenced. I don't see it getting upgraded at all,

"ORDER BY is a post opperation and apply the the complete resultset."

Also yup, I had to try it just to be complete in my testing, and in documenting different attempts on this forum.

0 Votes 0 ·