locked
Problem with TSQL query RRS feed

  • 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

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

    Tuesday, September 17, 2013 8:23 AM
    Answerer
  • 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.

    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

    Dineshkumar's BI Blog


    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 END

    Please 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