Can this sp be done without a Loop? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can this sp be done without a Loop?

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2007-11-20 : 13:01:23

Hello:

I have a stored procedure to write that I can only think of doing using a loop. Here is the problem

I have an order & orderdetail tables. The report consists of the order & the details plus additional lines with underscores on them i.e, '________' in groups of 10 per line which will be associated with each detail line on the report. The user will write amounts into these underscores for each detail where they appear. (see example at end)

In order to do this I have created another table, tblLines, which will hold the detail ID and an 80 varchar field which can hold 10 underscores each. This can also be a temp table in the stored proc I think.

The number of lines of underscores depends upon a field amount in each detail record. This amount (boxes) divided by 10 and rounded upwards gives the number of lines. So if a detail record has 17 in the boxes field, 2 lines with underscores will appear under that detail record in the report. (the user will use these underscores to put weights for each box).

So in the example above the tblLines will have 2 records for that detail record and so it goes.
The only way I thought to do this in a stored proc was to loop thru every detail record with the orderID of the order and figure out the number of lines needed for the report for that detail record and then to do an insert into the tblLines of the number of lines needed.

Then I can do a query on the order and outer join the tblLines to the detail table to create the query for an order. (the stored procedure I create will be called by vb.net and return a dataset for the report).

Is there any way to do this with without the loop?

Eventually I am going to have to do this for more than one order but that is another question for another day.
Thanks
smHaig
-------------------------Sample Report

Order1 -- Organic turkeys

Detail 1 17 boxes $20.00 John Foods
___ ___ ___ ___ ___ ___ ___ ___ ___ ___
___ ___ ___ ___ ___ ___ ___ ___ ___ ___

Detail 2 5 boxes $5.00 Larry Foods
___ ___ ___ ___ ___ ___ ___ ___ ___ ___

Detail 3 0 Boxes $2.00 Harry Foods

Detail 4 10 Boxes $8.00 Mary Foods
___ ___ ___ ___ ___ ___ ___ ___ ___ ___
___ ___ ___ ___ ___ ___ ___ ___ ___ ___

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-20 : 16:33:35

This might (or might not) help you get started.

DECLARE @Line TABLE(L1 CHAR(10), L2 CHAR(10), L3 CHAR(10))
DECLARE @Detail TABLE (ID INT, Amount INT, Revenue MONEY)

INSERT @Line 
SELECT '__________', '__________', '__________'

INSERT @Detail
SELECT 1, 17, $5.00
UNION ALL SELECT 2, 5, $12.89
UNION ALL SELECT 3, 0, $0

SELECT
	ID,
	Amount,
	Revenue
FROM
(
	SELECT
		ID AS JoinID,
		CAST(ID AS VARCHAR) AS ID,
		CAST(Amount AS VARCHAR) AS Amount,
		CAST(Revenue AS VARCHAR) AS Revenue
	FROM
		@Detail

	UNION ALL

	SELECT 
		ID AS JoinID,
		L1,
		L2,
		L3
	FROM
		(
		SELECT 
			ID,
			CASE WHEN Amount > 0 THEN (Amount / 10) + 1 ELSE 0 END AS LineCount
		FROM 
			@Detail
		) AS Line
	FULL OUTER JOIN
		(
			-- Using an inline Numbers table
			SELECT T1.N1 + T2.N2 AS Number 
			FROM
				(
					SELECT 0 AS N1
					UNION ALL SELECT 1 
					UNION ALL SELECT 2 
					UNION ALL SELECT 3
					UNION ALL SELECT 4
					UNION ALL SELECT 5
					UNION ALL SELECT 6
					UNION ALL SELECT 7
					UNION ALL SELECT 8
					UNION ALL SELECT 9
					UNION ALL SELECT 10
					UNION ALL SELECT 11
					UNION ALL SELECT 12
					UNION ALL SELECT 13
					UNION ALL SELECT 14
					UNION ALL SELECT 15
				) T1
			CROSS JOIN
				(	
					SELECT 0 AS N2
					UNION ALL SELECT 16
					UNION ALL SELECT 32
					UNION ALL SELECT 64
				) T2
			WHERE T1.N1 + T2.N2 > 0
		) AS Num
		ON Num.Number <= Line.LineCount
	LEFT OUTER JOIN
		@Line
		ON 1 = 1
	WHERE
		Num.Number > 0
		AND Line.ID IS NOT NULL
) AS Temp
ORDER BY JoinID, ID DESC

smh
Yak Posting Veteran

94 Posts

Posted - 2007-11-20 : 17:35:00

I am in awe of this solution. But I am lost half way thru. I see that you put all the values of the detail table into the line table's varchar fields -- in order to have one table I presume; and I see you calculating the number of lines that would be needed. However, there the trail ends for me.

If you could explain from:

FULL OUTER JOIN
(
-- Using an inline Numbers table
SELECT T1.N1 + T2.N2 AS Number
FROM
(
SELECT 0 AS N1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14
UNION ALL SELECT 15
) T1
CROSS JOIN
(
SELECT 0 AS N2
UNION ALL SELECT 16
UNION ALL SELECT 32
UNION ALL SELECT 64
) T2
WHERE T1.N1 + T2.N2 > 0
) AS Num
ON Num.Number <= Line.LineCount
LEFT OUTER JOIN
@Line
ON 1 = 1
WHERE
Num.Number > 0
AND Line.ID IS NOT NULL
)

Also, I am not sure what would happen if the number of detail fields are greater than 10 (which it is) which is the max number of underscores per line. You are using 3 columns for each I see.

However, in actuality the format of the report is not necessary to worry about. You are clearly able to create the report from start to finish!! I am only barely able to get the data into a dataset for Crystal Reports to worry about that.

The sample I used was only to show what the underscores are suppose to do. But there will be many more detail fields that will appear in more than one detail line on the report; plus lots of order table info for the heading. Thats why I thought that keeping the lines in a temp table and then doing a outer join would work.

It would create a large dataset since every row with a line would carry all the order & detail info, but as far as report layout, Crystal could handle that. And it would allow me to place the fields for the detail table on more than one detail line of the report.

The underscore lines would be in a second set of details lines (Crystal allows one to create sections within the detail part of the report). Then if there were no underscore lines for that order detail, that second detail section would just not print and there would be no empty lines

Perhaps the part of your query that is beyond my abilities gives a clue on how I could modify it, but alas, I need help on understanding it.

Thanks again
smHaig

The other que

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-20 : 18:43:34

This number table is jsut that, I table with numbers in it. A lot of databases have a talbe with numbers so you can join and do stuff like. You can run the SQL and see that it just returns a list of numbers if you like:

			-- Using an inline Numbers table
			SELECT T1.N1 + T2.N2 AS Number 
			FROM
				(
					SELECT 0 AS N1
					UNION ALL SELECT 1 
					UNION ALL SELECT 2 
					UNION ALL SELECT 3
					UNION ALL SELECT 4
					UNION ALL SELECT 5
					UNION ALL SELECT 6
					UNION ALL SELECT 7
					UNION ALL SELECT 8
					UNION ALL SELECT 9
					UNION ALL SELECT 10
					UNION ALL SELECT 11
					UNION ALL SELECT 12
					UNION ALL SELECT 13
					UNION ALL SELECT 14
					UNION ALL SELECT 15
				) T1
			CROSS JOIN
				(	
					SELECT 0 AS N2
					UNION ALL SELECT 16
					UNION ALL SELECT 32
					UNION ALL SELECT 64
				) T2

What this is doing is getting the Detail table to get the number of Lines required (Amount / 10). It then FULL OUTER JOINS to the numbers table to get "Amount / 10" number of rows. Actually, now that I look at it, you sould be able to LEFT OUTER JOIN instead of the FULL.

	SELECT 
		ID AS JoinID, -- Use this ID to group (order by) up the Detail row with the Line rows.
		L1,
		L2,
		L3
	FROM
		(
		SELECT 
			ID,
			CASE WHEN Amount > 0 THEN (Amount / 10) + 1 ELSE 0 END AS LineCount
		FROM 
			@Detail
		) AS Line
	FULL OUTER JOIN
		dbo.Numbers AS Num -- Just think of all that code as a numbers table
		ON Num.Number <= Line.LineCount -- Get a row for each Line as determined buy the Amount / 10
	LEFT OUTER JOIN
		-- Join to the Line table to get a row for each line
		@Line
		ON 1 = 1
	WHERE
		Num.Number > 0
		AND Line.ID IS NOT NULL

If you are going to have a dynamic number of columns that you need to generate Lines for, then you'd probably have to use some dynamic sql. If not, then you can simply extend the columns of the Line table and the derived tables in the query to achive this.

Try taking bits of the query and runing them separately and slowly building them together. It is a big chuck of SQL to digest, but if you break it apart, I bet you'll get what is happening. :)

smh
Yak Posting Veteran

94 Posts

Posted - 2007-11-20 : 19:08:24

Hi:

Yes I did discover the table of numbers after I wrote the post. Then I started looking at the post on Venn diagrams & joins -- from a post on how procedural programmers can't think in sets which is why they use loops and can't understand queries like the one I am asking about.

No, It is not dynamic columns that I will have. It is a dynamic number of lines of underscores, that is all.

I need to study the remainder of the query, but I also have a question relating to using the same colums for data and underscores.

If I put the ID and the Amount and the Revenue vertically on the report (using three lines of the report for these fields to display), then on the report there will be three fields one on each line.

But since the underines are also in the columns named ID, Amount and Revenue, they will not appear Horizontally as they should since they are a line of underscores, but they too will appear vertically, such as:

1
17
5
_______________
_______________
_______________
_______________
_______________
_______________
2
5
12.89
_______________
_______________
_______________

This is how it would appear in Crystal Reports if I put one fields on each line. Well I won't do it that way, but I would have several fields of the one detail record displayed on several lines. And I think this would screw up the underlines. But then I may just not quite understand how you have created this temp table.

But this is why I keep going back to doing an outer join such that all the detail records will appear and only those underline records that are linked to detail records will appear. And I can format the order detail fields as I wish without worrying about the lines since they will be in other fields not the same fieldnames as the sample.

But I am only thinking about this in the usual manner in which I do thngs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources