Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Unanswered: Order By Case Confusion

    I can't seem to figure out what is going on here... try this code. It should return 3 rows with no problem as is.

    ===========
    DECLARE
    @SortOn varchar(20)

    SET @SortOn = 'dicount'

    SELECT discounttype,
    isNull(stor_id,'') stor_id,
    isNull(lowqty,0) lowqty,
    isNull(highqty,0) highqty,
    discount
    FROM pubs..discounts
    ORDER BY CASE @SortOn
    WHEN 'discounttype' THEN discounttype
    WHEN 'stor_id' THEN stor_id
    WHEN 'lowqty' THEN lowqty
    WHEN 'highqty' THEN highqty
    WHEN 'dicount' THEN discount
    END
    ===========

    Now set @SortOn = 'discounttype' and you get:
    Server: Msg 8114, Level 16, State 5, Line 5
    Error converting data type varchar to numeric.

    ???

    Now for more confusion.. when you comment out lines 3,4,and 5 of the CASE statement (WHEN 'lowqty'...'highqty'...'dicount' lines) you once again get 3 rows returned without error.

    Anyone able to shed some light on what is goin on?

    [MESSAGE EDITED: misspelled discounttype.. sorry]
    Last edited by sphonemus; 07-23-02 at 18:19.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause. SQL is expecting a numeric - look at data type precedence.

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Try this:

    Code:
    DECLARE @SortOn varchar(20) 
    
    SET @SortOn = 'dicount' 
    
    SELECT discounttype, 
                isNull(stor_id,'') stor_id, 
                isNull(lowqty,0) lowqty, 
                isNull(highqty,0) highqty, 
               discount 
    FROM   pubs..discounts 
    ORDER BY 
             CASE  @SortOn
                  WHEN 'discounttype' THEN discounttype 
                  WHEN 'stor_id' THEN CAST(stor_id AS VARCHAR)
                  WHEN 'lowqty' THEN CAST(lowqty  AS VARCHAR)
                  WHEN 'highqty' THEN CAST(highqty  AS VARCHAR)
                  WHEN 'dicount' THEN CAST(discount AS VARCHAR)
             END
    MCDBA

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    The only problem with that code is that it will sort the numerics as varchars(ascii character comparision) and not as a true numeric. So values 10,9,12 would be sorted as 10,12,9 (ascending) and 9,12,10 (descending).

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause
    i've looked at that answer several times, and the error message too, and i still don't understand why it would throw an error

    but then, the query's kind of weird too

    why not stick the whatever-i-want-to-sort-on-this-week into an extra column in the result set and sort on that...
    Code:
    SELECT discounttype
         , isNull(stor_id,'') stor_id
         , isNull(lowqty,0) lowqty
         , isNull(highqty,0) highqty
         , discount
         , CASE @SortOn
             WHEN 'discounttype' THEN discounttype 
             WHEN 'stor_id' THEN stor_id
             WHEN 'lowqty' THEN lowqty 
             WHEN 'highqty' THEN highqty 
             WHEN 'dicount' THEN discount
           END as sortfield 
      FROM pubs..discounts 
    ORDER BY sortfield
    maybe that'll throw the same error, i dunno

    rudy
    http://rudy.ca/

  6. #6
    Join Date
    Jul 2002
    Posts
    2
    Originally posted by r937
    maybe that'll throw the same error, i dunno
    Nice thought but that errors as well.

    I have solved my problem by seperating each datatype with an IF clause like so:
    Code:
    IF @SortOn in (..) BEGIN -- varchars
         SELECT ....
         FROM ..
         ORDER BY CASE @SortOn
                              WHEN (varchars... etc..)
                         END
    END ELSE BEGIN -- numerics
         SELECT ....
         FROM ..
         ORDER BY CASE @SortOn
                              WHEN (numerics... etc..)
                         END
    END
    Kinda messy but it works. Any thoughts to make it a bit more graceful would be appreciated.

    Thanks for the replies!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •