CODEWITHSUNDEEP
sqlsql-servert-sql
Andre Figueiredo
Andre Figueiredo

Reputation: 13425

Propagate WHERE clause in a View to subquery

I have to transform this query in a View:

SELECT col1,
       CONVERT(decimal(5, 4), SUM(Volume) / CONVERT(float, (
                                        SELECT SUM(Volume) 
                                        FROM A INNER JOIN B ON [..]
                                        WHERE A.CodA = @param1 AND CodB.[Year] = @param2))
        ) AS [VolPercent]
FROM A INNER JOIN B ON [..]
WHERE A.CodA = @param1 AND CodB.[Year] = @param2

So, I have to filter subquery with same clausule from external query.
What would be something like this:

SELECT col1,
       CONVERT(decimal(5, 4), SUM(Volume) / CONVERT(float, (
                                        SELECT SUM(Volume) 
                                        FROM A as C INNER JOIN B as D ON [..]
                                        WHERE C.CodA = A.CodA AND D.[Year] = B.[Year]))
        ) AS [VolPercent]
FROM A INNER JOIN B ON [..]

How can I do that? Is there a way?

Upvotes: 0

Views: 243

Answers (1)

simon at rcl
simon at rcl

Reputation: 7344

Creating a view without the WHERE and then querying the View with the WHERE clause should give exactly the same results with exactly the same performance. However you need to add the columns you will use in the where clause. So yes, create your view as

create view MyView as
SELECT ACod.A, CodB.[Year],
       col1,
       CONVERT(decimal(5, 4), SUM(Volume) / CONVERT(float, (
                                        SELECT SUM(Volume) 
                                        FROM A as C INNER JOIN B as D ON [..]
                                        WHERE C.CodA = A.CodA AND D.[Year] = B.[Year]))
        ) AS [VolPercent]
FROM A INNER JOIN B ON [..]

and then your query is:

select * from MyView
where A = whatever
and [Year] = whatever

Cheers -

Upvotes: 1

Related Questions