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?

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 errorsdo this as a stored procedure and pass the variable in from the application.|||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.|||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.|||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.|||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.|||Lol, I'm using something called MyGeneration dOOdads (http://www.mygenerationsoftware.com/portal/dOOdads/Overview/tabid/63/Default.aspx) 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.|||"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.|||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?|||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.

Conditional statements in Views

Hi all,
Another interesting question for ya :P
When contructing a view, I hit across a field that internally is stored as a
single charactor to represent a status, like 'P' = Pending, 'C' = cancelled
etc.
Now, when I create a view, I want this view to say the full word 'Pending'
or 'Cancelled' etc, but when I try to write a conditional expression it kick
it out!
When I use IF statement, it assumes its all a string, and if I use the IIF
it says that the function doesn't exist!?
Seems a little strange how something as simple as a conditional statement
can be made so difficult, so please, someone put me out of my misery and tel
l
me how its done! :P
ThanksTry CASE
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||> but when I try to write a conditional expression it kick it out!
Can you be more specific? What conditional expression did you try? What
does "kick it out" mean? Do you get an error message? If so, what is it?
What tool are you using to create your view?

> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
(a) you can't use IF in a view. A view is a query, and is not eligible for
logic flow (if is not a conditional expression).
(b) there is no IIF in T-SQL. The closest place you will find this is
Analysis Services, and then Access.
Perhaps you meant to use CASE.
CREATE VIEW dbo.myView
AS
SELECT status = CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END, other columns
FROM table
However, the view designer in Enterprise Manager won't allow for CASE, so I
recommend you get in the habit of creating such scripts in Query Analyzer.
See http://www.aspfaq.com/2455
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Use CASE
SELECT
CASE colname
WHEN 'p' THEN 'Pending'
WHEN 'c' THEN 'Cancelled'
ELSE NULL
END
, colname2
FROM...
Or, create another table with two columns, one for the code and another code
the description and do
a join between the tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9C6EAC75-FCAB-4860-9651-F26D31CD05B7@.microsoft.com...
> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||The CASE expression is what you need and it's actually much more
powerful than the IIF function that you are probably familiar with from
Access and VB:
SELECT ... ,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END AS status
FROM YourTable
or
SELECT ... ,
CASE
WHEN status = 'P' THEN 'Pending'
WHEN status = 'C' THEN 'Cancelled'
END AS status
FROM YourTable
David Portas
SQL Server MVP
--|||Try the case statement.
CASE WHEN [Field]='C' THEN 'Cancelled' WHEN [Field]='P' THEN 'Pending' ...
ELSE 'default text' END AS [Aliased Field Name]
You can put in as many WHEN clauses as you like, using the syntax above.
Also note that you don't need the ELSE clause. Don't forget the END like I
always do.
Note that IIF isn't a SQL function. It works in Jet DB queries, but not SQL
Server.
"-Ldwater" wrote:

> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||Thanks all, were a little new at writing views, and it seems a bit.. well,
stupid if the CASE statement isn't supported in the Enterprise manager
Thanks for the hints, I think were gonna keep looking into it!|||Enterprise Manager really isn't designed to be a full featured query writing
environment. It is a MANAGEMENT tool and is really not used by anyone
writing serious queries.
Query Analzyer is for writing queries.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:8421F4B4-F86C-40BA-B2DF-3597AB73E7DE@.microsoft.com...
> Thanks all, were a little new at writing views, and it seems a bit.. well,
> stupid if the CASE statement isn't supported in the Enterprise manager
> Thanks for the hints, I think were gonna keep looking into it!