Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    4

    Unanswered: IIF function or case

    Hi I am wanting to use some thing like the IIF function in Access in a SQL view I did some looking n your Forum and found the case function its awsome does what I need but i can not use it in a view. Does any one have an alternate solution .. Thanks Jakes

    My "Case"


    USE SysproCompanyB
    GO
    SELECT dbo.ZZCuCostValue.Supplier, dbo.ZZCuCostValue.StockCode, dbo.ZZCuCostValue.[Year], dbo.ZZCuCostValue.[Month],'RandCost' =
    CASE
    WHEN BuyMulDiv IS NULL THEN '0'
    WHEN BuyMulDiv = 'M' THEN round(dbo.ZZCuCostValue.UnitCost * dbo.ZZCuCostValue.ExchangeRate,4)
    WHEN BuyMulDiv = 'D' THEN round(dbo.ZZCuCostValue.UnitCost / dbo.ZZCuCostValue.ExchangeRate,4)
    ELSE 0
    END
    FROM dbo.ApSupplier INNER JOIN
    dbo.TblCurrency ON dbo.ApSupplier.Currency = dbo.TblCurrency.Currency INNER JOIN
    dbo.ZZCuCostValue ON dbo.ApSupplier.Supplier = dbo.ZZCuCostValue.Supplier

    GO

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Run the create view script in Query Analyzer, and you should not get the Enterprise Mangler error message. CASE is perfectly fine in a view, but EM has problems with it.

  3. #3
    Join Date
    Nov 2006
    Posts
    4
    Thanks I will try that

  4. #4
    Join Date
    Nov 2006
    Posts
    4
    Hi MCrowley. This may sound realy simple how do I run the script in Query Analyzer I can not seem to find any thing that looks fimilar..

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Are you in Enterprise Manager? If so, click on the Tools Menu Item then click on SQL Query Analyzer. Then, select your database from the dropdown atthe top middle of the screen, cut and paste your code, then click on the green arrow next to the blue checkmark to execute the script.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Nov 2006
    Posts
    4
    HI Tomh53 thanks for that but I was wanting to know how to run the create view script that MCrowley told me about.
    Jakes

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Here is a sample. Replace the select statement with your query:

    Code:
    create view vwTest
    as
    select *
    from pubs..authors

Posting Permissions

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