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 OrderNumber

But 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 OrderNumber

Please 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

Answer 1

Sorry cannot test it right now

ORDER BY CASE WHEN CAST(OrderNumber AS VARCHAR(50)) ='ALL' THEN 0 END,OrderNumber 

---

Best 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

Answer 2

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,OrderNumber

error :

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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 END

Please let me know if you can suggest any other modified form of the query using cast function.

Thanks

Ajit

Answer 7

Hi Saravana,

Many Thanks for your help.

The query ran successfully :)

Regards

Ajit