SQL Views – Inventory Mulitbin
The following view lists all your inventory bin quantities for GP installations that use Mulit-bin.
This view is also a modification of the spvInventoryMultiBin view from Salespad (www.salespad.net).
You can name the view whatever you want by change the [tspvInventoryMultiBin] below. Also the USE [TSP] tells the script to only execute on my database named TSP. You will need to modify the [TSP] to be whatever your database name is.
USE [TSP]
GO
/****** Object: View [dbo].[tspvInventoryMultibin] Script Date: 04/19/2010 16:51:43 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE view [dbo].[tspvInventoryMultibin]
AS
select top 2147483647
Item_Number = ITEMNMBR,
Location = LOCNCODE,
Bin = BIN,
Qty_Type = cast(CASE QTYTYPE when 1 then ‘On Hand’ when 2 then ‘Returned’ when 3 then ‘In Use’ when 4 then ‘In Service’ when 5 then ‘Damaged’ end as varchar(10)),
Quantity = QUANTITY,
Qty_Allocated = ATYALLOC ,
dex_row_id
from IV00112 (nolock)
order by dex_row_id
Trackbacks & Pingbacks
- SQL Views – Inventory Mulitbin - DynamicAccounting.net
- SQL Scripts – Stock Count (Multibin Inventories) « Real Life Dynamics User
- SQL Scripts – Stock Count (Multibin Inventories) - Real Life Dynamics User (RLDU)
- DynamicAccounting.net | SQL Views – Inventory MulitbinSQL Views – Inventory Mulitbin - DynamicAccounting.net
Real Life Dynamics User 
Hi Ron,
Is there any specific reason for mentioning “top 2147483647”? I know that the value is MAX for row count in SQL.
But what would happen if we do not mention it? Or what’s the advantage of mentioning it towards conventional querying?
Thanks
Vaidy Mohan
You know that is a good question. As you will notice on most of my SQL View (only the SQL Views) I have a reference to http://www.salespad.net. Most of the views I post are variations of Salespad views. This is an example of a view that I built off of a Salespad view. I wondered that myself, but I just posted it as it was.
Ron
I get the following error:
Msg 102, Level 15, State 1, Procedure tspvInventoryMultibin, Line 7
Incorrect syntax near ‘‘’.
Msg 154, Level 15, State 1, Procedure tspvInventoryMultibin, Line 7
a USE database statement is not allowed in a procedure, function or trigger.
Are you running this script in SQL Studio as a Query?
Ben,
Sorry for the delayed response…I have been out of the office this week. I am using SQL Server Management Studio to execute this statement. I am not sure why you are receiving the error message.
One option to try would be to remove the USE [XXX] and the following GO statement and just make sure you are executing the query on the proper database in the SSMS database selection drop down. Does that make sense?
Ron
Ron & Vaidy,
SQL Server 2000 allowed veiws to be sorted with an order by clause at the end of it like “order by dex_row_id”. However, when SQL Server 2005 came out a view could no longer be ordered by defaults unless you were grabbing a set of records. By grabbing top 2147483647, which was the max number of results for a view you were allowed to order by and mimic the behavior of SQL Server 2000. This is why we did it.
However, It was not a good way of doing things. By grabbing the top X you force a table scan, which then slows performance. SalesPad no longer uses top x in its latest versions of its product. Instead we do the ordering of code in the core product with C#. Hope this helps.
Sincerely,
Joseph A Alt III
Thank you very much for the insight Joseph.
*Joseph is a developer at Salespad.
La plus ancienne de ces joueurs, Jackson Martinez, est 28.
Dean Ambrose a commencé le match contre le Stardust, et l’ancien a réussi à obtenir une victoire rapide.