Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Unanswered: Conditional Views

    I have a view which has to be versatile enough to perform different calculations. For instance, using the view below I may want the stock level for that particular wine or in another instance I would the need the stock level for all wines that share the same type. Note that I would never need a combonation of both in the same view.

    Is there anyway to specify <myVar> before or when calling this view?

    Code:
    CREATE VIEW vwWines
    AS
    
    SELECT
    	tblWine.ID,
    	tblWine.WineTypeID,
    	StockQty = 
    	CASE
    		WHEN <myVar> = 1
    		THEN tblWine.StockQty
    		ELSE
    		(
    			SELECT
    				SUM(StockQty)
    			FROM
    				tblWine AS tblWineTwo
    			WHERE
    				tblWine.WineTypeID = tblWineTwo.WineTypeID
    		)
    	END
    FROM
    	tblWine
    Note: This view has been simplified and been done on the fly so may contain errors

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do this as a stored procedure and pass the variable in from the application.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Why? Why not have two views and let the front end decide which one to call, based on the need of the operator? Follow the KISS principle.

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

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Keep It Simple Stupid? It took me a minute to remember that one.

    I do not know. Maybe having one peice of code to maintain instead of 2. It does not really matter. Either way is valid.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    Exactly, I had thought of that but there is other C# code that are executed on the results and to duplicate it would cause more work in the long run.

    I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view. It automatically produces the stored procedures and connection code. I wouldn't want to do it this way as I'd have to provide other means of connecting to the database which again isn't good for maintainence.

    I'm going to experiment a little more but if I can't crack it, I'm going to go with the method of creating two view just to keep the code cleaner.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by GoMo
    I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view.
    Dear Lord...please tell me you aren't using NHibernate.
    Tools such as this are a bad idea. They inevitably lead to ineffecient code, unscalable applications, and insecure databases.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    Lol, I'm using something called MyGeneration dOOdads and it seems to have worked find over the past two years over various projects. Obviously its not flexible enough to cover problems like these but for what it provides, it's definitely worth the trade off. It's the #1 downloaded .NET tool on Download.com, apparently.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "An Amazing 48k Architecture that Supports the 1.1 and 2.0 .NET Framework
    Transactions, Dynamic Queries, and a Highly Intuitive API"
    Dynamic Queries are to be AVOIDED. Tools such as this violate the most basic principles of database application design. They do so in the name of short-term development gains, and at the expense of long-term quality.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    But surely you would require the use of dynamic queries even without the help of an ORM tool, for example, an advanced search form. Or am I thinking of the wrong sort of dynamic queries?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, you would not need dynamic queries for an automated search form. And if dynamic sql is required it should be constructed with a stored procedure, not by an interface or middle-tier, which should not even have access to the underlying tables.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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