Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Location
    Bay Area CA
    Posts
    15

    Unanswered: no case statement in views + sql 7

    I have a query which will be a subquery that I want to use a case statement on. (It is an outer join and I want to substitue nulls for a specific value.) I can make it work in query analyzer and can also make it work in a stored procedure, but I get a message that the case statement is not supported in views. So, I tried to make a function ,but they are not supported in SQL 7, and apparently stored procedures cannot be called inline in views, either. I can retrieve the desired recordset with exec sp_name, but I need the recordset to be used in another query and cannot figure out how to use the results from a Stored procedure as a subquery in a view. Any help is greatly appreciated.

    Thanks,

    Tman2

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Case statements are supported in views, but the GUI query designer built into Enterprise Manager cannot parse and graphically display statements using CASE. You can write your query using the Query Analyzer tool instead (no serious TSQL programmer uses the GUI).
    Regardless, you do not need a CASE statement to substitute NULL for a specific value. Use the NULLIF() function instead.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Sep 2005
    Location
    Bay Area CA
    Posts
    15
    I was able to create a view in query designer that uses case, but it does not function correctly. (It defaults all values to 0). However, if I use the exact same SQL statement in query designer without it being a view, it works. (ie. create view sQL statement to create view, select * from view produces erroneous results, but SQL statement in query designer works.)

    I will try the nullif function you mentioned, but htat will only work for this particular cases, and there will be many instances where I will need the case statement. Any ideas what to do? This only seems to be causing a problem in SQL 7, not the newer versions...

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post your code if you want somebody here to review it. There could be a problem with default connection settings in Query Analyzer. The SET CONCAT NULL option, for instance, has bitten me several times...
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Sep 2005
    Location
    Bay Area CA
    Posts
    15
    It probably is something like that. WHEN NULL was not identifying nulls as nulls. Any ideas, or this there something wrong with my syntax (ie must use isnull like in VB)? Thanks for all your help. I have it working now, (really can just use isnull without the case statement at all) but for academic purposes, I would be intrested to know if there is an option that changes this. I did a little research on the option you pointed out above, what a #!@* that must have been to find...

    Thanks again.

Posting Permissions

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