Need Help in Writing VIEW in SQL Server

Question

I have 2 tables in SQL Server, one is called PO RECEIPT table and the other is Move Order table.

I need to write a query to insert data into Receipt table which is working fine. Receipt Number will be unique in Receipt table because it is a primary key and auto increment, e.g, for Receipt number 1 I have 2000 quantity received (it is a column).

If I want to move 500 to the move and another 500 next time for same receipt number. Now I want to write a VIEW which will add up move quantity in the Move Order table grouped by Receipt Number and will minus this quantity from the quantity received column in the Receipt table.

Currently I write this view which is working fine for same Receipt Number but when we have different Receipt Number in the Move Order table it generates an error. The view is

select distinct 
    [5_PO_RECEIPT_TABLE_DATABASE].[Part Number],
    [5_PO_RECEIPT_TABLE_DATABASE].[Receipt Number],
    (select sum([Move_Order].[Move Quantity]) 
     from [Move_Order] 
     group by [Move_Order].[Receipt Number]),
    [5_PO_RECEIPT_TABLE_DATABASE].[Quantity Received] + [Move_Order].[Move Quantity] as TotalQuantity 
from 
    [5_PO_RECEIPT_TABLE_DATABASE]
inner join 
    [Move_Order] on [5_PO_RECEIPT_TABLE_DATABASE].[Receipt Number] = [Move_Order].[Receipt Number]

The error generated by above view is

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Answer 1

Well, the error is pretty clear: your subquery (the (select sum(..) from [Move_order].... returns multiple values - and you cannot have a subquery return multiple values if you use it in the SELECT list of columns...

Most likely, you just want to the SUM([Move_Order].[Move Quantity]) for that one Move_Order you're currently looking at - so you need to change your subquery to take that into account:

SELECT DISTINCT
    rcpt.[Part Number],
    rcpt.[Receipt Number],
    (SELECT SUM(m.[Move Quantity]) 
     FROM [Move_Order] m
     WHERE m.[Receipt Number] = rcpt.[Receipt Number]),
    rcpt.[Quantity Received] + mo.[Move Quantity] AS TotalQuantity 
FROM
    [5_PO_RECEIPT_TABLE_DATABASE] rcpt
INNER JOIN 
    [Move_Order] mo ON rcpt.[Receipt Number] = mo.[Receipt Number]

I also use meaningful / self-explanatory table aliases to make a query a lot more readable ....

So now, your subquery will sum up all the [Move Quantity] values from the [Move Order] table for that one [Receipt Number] that is currently being processed.

Answer 2

Also in SQLServer2005+ you can use option with OVER() clause

SELECT DISTINCT
       rcpt.[Part Number],
       rcpt.[Receipt Number], 
       SUM(mo.[Move Quantity]) OVER(PARTITION BY rcpt.[Receipt Number]),
       rcpt.[Quantity Received] + mo.[Move Quantity] AS TotalQuantity
FROM [5_PO_RECEIPT_TABLE_DATABASE] rcpt
  INNER JOIN [Move_Order] mo ON rcpt.[Receipt Number] = mo.[Receipt Number]

BTW, I think DISTINCT keyword is excessive in this case because your values in the TotalQuantity column is differ