Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    14

    Post Unanswered: Passing parameter to a sql view

    Is there any way I can do this?
    Last edited by sqlnewbiz; 12-10-14 at 09:28.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Let'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) Correlated-subqueries are a performance nightmare and should be avoided at all costs
    7) Your final correlated-subquery 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 = 'PUR-STK'
            AND    p.PartNum = T.PartNum
           ) As BuyQty
         , (
            SELECT Coalesce(Sum(p.TranQty), 0)
            FROM   dbo.PartTran As p
            WHERE  p.TranType = 'STK-UKN'
            AND    p.PartNum = T.PartNum
           ) As purchRet
         , (
            SELECT Coalesce(Sum(p.TranQty), 0)
            FROM   dbo.PartTran As p
            WHERE  p.TranType = 'STK-CUS'
            AND    p.PartNum = T.PartNum
           ) As SaleQty
         , (
            SELECT Coalesce(Sum(p.TranQty), 0)
            FROM   dbo.PartTran As p
            WHERE  p.TranType = 'INS-STK'
            AND    p.PartNum = T.PartNum
           ) As SaleRet
         , (
            SELECT Coalesce(Sum(p.TranQty), 0)
            FROM   dbo.PartTran As p
            WHERE  p.TranType = 'ADJ-QTY'
            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
    Okay, now time for a re-write.

    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
    Easy, right?

    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      = 'PUR-STK' THEN TranQty ELSE 0 END) As BuyQty
         , Sum(CASE WHEN TranType      = 'STK-UKN' THEN TranQty ELSE 0 END) As PurchRet
         , Sum(CASE WHEN TranType      = 'STK-CUS' THEN TranQty ELSE 0 END) As SaleQty
         , Sum(CASE WHEN TranType      = 'INS-STK' THEN TranQty ELSE 0 END) As SaleRet
         , Sum(CASE WHEN TranType      = 'ADJ-QTY' 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
    Neat, huh?

    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!
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just curious, but does:
    Code:
    SELECT
       Coalesce(Sum(CASE WHEN 'OB' = TranReference THEN TranQty END), 0) AS OpQty
    ,  Coalesce(Sum(CASE WHEN 'PUR-STK' = TranType THEN TranQty END), 0) AS BuyQty
    ,  Coalesce(Sum(CASE WHEN 'STK-UKN' = TranType THEN TranQty END), 0) AS PurchRet
    ,  Coalesce(Sum(CASE WHEN 'STK-CUS' = TranType THEN TranQty END), 0) AS SaleQty
    ,  Coalesce(Sum(CASE WHEN 'INS-STK' = TranType THEN TranQty END), 0) AS SaleRet
    ,  Coalesce(Sum(CASE WHEN 'ADJ-QTY' = 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 <= '2012-01-02'
       GROUP BY t.PartNum, t.PartDescription, t.Company, t.TranDate
    work for you?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2013
    Posts
    14
    Hi Gvee,

    Thankyou for the help and rewritten query.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •