Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: "IF"'s in a view?

    I Access SQL one can imbed "IF" statements in a select query (or view).
    In SQL Server, Microsoft seems to only allow the use of control statements such as IF and CASE in Stored Procedures but not in a view.

    Question: Is there any way to use "IF" or "CASE" in a view? If a stored procdure is the best route, how do i access the results set afterwards?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Case works fine - please post the view you tried to create.

  3. #3
    Join Date
    Mar 2002
    Posts
    192
    Here is the SQL fragment converted from ACCESS IIF's to SQL Server CASE. I don't have access to the SQL Server at the moment but I will try out your suggestion first chance I get.


    CASE
    WHEN [IncidentHeaderDynamicsCustNmbr] Like '99900%' THEN
    [CustName],
    [CntcPrsn],
    [StmtName],
    [Address1],
    [Address2],
    [City],
    [State],
    [Country],
    [Zip],
    [Phone1],
    [Phone2],
    [Fax],
    WHEN IsNull(IncidentHeaderSiteID])=True And IsNull(IncidentHeaderClientAddressID])=True) THEN
    [CustName],
    [CntcPrsn],
    [StmtName],
    [Address1],
    [Address2],
    [City],
    [State],
    [Country],
    [Zip],
    [Phone1],
    [Phone2],
    [Fax],
    ELSE
    Trim([IncidentHeaderSiteCustName]))) AS InvoiceHeaderSiteCustName,
    Trim([IncidentHeaderSiteCntcPrsn]))) AS InvoiceHeaderSiteCntcPrsn,
    Trim([IncidentHeaderSiteStmtName]))) AS InvoiceHeaderSiteStmtName,
    Trim([IncidentHeaderSiteAddress1]))) AS InvoiceHeaderSiteAddress1,
    Trim([IncidentHeaderSiteAddress2]))) AS InvoiceHeaderSiteAddress2,
    Trim([IncidentHeaderSiteCity]))) AS InvoiceHeaderSiteCity,
    Trim([IncidentHeaderSiteState]))) AS InvoiceHeaderSiteState,
    Trim([IncidentHeaderSiteCountry]))) AS InvoiceHeaderSiteCountry,
    Trim([IncidentHeaderSiteZip]))) AS InvoiceHeaderSiteZip,
    Trim([IncidentHeaderSitePhone1]))) AS InvoiceHeaderSitePhone1,
    Trim([IncidentHeaderSitePhone2]))) AS InvoiceHeaderSitePhone2,
    Trim([IncidentHeaderSiteFax]))) AS InvoiceHeaderSiteFax,
    END

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    The problem looks like with your syntax (check out BOL) - the case statement goes as follows:

    CASE input_expression
    WHEN when_expression THEN result_expression
    [...n]
    [
    ELSE else_result_expression
    ]
    END

    or

    CASE
    WHEN Boolean_expression THEN result_expression
    [...n]
    [
    ELSE else_result_expression
    ]
    END

    Anyway, you have case when - but you are missing the end statement - Maybe you are thinking you can return multiple results - You will have to test for each case.

Posting Permissions

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