Re-using a calculated sub-select field in a view?

[RCS]

I've been running into more and more complexity with an application, because
as time goes on - we need more and more high-level, rolled-up information.
And so I've created views, and views that use other views.. and the queries
are getting slower and slower.

This morning, I'm working on something like this:

select
<some columns>,
"calculatedcolumn" = (select top 1 crap from stuff where
thingy='whatchamacallit')
from
someview

now, I realized that I need to really return "calculatedcolumn" in a couple
other places in the select like this - well, this is what I WANT to do:
select
<some columns>,
calculatedcolumn = (select top 1 crap from stuff where
thingy='whatchamacallit'),
otherfield = case SomeBit
when 1 then calculatedcolumn
else count(somefield)
end,
otherfield1 = case SomeotherBit
when 1 then calculatedcolumn
else sum(somefield)
end,
otherfield2 = case SomeBit2
when 1 then calculatedcolumn
else avg(somefield)
end,
otherfield3 = case SomeBit3
when 1 then calculatedcolumn
else count(somefield)
end,
from
someview

Point is, I CAN'T do that, so I have to re-run that sub-select for EACH of
these cases, and that is KILLING this stored procedure. It seems to me, that
if the database when and already got that field, for that row - I should be
able to re-use, rather than going back out additional times.

Is there a way to so this? Put simpler:

select
x = (select top 1 user_id from users),
bestUser=x,
smartestUser=x
from
Users

can I re-use "x" in that example. Thanks!

[Erland Sommarskog]

RCS (rs****@gmail.com) writes:

Is there a way to so this? Put simpler:

select
x = (select top 1 user_id from users),
bestUser=x,
smartestUser=x
from
Users

can I re-use "x" in that example. Thanks!

Maybe. It depends a little what is in that subquery. If it's uncorrelated
to the main query, you could to:

SELECT x.x, bestUser = CASE WHEN ... THEN x ELSE NULL END, ...
FROM users
CROSS JOIN (SELECT TOP 1 user_id FROM users ORDER BY somecol) AS x

Here I'm using a derived table. A derived table is kind of a temp table
in the middle of the query. However, it is never materialized, and SQL
Server may not even compute the table as such, but can recast computation
order as result is the same.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[RCS]

Thanks for the response!

In this particular case, the general gist is that this is for tracking
scores for tests.. the outer select is for general information (module name,
etc) and the subselects are tallying up total users, how many passed, how
many failed.. so in psuedo sql, it's something like this (this is very, very
over-simplied - but hopefully is clear):

select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = (select count(distinct userid) from modulescores where
moduleid=m.moduleid and didpass=1)
from module as m

but it gets more complicated, I have to add some logic in for several fields
that say "if the module is a training module, then return back 'totalusers'
becuase even just finishing a practice module, should be considered
'passing'". For example.. So what I am doing now (using the same example
above) is:

select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = case moduleistraining
when 1 then (select count(*) from moduleusers where moduleid =
m.moduleid) -- this is the same as "totalusers" above
else (select count(distinct userid) from modulescores where
moduleid=m.moduleid and didpass=1)
end
from module as m

But that "(select count(*) from moduleusers where moduleid = m.moduleid)"
(again, as a simple example) is in real life, a complex join, to a couple of
views and is slow - and I have to keep re-using it in other select fields.

I've never had a use for, or maybe never understood the "cross join"
statment - and I'm not clear on how you mean to use it below. Could you
elaborate?? Thanks again!!
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

RCS (rs****@gmail.com) writes:

Is there a way to so this? Put simpler:

select
x = (select top 1 user_id from users),
bestUser=x,
smartestUser=x
from
Users

can I re-use "x" in that example. Thanks!

Maybe. It depends a little what is in that subquery. If it's uncorrelated
to the main query, you could to:

SELECT x.x, bestUser = CASE WHEN ... THEN x ELSE NULL END, ...
FROM users
CROSS JOIN (SELECT TOP 1 user_id FROM users ORDER BY somecol) AS x

Here I'm using a derived table. A derived table is kind of a temp table
in the middle of the query. However, it is never materialized, and SQL
Server may not even compute the table as such, but can recast computation
order as result is the same.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

RCS (rs****@gmail.com) writes:

select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = case moduleistraining
when 1 then (select count(*) from moduleusers where moduleid =
m.moduleid) -- this is the same as "totalusers" above
else (select count(distinct userid) from modulescores where
moduleid=m.moduleid and didpass=1)
end
from module as m

SELECT m.modulname, mu.totalusres,
passed = CASE m.moduleistraing
WHEN 1 THEN mu.totalusrers
ELSE ms.didpass
END
FROM modules m
JOIN (SELECT moduleid, totalusers = COUNT(*)
FROM moduleusers
GROUP BY moduleid) mu ON m.moduleid = mu.moduleid
JOIN (SELECT moduleid, didpass = COUNT(DISTINCT userid
FROM modulescores
WHERE didpass = 1
GROUP BY moduleid) AS ms ON m.moduleid = ms.moduleid

See my preivious post for the explanation of the derived tables.
I've never had a use for, or maybe never understood the "cross join"
statment - and I'm not clear on how you mean to use it below. Could you
elaborate?? Thanks again!!

A cross join gives all possible combinations; this is the famous
"carteisan product". I used a cross join this case because that was
how I could translate your very loose example.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[RCS]

Ah-HA!!! Very nice - thanks very much!!!

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

RCS (rs****@gmail.com) writes:

select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = case moduleistraining
when 1 then (select count(*) from moduleusers where moduleid =
m.moduleid) -- this is the same as "totalusers" above
else (select count(distinct userid) from modulescores where
moduleid=m.moduleid and didpass=1)
end
from module as m

SELECT m.modulname, mu.totalusres,
passed = CASE m.moduleistraing
WHEN 1 THEN mu.totalusrers
ELSE ms.didpass
END
FROM modules m
JOIN (SELECT moduleid, totalusers = COUNT(*)
FROM moduleusers
GROUP BY moduleid) mu ON m.moduleid = mu.moduleid
JOIN (SELECT moduleid, didpass = COUNT(DISTINCT userid
FROM modulescores
WHERE didpass = 1
GROUP BY moduleid) AS ms ON m.moduleid = ms.moduleid

See my preivious post for the explanation of the derived tables.

I've never had a use for, or maybe never understood the "cross join"
statment - and I'm not clear on how you mean to use it below. Could you
elaborate?? Thanks again!!

A cross join gives all possible combinations; this is the famous
"carteisan product". I used a cross join this case because that was
how I could translate your very loose example.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ellen K]

It's difficult to understand what you are really trying to do, but if
calculatedcolumn is going to be the same all the way through, DECLARE
and SET it upfront, i.e.
DECLARE @CalculatedColumn int <or whatever>
SET @CalculatedColumn = (select top 1 crap from stuff where
thingy='whatchamacallit')

then every time you need it again, just put @CalculatedColumn instead
of the whole statement that figures it out.

[Ian Thomas]

A variable could be used if you know the result is going to be static.

I've also run into a problem similar to this when updating a table
with calculations, but when the calculations are based off the current
row it's updating the use of a variable gets thrown out the window :(
I had to keep reselecting the result, a correlated sub-query really,
but with my update statement I used it a hell of a lot and a simple
update statement grew to over 300 lines :)

But hey, with a cursor it took 40 mins with 1.5 million rows, now it
takes 5 mins :)

I'm going to take a look into that derived table example, might be
able to speed my statements up! Cheers!

Ian
"Ellen K" <ek*******@yahoo.com> wrote in message news:<11**********************@f14g2000cwb.googleg roups.com>...

It's difficult to understand what you are really trying to do, but if
calculatedcolumn is going to be the same all the way through, DECLARE
and SET it upfront, i.e.
DECLARE @CalculatedColumn int <or whatever>
SET @CalculatedColumn = (select top 1 crap from stuff where
thingy='whatchamacallit')

then every time you need it again, just put @CalculatedColumn instead
of the whole statement that figures it out.