views dependent on other views

[lukster]

Hello There,

I'm trying to create a view that has calculations dependent on
calculations, where the problem resides is that each time I make a
calculation I must create an intermediate view so I can reference a
previous calculation.

for example lets say I have my_table that has columns a & b. now I want
a view that has a & b, c = a + b, and d = c + 1.

this is grossly simplified, the calculations I actually use are fairly
complex and copying / pasting them is out of the question.

so what I have is my_view_a which makes column c, and my my_view_final
which makes column d (however, in my real application I have 5 of these
views, a/b/c/d/e/)

is there anyway I can consolidate all these views into one? I was
thinking of using a stored procedure with temp tables or something
along those lines.

I just which I can use the aliases that I create for c in d in one
step.

any insight would be greatly appreciated.

[Hugo Kornelis]

On 14 Mar 2006 12:29:25 -0800, lu*****@gmail.com wrote:

Hello There,

I'm trying to create a view that has calculations dependent on
calculations, where the problem resides is that each time I make a
calculation I must create an intermediate view so I can reference a
previous calculation.

for example lets say I have my_table that has columns a & b. now I want
a view that has a & b, c = a + b, and d = c + 1.

this is grossly simplified, the calculations I actually use are fairly
complex and copying / pasting them is out of the question.

so what I have is my_view_a which makes column c, and my my_view_final
which makes column d (however, in my real application I have 5 of these
views, a/b/c/d/e/)

is there anyway I can consolidate all these views into one? I was
thinking of using a stored procedure with temp tables or something
along those lines.

I just which I can use the aliases that I create for c in d in one
step.

any insight would be greatly appreciated.

Hi lukster,

You can use derived tables instead of views:

SELECT a, b, c, c + 1 AS d
FROM (SELECT a, b, a + b AS c
FROM SomeTable
WHERE .....
) AS Der
WHERE .....

You can nest this if yoou need to.

Another technique is to repeat the calculation:

SELECT a, b, a + b AS c, (a + b) + 1 AS d
FROM SomeTable
WHERE .....

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

(lu*****@gmail.com) writes:

I'm trying to create a view that has calculations dependent on
calculations, where the problem resides is that each time I make a
calculation I must create an intermediate view so I can reference a
previous calculation.

for example lets say I have my_table that has columns a & b. now I want
a view that has a & b, c = a + b, and d = c + 1.

this is grossly simplified, the calculations I actually use are fairly
complex and copying / pasting them is out of the question.

so what I have is my_view_a which makes column c, and my my_view_final
which makes column d (however, in my real application I have 5 of these
views, a/b/c/d/e/)

is there anyway I can consolidate all these views into one? I was
thinking of using a stored procedure with temp tables or something
along those lines.

I just which I can use the aliases that I create for c in d in one
step.

I will have to take a long shot and guess what you are looking for.
I believe that the answer to your question is derived tables. A
derived table is sort of a temp table within the query, but only
logically. The actual computation order can often be different, as
long as the result is the same. Here is a quick example from the
Northind database that presents the number of orders per season.
The query features two derived tables. The innermost extracts day
and month from OrderDate, and the outer table translates the date
to a season.

SELECT Season, COUNT(*)
FROM (SELECT Season = CASE WHEN daymonth BETWEEN '0101' AND '0315' OR
daymonth BETWEEN '1201' AND '1231'
THEN 'Winter'
WHEN daymonth BETWEEN '0316' AND '0531'
THEN 'Spring'
WHEN daymonth BETWEEN '0601' AND '0831'
THEN 'Summer'
WHEN daymonth BETWEEN '0901' AND '1130'
THEN 'Autumn'
END
FROM (SELECT daymonth =
substring(convert(char(8), OrderDate, 112), 5, 4)
FROM Orders) AS O) AS O2
GROUP BY Season

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>> is there anyway I can consolidate all these views into one? <<

Sure, but why? You can nest VIEWs without any problems. Those other
views were probably constructed for a good reason. "Trust in the
Optimizer, Luke!"

However, look up the WITH CHECK OPTION on VIEWs; could be useful.

[Doug]

hmmm. use parenthesis. think about it logically step by step.

if you can do it with views, you can do it in a single select, if you
use good formatting and parentesis to keep your brain straight around
it.

luck
doug

[lukster]

Derived is exactly what I need,

thanks everyone for the prompt responses.

just in reponse to some of the comments:

Repeating wouldn't work because for what I'm doin' column 'c' is a
lengthy case statement which 4-5 other colums depend on, copy/pasting
that over and over woudl make the query unreadable.

why eleminate the views? I'd like to minimize the amount of views
because it's clutters my database, I'm well over 200 views now, most of
which are just intermediate steps, also. when working on the final view
I'd like to see all the steps at once, writing documentation for 5
views and having flip between them is annoying (especially with now
slow access is)

once again, thank you very much.