locked
iif function give error on '=' RRS feed

  • Question

  • i m using query iif((sum(m_107)+sum(m_109))='0','1',sum(m_107)+sum(m_109))  it gives a run time error at '=' sign
    Thursday, December 30, 2010 9:44 AM

Answers

  • Hmmm, there is no IIF function in T-SQL, are  you using SSRS expression?

    SELECT CASE WHEN sum(m_107)+sum(m_109)='0' THEN 1 ELSE sum(m_107)+sum(m_109) END

    Sorry , cannot test the above


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 30, 2010 9:52 AM
    Answerer
  • Hi

     

    You're looking for the MS Access function. It's not used in T-SQL.

     

    You can use the Case statement: http://msdn.microsoft.com/en-us/library/ms181765%28v=SQL.100%29.aspx

    from BOL

    C. Using CASE to replace the IIf function that is used in Microsoft Access
    
    
    SELECT FirstName, LastName, TelephoneNumber, 
       IIf(IsNull(TelephoneInstructions),"Any time",
       TelephoneInstructions) AS [When to Contact]
    FROM db1.ContactInfo; 
    
    
    The following example uses CASE to provide an output value for the TelephoneSpecialInstructions column in the AdventureWorks view Person.vAdditionalContactInfo.
    
    
    
    USE AdventureWorks;
    GO
    SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' = 
       CASE
         WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
         ELSE TelephoneSpecialInstructions
       END
    FROM Person.vAdditionalContactInfo;
    

    Regards,

    Jason

     

     


    MCITP BI Developer 2008 - MCTS SQL Server 2005
    Thursday, December 30, 2010 9:55 AM

All replies

  • Hmmm, there is no IIF function in T-SQL, are  you using SSRS expression?

    SELECT CASE WHEN sum(m_107)+sum(m_109)='0' THEN 1 ELSE sum(m_107)+sum(m_109) END

    Sorry , cannot test the above


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 30, 2010 9:52 AM
    Answerer
  • Hi

     

    You're looking for the MS Access function. It's not used in T-SQL.

     

    You can use the Case statement: http://msdn.microsoft.com/en-us/library/ms181765%28v=SQL.100%29.aspx

    from BOL

    C. Using CASE to replace the IIf function that is used in Microsoft Access
    
    
    SELECT FirstName, LastName, TelephoneNumber, 
       IIf(IsNull(TelephoneInstructions),"Any time",
       TelephoneInstructions) AS [When to Contact]
    FROM db1.ContactInfo; 
    
    
    The following example uses CASE to provide an output value for the TelephoneSpecialInstructions column in the AdventureWorks view Person.vAdditionalContactInfo.
    
    
    
    USE AdventureWorks;
    GO
    SELECT FirstName, LastName, TelephoneNumber, 'When to Contact' = 
       CASE
         WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
         ELSE TelephoneSpecialInstructions
       END
    FROM Person.vAdditionalContactInfo;
    

    Regards,

    Jason

     

     


    MCITP BI Developer 2008 - MCTS SQL Server 2005
    Thursday, December 30, 2010 9:55 AM