Results 1 to 7 of 7

Thread: order parameter

  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: order parameter

    why does this order parameter not work?!

    Declare @SortOrder varchar(50)

    ORDER BY CASE
    WHEN @SortOrder = 'Sales' THEN Sum(SALES.Sale)
    WHEN @SortOrder = 'Profit' THEN Sum(GP)
    WHEN @SortOrder = 'Product' THEN SALES.Product
    END


    Error:

    Warning: Null value is eliminated by an aggregate or other SET operation.
    Server: Msg 8114, Level 16, State 5, Line 8
    Error converting data type varchar to float.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ah, this is a good one.

    The CASE function returns a value. That value must have a datatype. The CASE statement uses the datatype of the first clause as its return type.
    In a CASE statement such as yours, different datatypes are returned under different conditions, and they are not implicitly convertable. You will need to use the CONVERT function in each clause to cast them all as the same datatype. I would recommend casting them as varchar, but you will need to pad your numeric values with leading zero's in order to get them to sort correctly as strings.
    You can ignore the "Null value is eliminated by an aggregate or other SET operation." warning.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Posts
    76
    thanks

    Before I sen ur reply I introducted 2 sortOrder parameters, (sortOrder, sortOrder1 both varchar) and changed the Order by to look like:
    -- The 2 sortOrder are set as null, so only 1 will have a value not Null...


    ORDER BY CASE
    WHEN @SortOrder = 'Sales' THEN Sum(SALES.Sale)
    WHEN @SortOrder = 'Profit' THEN Sum(GP) END
    CASE WHEN @SortOrder1 = 'Product' Then SALES.Product END

    Why doesn't this work?!

    Error Message:

    Server: Msg 156, Level 15, State 1, Line 24
    Incorrect syntax near the keyword 'CASE'


    ... About the way u said to overcome the problem, how do I "pad your numeric values with leading zero's "

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    1. You need a comma before the 2nd case statement
    2. Can be padded with spaces e.g. str(val,10,0)

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by STUCK1234
    Error:

    Warning: Null value is eliminated by an aggregate or other SET operation.
    Server: Msg 8114, Level 16, State 5, Line 8
    Error converting data type varchar to float.
    The warning is actually because of the NULLs in the fields, while the error is because of the datatype conversion, as bm mentioned.

    For queries like this, when there is a finite number of scenarios, I would recommend to have a separate routine per ORDER clause (whether it's a stored procedure or a function that you're working on). A dynamic change in ORDER clause between executions may result in 2 possible outcomes:

    1. The need to change the execution plan, and thus recompile
    2. The need to have a different execution plan, but inability to recompile due to high compilation cost.

    In both cases you will end up with degraded performance and other side effects.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey!

    Where the hell have you been?

    On sabatical?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Brett Kaiser
    Hey!

    Where the hell have you been?

    On sabatical?
    Busy
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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