asked
Need Help in Writing View in SQL
I have 2 Tables in Sql server one is called PO RECEIPT TABLE and Another is Move Order Table. I 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 Group by Receipt Number and will Minus this Quantity from the quantity Received Cols in 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."**
OK thanks... I am Stuck in this View From View Days. It stops my Development without this I can't Move Forward..
If any of the answers below is helpful, please indicate that by clicking on the thumbs up next to each helpful answer. If any of the answers solved your problem, indicate that by clicking on the checkbox next to that answer.
You've put a query in as part of the columns of your table. When you do something like this it can only ever return a single value. If it returns more than one value you get the error you see there. Instead of having it as part of the column list, make it a derived table and join to it. ( SELECT SUM([Move_Order].[Move Quantity]) FROM [Move_Order] GROUP BY [Move_Order].[Receipt Number] ) Can be a table. Then you can use the columns as needed.
I correct that Issue. the New Query is SELECT DISTINCT [5_PO_RECEIPT_TABLE_DATABASE].[Part Number] , [5_PO_RECEIPT_TABLE_DATABASE].[Receipt Number] , [5_PO_RECEIPT_TABLE_DATABASE].[Quantity Received] + ( SUM([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] GROUP BY [5_PO_RECEIPT_TABLE_DATABASE].[Part Number] , [5_PO_RECEIPT_TABLE_DATABASE].[Receipt Number] , [5_PO_RECEIPT_TABLE_DATABASE].[Quantity Received] The Issue is It work Fine when Data Present Both in 5_PO_RECEIPT_TABLE and MOVE_ORDER table, but when the Move Order is Empty It does not return any Value.. What I want when the Move Order table will be Empty I want to get the Result of 5_PO_RECEIPT TABLE... Thanks in Advance for your Help..
That'd be because of the INNER JOIN - that will remove any rows where there are non-matching rows (effectively what you have). replace it with an OUTER JOIN, and change the SUM() to SUM(ISNULL(MoveOrder.MoveQuantity, 0)) - should do the job. Something like that, anyway.
16 People are following this question.
