SQL Views - self reference

Hi, I would really appreciate your help on this:

-I have a view vw_VIEW1 with fields AAA and BBB, which are based on some external tables using SELECT Statements.

-trying to calculate a third field CCC so that CCC=somefunction(AAA,BBB), but the view won't let me because
the view columns AAA and BBB are not allowed to be used directly. Error message is that "self-referencing" is not allowed in views.

-Since my fields AAA and BBB each involve a long SELECT query of their own, it's impossible to pass the entire arguments
used to calculate AAA and BBB into somefunction() to calculate CCC.

Is there a way to calculate CCC in the in the view (or in a stored procedure?)
Thanks a lot!

Can you submit the SQL for View1 with fields AAA and BBB, as well as the code for the function? It's hard to advise when you have not described what it is you are trying to accomplasih ...

Here is one example:


CREATE VIEW dbo.vw_Test
AS

SELECT

Val1,
Val2,
Val3,
(SELECT MAX(Val3) FROM tblTest) AS Max3,
[Val3]*[Max3] AS Result

FROM tblTest


When attempting to store the value [Val3]*[Max3] to a new column 'Result',
SQL says Max3 is not a valid column.
Was wondering if there is a way to use the Value of Max3 in the calculation of other columns

Thanks

Repeat the SQL you used for Max3 in the next expression

SELECT
Val1,
Val2,
Val3,
(SELECT MAX(Val3) FROM tblTest) AS Max3,
(SELECT [Val3]* MAX(Val3) FROM tblTest) AS Result
FROM tblTest

Something else you can do is in-line select. Most inside SQL is generated first, outer SQL runs based on results of inner like so:

Select Val1, Val2, Val3,Max3, [Max3] * [Val3] as result from
(SELECT
Val1,
Val2,
Val3,
(SELECT MAX(Val3) FROM tblTest) AS Max3,
FROM tblTest)

So the above is equivalent to first making a view out of the red select statement called view1 and then selecting from it like so:

Select Val1, Val2, Val3, [Max3] * [Val3] as result
from view1
That is why it is called in-line select, you don't actually make a view you just ask the SLQ to execute "in line"

Thanks very much!!!

That 2nd case was really useful: since the SELECT statement that
produces the equivalent of variable Max3 is very long in the view i'm building.

Glad I could help