Results 1 to 9 of 9

Thread: IIF - Newbie

  1. #1
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Unanswered: IIF - Newbie

    Hi,

    just for understanding a simple iif statement:
    I like to set a numeric value to 0, if it is smaller than 0.
    This expression shall be valid over all dimensions for a specific measure

    like Iif([Measures].[Provision Ankauf]<0,0,[Measures].[Provision Ankauf] )

    it's not working, Pivottable returns ##VALUE everywhere

  2. #2
    Join Date
    Feb 2002
    Posts
    58
    make sure [Measures].[Provision Ankauf] is returning a number, and is not null.

    also to make sure you're not crazy try: IIF(5<0,0,5) and see what happens

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    IIF is not supported in SQL Server

    use CASE instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As Rudy pointed out, you need to translate your VB (actually Jet) syntax to:
    Code:
    CASE
       WHEN [Measures].[Provision Ankauf] < 0 THEN 0
       ELSE [Measures].[Provision Ankauf]
    END
    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Just to be ornery:

    ([Measures].[Provision Ankauf]+abs([Measures].[Provision Ankauf]))/2
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Just to be ornery:
    Ornery ?!?! That's deviant, low down, and high smellin' !!! I LIKE it!

    In reality, the CASE statement is something that the original poster needs to know how to use since it opens all kinds of other possibilities that they'll need someday, but your bit of nifty math is both kinky and effective, so it is lots of fun the play with!

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, brilliant as usual!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, he needs to know CASE.

    For some reason, possibly (probably) unjustified, I always feel like logic statements are less efficient than formula solutions. Just my bias, though I have to agree that the CASE statement is more readable. Without a comment, somebody looking at my solution wouldn't be able to immediately see its purpose.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, now that i take a closer look, dajm probablu just needs IIF

    i think he/she is using access, based on Pivottable and ##VALUE, and simply posted in the wrong forum

    also knowing CASE isn't that bad an idea, though, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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