385
I need to create sql view which include the following data.
1) Item No.
2) Item Description
3) Price
4) Vendor Name
5) Last Vendor Name
Can anyhave have this view in sql.
Thanks.
Jozy.
Hi Jozy,
Joseph provided you with the best steps to get the data you are looking for.. Unfortunately the very nature of forums limits the kind of help that can be provided.. We don't know the type of access you have to your SQL server, as your OP mentions a SQL View... but not how you going to create it and who is going to create it.
Providing access to SQL with SSMS (SQL STudio Management) bears some risks and liability that no one is going to take here by providing SQL code without knowing more on your setup.
Victoria Yudin's web site shares tons of scripts with table joins sample. The link Deanne provided already points to a SQL view with JOINS.. if you don't know how to customize the code to get it tailored to your need, you may want to start a T-SQL 101 course first..
You can either customize Victoria's Inventory view by adding the missing price table, or you can create the view "as is" and join the missing table to the view afterwards.
HTH
Hi Jozy
There is a built-in view in your GP company database called ItemQuantities. This will give you all of the information you need except for price. That can be a join with the IV00108 table.
In that view:
Assigned vendor: Vendor Name
Last vendor name: Last ORD Vendor
Last purchase cost: Last RCPT Cost
Last ORD Vendor is only the vendor ID, so you'd also have to do a join with PM00200 to get the full vendor name.
One more thing to point out: this shows the overall records for each inventory item (all of the data of each site rolled up to a master record) and also each inventory site you have with the details per site.
I hope this helps. Let me know if you need more information!
Thanks.
Joe
#1
André Arnaud de Cal...
291,711
Super User 2024 Season 2
#2
Martin Dráb
230,458
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
