Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Provided Answers: 6

    Unanswered: Trouble with a case statement

      ,CASE WHEN CAST(qa.[INDIRECT_PRICE] as varchar) IS NULL THEN ISNULL(CAST([INDIRECT_PRC] as varchar(10)),'') ELSE ROUND(([INDIRECT_PRC] - [NET_INDIRECT_PRC]) * qa.[QTY],2) END [Post Purchase Discount Dollars Due]
       ,CASE WHEN CAST(qa.[DIRECT_PRICE] as varchar) <> '-' THEN ROUND(([INDIRECT_PRC] - [NET_INDIRECT_PRC]) * qa.[QTY],2) ELSE '-' END [Post Purchase]

    I'm basically trying to say when indirect price = or <> '-' then either then my formula else '-' end whatever or vice versa, but for some reason I am unable to get the NULL to '-' in this particular case statement.

    I've tried coalesce(cast and a few other commands but I am unable to get those pesky nulls to say - I was able to do it for fields without formulas but with the round formula it doesn't play nice.

    Thanks for any assistance.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    There are multiple problems, which is why things are so confusing.

    First and foremost, data formatting needs to be handled near the user. Formatting should not be done in the data itself, by SQL Server, or even by an Application Server if it can be avoided. Formatting ought to be done by the client application or web browser.

    NULL isn't a value, it is a "marker for unknown". NULL doesn't really have a type because NULL denotes the absence of a value.

    Next order of business, all of the values returned by a CASE statement have to have a common data type. A CASE statement can't return a VARCHAR() on one row and a NUMERIC on the next (like your example is trying to do). The way that SQL handles result sets can't cope with variations like that without using something like XML.

    My best guess is that the application that displays the result set for the user ought to handle the formatting. Excel does this very well, as does SQL Server Reporting Services.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Provided Answers: 6
    Thanks Pat for clearing this up I appreciate your expertise.

    Kind regards

Posting Permissions

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