• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

table-valued function explode qty to rows

I have a table of product records which includes a field with the qty on hand.  I need to query that table returning as many rows for each product record as the value in the qty on hand field.

What is the best way to 'explode' the rows based upon the value in the qty field?

I have pasted the code from my attempt to use a table-value function; that does not work.

thank you.

CREATE FUNCTION [dbo].[rowsFromValue](@qty int)
RETURNS @retArray TABLE (idx smallint Primary Key)
AS
BEGIN
DECLARE @idx smallint
IF @qty = null
	BEGIN
	SET @qty = 0
	END
SET @idx = 0
	WHILE @qty > 0
		BEGIN
			SET @idx = @idx + 1
			INSERT @retArray (idx)
			VALUES (@idx)
			SET @qty = @qty - 1
		END
RETURN
END
 
GO
 
 
CREATE TABLE [PRODUCT](
	[UPC] [varchar](14) NULL,
	[ONHAND_QTY] [int] NULL)
 
SELECT
UPC
,PM_TITLE
,IDX
FROM PRODUCT AS P
CROSS APPLY [Cognos].[dbo].[rowsFromValue](ONHAND_QTY) AS R

Open in new window

0
pmdadmin
Asked:
pmdadmin
  • 2
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
Try it this way:
CREATE FUNCTION dbo.rowsFromValue(
                              @qty int
                        )

RETURNS @retArray TABLE (idx smallint Primary Key)

AS

BEGIN

DECLARE @idx smallint
IF @qty Is Not Null
      SET @idx = 0
      WHILE @idx < @qty
            BEGIN
                  SET @idx = @idx + 1
                  INSERT @retArray (idx)
                  VALUES (@idx)
            END
RETURN
END

Incidentally, you would be much better off using the SQL Server 2005 ROW_NUMBER() function here.
0
 
pmdadminAuthor Commented:
Yes, your version is much cleaner.  However, the challenge is then using the function.  How do I use the function in my select statement passing the ONHAND_QTY as the parameter.  I still receive this strange error:
"ONHAND_QTY" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

I am already running SQL server 2005.

shouldn't I be able to pass in a field value?

Or, is there another way to retrieve multiple rows, in effect exploding the quantity into rows?

Much appreciated.  Thank you.
0
 
pmdadminAuthor Commented:
I should add that this is the desired result:

Given product record:  UPC, TITLE, ONHAND_QTY
999999999999  |  My Product Title  |  5

Expected resulting records:
999999999999  |  My Product Title  |  1
999999999999  |  My Product Title  |  1
999999999999  |  My Product Title  |  1
999999999999  |  My Product Title  |  1
999999999999  |  My Product Title  |  1


0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
SharathData EngineerCommented:
check this

CREATE FUNCTION dbo.rowsFromValue(@qty int)
RETURNS @retArray TABLE (idx smallint Primary Key,RET int default 1)
AS
BEGIN
DECLARE @idx smallint
IF @qty Is Not Null
      SET @idx = 0
      WHILE @idx < @qty
            BEGIN
                  SET @idx = @idx + 1
                  INSERT @retArray (idx) 
                  VALUES (@idx)
            END
RETURN
END
GO
 
CREATE TABLE [PRODUCT](
	[UPC] [varchar](14) NULL,
	PM_TITLE varchar(100),
	[ONHAND_QTY] [int] NULL)
 
INSERT INTO [PRODUCT] VALUES ('999999999999','My Product Title',5)
GO
 
SELECT UPC
       ,PM_TITLE
       ,RET
  FROM PRODUCT AS P
 CROSS APPLY [dbo].[rowsFromValue](ONHAND_QTY) AS R
GO

Open in new window

0
 
SharathData EngineerCommented:
check the result
SELECT UPC,PM_TITLE,ONHAND_QTY FROM PRODUCT
 
UPC	         PM_TITLE	        ONHAND_QTY
999999999999	My Product Title	5
 
SELECT UPC
       ,PM_TITLE
       ,RET
  FROM PRODUCT AS P
 CROSS APPLY [dbo].[rowsFromValue](ONHAND_QTY) AS R
 
UPC	         PM_TITLE	        RET
999999999999	My Product Title	1
999999999999	My Product Title	1
999999999999	My Product Title	1
999999999999	My Product Title	1
999999999999	My Product Title	1

Open in new window

0
 
Anthony PerkinsCommented:
>>How do I use the function in my select statement passing the ONHAND_QTY as the parameter. <<
You cannot do that, that way.  See if the other solutions help you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now