Results 1 to 4 of 4
Thread: Passing parameter to a sql view

121014, 08:24 #1Registered User
 Join Date
 Mar 2013
 Posts
 14
Unanswered: Passing parameter to a sql view
Is there any way I can do this?
Last edited by sqlnewbiz; 121014 at 09:28.

121014, 08:57 #2www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,444
Provided Answers: 12Let's not beat around the bush  that's a terrible query. Sorry, but that's just how it is.
The query you posted even has syntax errors!
What query builder did you use to produce this code?
You need to understand
1) TOP has no meaning without ORDER BY
2) TOP 100 PERCENT is 100% pointless
3) Formatting a query for readability is very important.
4) DISTINCT and GROUP BY needn't be mixed.
5) TOP 1 isn't needed where your Sum() aggregate returns only a single result (no grouping)
6) Correlatedsubqueries are a performance nightmare and should be avoided at all costs
7) Your final correlatedsubquery doesn't contain an aggregate  which "MtlUnitCost" should it be picking up? See point 1 above.
8) You cannot pass a parameter to a view. You can pass parameters to a stored procedure or function, but a view is basically a virtual table.
Here's your mammoth query reformatted with the redundancy removed:
Code:SELECT ( SELECT Coalesce(Sum(p.TranQty), 0) FROM dbo.PartTran As p WHERE p.TranReference = 'OB' AND p.PartNum = T.PartNum ) As OpQty , ( SELECT Coalesce(Sum(p.TranQty), 0) FROM dbo.PartTran As p WHERE p.TranType = 'PURSTK' AND p.PartNum = T.PartNum ) As BuyQty , ( SELECT Coalesce(Sum(p.TranQty), 0) FROM dbo.PartTran As p WHERE p.TranType = 'STKUKN' AND p.PartNum = T.PartNum ) As purchRet , ( SELECT Coalesce(Sum(p.TranQty), 0) FROM dbo.PartTran As p WHERE p.TranType = 'STKCUS' AND p.PartNum = T.PartNum ) As SaleQty , ( SELECT Coalesce(Sum(p.TranQty), 0) FROM dbo.PartTran As p WHERE p.TranType = 'INSSTK' AND p.PartNum = T.PartNum ) As SaleRet , ( SELECT Coalesce(Sum(p.TranQty), 0) FROM dbo.PartTran As p WHERE p.TranType = 'ADJQTY' AND p.TranReference <> 'OB' AND p.PartNum = T.PartNum ) As AdjQty , ( SELECT TOP (1) p.MtlUnitCost FROM dbo.PartTran As p WHERE p.PartNum = T.PartNum ) As unitcost , t.PartNum , t.PartDescription , t.Company , t.TranDate FROM dbo.PartTran As T GROUP BY t.PartNum , t.PartDescription , t.Company , t.TranDate
Because all of your subqueries are against dbo.PartTran, we can almost certainly replace them with a single join.
But wait, your outermost table is also dbo.PartTran  so why do we even need a join? (A: we probably don't!)
How would it look if we just wanted a total TranQty for our grouping?
Code:SELECT PartNum , PartDescription , Company , TranDate , Sum(TranQty) As TotalTranQty FROM dbo.PartTran GROUP BY PartNum , PartDescription , Company , TranDate
Now comes the fun part. A little trick using CASE statements:
Code:SELECT PartNum , PartDescription , Company , TranDate , Sum(TranQty) As TotalTranQty , Sum(CASE WHEN TranReference = 'OB' THEN TranQty ELSE 0 END) As OpQty , Sum(CASE WHEN TranType = 'PURSTK' THEN TranQty ELSE 0 END) As BuyQty , Sum(CASE WHEN TranType = 'STKUKN' THEN TranQty ELSE 0 END) As PurchRet , Sum(CASE WHEN TranType = 'STKCUS' THEN TranQty ELSE 0 END) As SaleQty , Sum(CASE WHEN TranType = 'INSSTK' THEN TranQty ELSE 0 END) As SaleRet , Sum(CASE WHEN TranType = 'ADJQTY' AND TranReference <> 'OB' THEN TranQty ELSE 0 END) As AdjQty , Min(MtlUnitCost) As MinUnitCost , Max(MtlUnitCost) As MaxUnitCost , Avg(MtlUnitCost) As AvgUnitCost FROM dbo.PartTran GROUP BY PartNum , PartDescription , Company , TranDate
Now to answer your bit about using some kind of parameter... just add a single WHERE clause!
Please ask any questions you might have  it is very important that you understand this query, as you'll no doubt have to support it, and maybe even write more like it at a later date!

121014, 09:18 #3Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Just curious, but does:
Code:SELECT Coalesce(Sum(CASE WHEN 'OB' = TranReference THEN TranQty END), 0) AS OpQty , Coalesce(Sum(CASE WHEN 'PURSTK' = TranType THEN TranQty END), 0) AS BuyQty , Coalesce(Sum(CASE WHEN 'STKUKN' = TranType THEN TranQty END), 0) AS PurchRet , Coalesce(Sum(CASE WHEN 'STKCUS' = TranType THEN TranQty END), 0) AS SaleQty , Coalesce(Sum(CASE WHEN 'INSSTK' = TranType THEN TranQty END), 0) AS SaleRet , Coalesce(Sum(CASE WHEN 'ADJQTY' = TranType AND 'OB' <> TranReference THEN TranQty END), 0) AS BuyQty , Max(t.MtlUnitCost) AS unitcost , t.PartNum, t.PartDescription, t.Company, t.TranDate FROM dbo.PartTran AS t WHERE TranDate <= '20120102' GROUP BY t.PartNum, t.PartDescription, t.Company, t.TranDate
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

121014, 09:19 #4Registered User
 Join Date
 Mar 2013
 Posts
 14
Hi Gvee,
Thankyou for the help and rewritten query.