January 7, 2011 at 8:51 am
I know I should be able to figure this out, but I am drawing a complete blank. Can someone help me on this? MY intention is to pull all items from the ItemMaster table. Thanks.
DECLARE @Item VARCHAR (30)
SET @Item = (SELECT item from ItemMaster)
SELECT i.item,
i.description,
i.u_m,
i.product_code,
i.unit_cost,
w.whse,
w.qty_on_hand
FROM ItemMaster i
INNER JOIN itemwhse w ON i.item = w.item
WHERE p_m_t_code = 'P'
AND i.item = @Item
January 7, 2011 at 9:16 am
if you want to return all the data then why do you need to use a variable and where clause?
try
SELECT i.item,
i.description,
i.u_m,
i.product_code,
i.unit_cost,
w.whse,
w.qty_on_hand
FROM ItemMaster i
INNER JOIN itemwhse w ON i.item = w.item
WHERE p_m_t_code = 'P'
January 7, 2011 at 9:24 am
This is part of a Stored Procedure. I am just working on the SELECT statement separately right now. Sorry, I forgot to clarify that.
January 7, 2011 at 9:29 am
the error you are getting is relating to the set statement, there must be more than one row in the ItemMaster table, and you are trying to use a set statement to set the varaible to multiple rows which will not work.
But from looking at the code it does not make logical sense unless there is only one row in ItemMaster
January 7, 2011 at 9:30 am
So what is not working? You haven't provided a reason why this doesn't work, or what you want to do. If you want to return all items from the table, then you would use no WHERE clause, or if it's all of a certain item, then it's using WHERE ItemID = @ItemID
We don't know what the relationship is here, or what data there is. It is unclear why you join to the itemwhse table if you want to pull everything from ItemMaster.
January 7, 2011 at 9:36 am
I am trying to pull all records from the ItemMaster table. There are about 38,000 records. I am also trying to pass a variable so I can use this in a report. I want to be able to select either a single item, or pull the entire item master, with the entire item master being the default. Here is the code for the Stored Procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Rpt_WeirItemWhseSp
(@Item VARCHAR(30))
AS
BEGIN TRANSACTION
SET @Item = (SELECT item from ItemMaster)
SELECT i.item,
i.description,
i.u_m,
i.product_code,
i.unit_cost,
w.whse,
w.qty_on_hand
FROM ItemMaster i
INNER JOIN itemwhse w ON i.item = w.item
WHERE p_m_t_code = 'P'
COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
January 7, 2011 at 9:50 am
what is the purpose of this line in the code
SET @Item = (SELECT item from ItemMaster)
what would you expect @item to be populated with if there are 38,000 rows in the table?
January 7, 2011 at 9:57 am
You also still haven't explained what doesn't work or the join. What is the point there? I assume that you are trying to get child records, but is there a problem with a 1:1 or 1:n join? You are writing this as if we have any knowledge whatsoever of your data and business. We don't, so it is not clear what the purpose is. Also, you have no need of a transaction.
If you are trying to pull from ItemMaster then
create procedure MyProc
@id int = null
as
if @id is null
SELECT i.item,
i.description,
i.u_m,
i.product_code,
i.unit_cost,
w.whse,
w.qty_on_hand
FROM ItemMaster i
INNER JOIN itemwhse w
ON i.item = w.item
WHERE p_m_t_code = 'P'
else
SELECT i.item,
i.description,
i.u_m,
i.product_code,
i.unit_cost,
w.whse,
w.qty_on_hand
FROM ItemMaster i
INNER JOIN itemwhse w
ON i.item = w.item
WHERE p_m_t_code = 'P'
and i.Item = @id
January 7, 2011 at 10:00 am
Maybe I am going about this all wrong. What I am expecting to pull from this SET statement is all of the items int he item master. Then, in the report, there is a pull down menu that allows you to select only one item if you wish. As expected, I get the "Subquery returned more than 1 value" error.
Is there a better way to construct this query? I appreciate your help on this.
Thanks.
January 7, 2011 at 10:06 am
Mr. Jones, you hit it on the head! That worked. Thank you very much.
I apologize for being vague initially. In the future I will provide more information so that you don't have to guess.
Thanks again for both lessons.
January 7, 2011 at 10:18 am
You are welcome.
This might help you for future questions: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 7, 2011 at 10:27 am
Steve,
Thanks for the link. I have bookmarked it and will reference it before my next post.
One more silly question: How do you create the blue boxes that you use to put your code in?
Steve
January 7, 2011 at 10:30 am
It's on the left of the box when you type your post (see attachement).
January 7, 2011 at 10:40 am
Got it. Thanks, Ninja's_RGR'us.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply