Post reply

Derived fields in a View

  • January 4, 2006 at 10:13 am

    #112127

    Hi,

    I'm trying to derive a new field in a view. I want to show a value depending on the value of another field in the table. eg

    IIF(FileName = 'Retail', NumberOfRecords, '0')

    ie. I want to bring back the NumberofRecords value if the filename is 'Retail' and if not bring back '0'

    I know that IIF is not supported in T-SQL, does anyone know an alternative way I can derive this field in my view

    Regards

     

  • January 4, 2006 at 10:28 am

    #612668

    CASE ... WHEN ... END is the T-SQL equivalent to IIF()

    CASE

    WHEN FileName = 'Retail' THEN NumberOfRecords

    ELSE 0

    END

  • January 4, 2006 at 10:36 am

    #612673

    Yes but Case is not allowed in the query designer either

  • January 4, 2006 at 10:49 am

    #612681

    Which "query designer" ?

    CASE is legal T-SQL and you can use it in views, so you shouldn't let a UI based designer flaw influence your view definition.

  • January 4, 2006 at 10:54 am

    #612687

    Thanks a lot. This works. I've pasted it in through the properties window.

    Bollocks that you can't do it the easy way though through the GUI

  • January 5, 2006 at 11:26 am

    #612980

    Which GUI doesn't allow you to use CASE? I've never used a 3rd party environment, SQL Query Analyzer has always let me take care of business.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply