Answered by:
Problem with TSQL query

Question
-
I have a TSQL query as follows :
SELECT NULL AS OrderNumber
UNION
SELECT OrderNumber
FROM vNavigation_OrderHeader
WHERE (OrderNumber > 0) AND (CompletedPhase IS NULL) AND (IsCloseoutPending = 0 OR
IsCloseoutPending IS NULL)
ORDER BY OrderNumberBut instead of NULL I want to have 'ALL' in the result set. So I modified the query in the way as shown below:
SELECT 'ALL' AS OrderNumber
UNION
SELECT convert(varchar,OrderNumber)
FROM vNavigation_OrderHeader
WHERE (OrderNumber > 0) AND (CompletedPhase IS NULL) AND (IsCloseoutPending = 0 OR
IsCloseoutPending IS NULL)
ORDER BY OrderNumberPlease note the Datatype of OrderNumber is integer in the view vNavigation_OrderHeader.
The problem I am facing is that the result set I am getting has 'ALL' appended to it but the result set is not sorted. I want 'ALL' to be the first entry followed by ordernumbers in ascending order.
Thanks
Ajit
Tuesday, September 17, 2013 8:17 AM
Answers
-
Hi Ajit,
Hope this will solve your problem
SELECT OrderNumber from ( SELECT 'ALL' AS OrderNumber UNION ALL SELECT convert(varchar,OrderNumber) as OrderNumber FROM vNavigation_OrderHeader WHERE (OrderNumber > 0) AND (CompletedPhase IS NULL) AND (IsCloseoutPending = 0 OR IsCloseoutPending IS NULL) ) a ORDER BY CASE WHEN OrderNumber='ALL' THEN 0 ELSE OrderNumber END
Please mark my reply as an Answer if it Solved your problem. Vote it if it helped you.
Thanks
Saravana Kumar C
- Proposed as answer by Kalman Toth Tuesday, September 17, 2013 9:14 AM
- Marked as answer by Ajit Kumar KSingh Tuesday, September 17, 2013 11:02 AM
Tuesday, September 17, 2013 9:08 AM
All replies
-
Sorry cannot test it right now
ORDER BY CASE WHEN CAST(OrderNumber AS VARCHAR(50)) ='ALL' THEN 0 END,OrderNumberBest Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
Tuesday, September 17, 2013 8:23 AMAnswerer -
Hi Uri,
I tried your way but it thors error :
SELECT 'ALL' AS OrderNumber
UNION
SELECT convert(varchar,OrderNumber)
FROM vNavigation_OrderHeader
WHERE (OrderNumber > 0) AND (CompletedPhase IS NULL) AND (IsCloseoutPending = 0 OR
IsCloseoutPending IS NULL)
ORDER BY CASE WHEN CAST(OrderNumber AS VARCHAR(50))='ALL' THEN 0 END,OrderNumbererror :
Msg 207, Level 16, State 1, Line 8
Invalid column name 'OrderNumber'.
Msg 104, Level 16, State 1, Line 8
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
Tuesday, September 17, 2013 8:37 AM -
Hi Ajit,
Hope this will solve your problem
SELECT OrderNumber from ( SELECT 'ALL' AS OrderNumber UNION ALL SELECT convert(varchar,OrderNumber) as OrderNumber FROM vNavigation_OrderHeader WHERE (OrderNumber > 0) AND (CompletedPhase IS NULL) AND (IsCloseoutPending = 0 OR IsCloseoutPending IS NULL) ) a ORDER BY CASE WHEN OrderNumber='ALL' THEN 0 ELSE OrderNumber END
Please mark my reply as an Answer if it Solved your problem. Vote it if it helped you.
Thanks
Saravana Kumar C
- Proposed as answer by Kalman Toth Tuesday, September 17, 2013 9:14 AM
- Marked as answer by Ajit Kumar KSingh Tuesday, September 17, 2013 11:02 AM
Tuesday, September 17, 2013 9:08 AM -
Hi Ajit,
First of all, When you do a "Union" SQL automatically orders data by either the first column or any primary key or any other cluster index is there in the resultset<selected from table_name>.
In your case, the order of sorting is that, all the numerals come first then the AlphaNumeric comes.
If you want to have the <OrderNumber> in sorted order being 'All' as first instead of Convert(varchar(20),OrderNumber) use Cast function.
Please mark my reply as an Answer if it Solved your problem. Vote it if it helped you.
Tuesday, September 17, 2013 9:16 AM -
DECLARE @table table (OrderNumber int) insert INTO @table SELECT 1 union SELECT 2 union SELECT 3 union SELECT 4 union SELECT 5 union SELECT 6 select ISNULL(x.ordernumber,'ALL') from ( select NULL AS ordernumber union select cast(OrderNumber AS varchar(50)) from @table )x ORDER BY x.ordernumber
---
select ISNULL(x.ordernumber,'ALL') from (
SELECT NULL AS OrderNumber
UNION
SELECT cast(OrderNumber as varchar(max)) FROM vNavigation_OrderHeader
WHERE (OrderNumber > 0) AND (CompletedPhase IS NULL) AND (IsCloseoutPending = 0 OR
IsCloseoutPending IS NULL)
)x
Regards, Dineshkumar,
Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you
- Edited by Dinesh Kumar Rajendran Tuesday, September 17, 2013 9:21 AM
Tuesday, September 17, 2013 9:18 AM -
Hi Himanshu,
I used the below query using cast :
select OrderNumber from
(
SELECT 'ALL' AS OrderNumber
UNION
SELECT cast(OrderNumber as varchar)
FROM vNavigation_OrderHeader
WHERE (OrderNumber > 0) AND (CompletedPhase IS NULL) AND (IsCloseoutPending = 0 OR
IsCloseoutPending IS NULL)
)a
ORDER BY CASE WHEN OrderNumber='ALL' THEN 0 ELSE OrderNumber ENDPlease let me know if you can suggest any other modified form of the query using cast function.
Thanks
Ajit
Tuesday, September 17, 2013 11:00 AM -
Hi Saravana,
Many Thanks for your help.
The query ran successfully :)
Regards
Ajit
Tuesday, September 17, 2013 11:02 AM