Re: Indented Bill of Materials

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/02/05 

Date: Wed, 02 Feb 2005 01:00:41 +0100

On Tue, 1 Feb 2005 14:19:17 -0500, Robert Schuldenfrei wrote:

(snip)
>am new to SQL Server, so my issues are of the most basic kind. Once I
>really know why this works in detail, I would like to make some improvements
>to better fit my application. I will also need to work efficiently with
>trees if I am ever going to produce enterprise level applications. HOWEVER,
>first I need to learn how this works.

Hi Bob,

Good thinking. I've seen that you posted some specific questions, I'll try
to address them all.

(snip)
>DROP TABLE #temp
>
>SELECT *, --get all of the columns from
>Hi
> 0 as Level, --new column in #Temp.
>What does 0 do?

It's just an extra column in the output of the SELECT statement, that will
hold the constant value 0 for each row. The "AS Level" ensures that this
columns has a name.

> CAST(str(pageid,10) as varchar(8000))
> AS H --create hierarchy string
> INTO #Temp --into temp table
> FROM Hi --source original table
> WHERE ParentPageID is null --get the root of each tree first

The net effect of this total SELECT .. INTO is that all rows from Hi that
have no parent (ParentPageID is null) are inserted in a temp table, all
with Level equal to 0.

>
>SET @lev = 0 --initialize current level to 0
>
>WHILE @@rowcount > 0 --what causes this to <= 0 in order to end?

Check @@rowcount in Books Online. The value of @@rowcount is always the
number of rows affected by the last statement executed. On the first
execution of the WHILE statement, the last executed statement is the SET
statement, so @@rowcount will be 1 and the statements in the WHILE
construction will be executed. On each subsequent evaluation of the
WHIILE, the last executed statement is the INSERT .. SELECT in the loop.
If no row matches the criteria of the SELECT, no row gets inserted,
@@rowcount is 0, the WHILE condition will be false and the script will
continue with the statements after the WHILE loop.
Basically, this is just saying "keep repeating the same INSERT statement
as long as it still affects rows".

> BEGIN
> SET @lev = @lev + 1 --increase current level
> INSERT INTO #Temp --construct rows in #Temp. Error w/new col
> SELECT G.*, --all columns from Hi
> @lev, --current level
> T.H+str(G.pageid,10) --path to this node
> FROM Hi G JOIN #Temp T --this JOIN is the key. How does it work?
> ON G.ParentPageID = T.PageID
> AND T.level = @lev - 1
> END

You are right that this JOIN (or actually, this entire INSERT .. SELECT
statement) is the key.
In the first iteration, @lev will be increased to 1. Then, all rows from
the temp table with Level equal to 0 (T.Level = @lev - 1) are used to find
their "child" rows in Hi (Hi.ParentPageID = #Temp.PageID). The matching
rows from Hi are then added to the temp table, with the Level equal to
@lev (1).
On the next iteration, @lev is increased to 2. Now, the rows in #Temp with
Level 0 are no longer relevant - the rows with Level 1 (just inserted on
the previous iteration) are used to find their children, and to insert
those with Level 2.
This continues - each next iteration finds the children of the rows
inserted on the previous iteration. This will go on until no more children
can be found.

>SELECT space(level*3)+descrip AS bom, --output indented BOM
> PageID
> FROM #temp
> ORDER by H

This is fairly straightforward. The SPACE function is used to indent the
values; the H column is used to get the correct ordering. If you have
trouble working out how H works, include H in the SELECT list and check
the values. I think you'll be able to figure it out.

(snip)
>As you can see the program works. If anyone can clear up my questions in
>the comments I would be very greatful. I tried to add another column called
>qty_per_assembly to the Hi table. I got an error at line 19 of the program
>that is INSERT INTO #Temp. The error was: Insert Error: Column name or
>number supplied values does not match table definition. How does #Temp get
>defined? Well, that is enough for now.

The easy answer first: #Temp is defined in the SELECT .. INTO statement
(which is described in Books Online). This will create a table with the
columns that are in the select list - in this case all columns from Hi (as
a result of the * in the SELECT list), plus the Level and H columns.

I thought your problems were caused by the use of SELECT * both here and
in the INSERT .. SELECT statement in the WHILE loop. However, even though
the use of SELECT * is usually not recommended (I am in fact quite
surprised to see it in code that you apparently got from someone as
knowledgeable as Steve Kass!), this is not the cause of your problem.

I'm not sure what does cause your problem. I copied all your code into QA,
change the USE command to my test database and tested it. Then, I added an
extra column (shortening the name to reduce the typing) and changed
nothing but the final select. It worked without any further modifications.

This is what I added after the inserts but before running the script:

go
ALTER TABLE Hi ADD qty INT DEFAULT 1 WITH VALUES
go

And this is what I changed the final SELECT statement of your script to:

SELECT qty, space(Level*3)+Descrip AS bom, --output indented BOM
   PageID
   FROM #Temp
   ORDER by H

Let me know if you still experience problems if you make the same changes.
And show me how you chenged the code when you wanted to add a column if
you want further help in finding the cause of your error.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Indented Bill of Materials
    ... but eventually I will be calling SQL ... > have no parent are inserted in a temp table, ... > execution of the WHILE statement, the last executed statement is the SET ... > In the first iteration, @lev will be increased to 1. ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance enhancement
    ... speed up execution? ... > sybase, but it should be similar to other DBMS for this to work. ... > say that the adoDataSet is opening a temp table, ...
    (borland.public.delphi.database.ado)
  • Re: Square root
    ... However, in some cases, the use of a square root indicates that the actual thing to be computed is a Pythagorean sum. ... // 1 iteration gives 1 significant digits in the result. ... double temp = errval / guess; ...
    (comp.lang.java.help)
  • Re: What I did yesterday:
    ... I wouldn't recommend leaving on the counter as the temp may drop too ... I follow proper food handling rules. ... The flat was still a bit tough, it could have stayed in a couple ...
    (alt.food.barbecue)
  • Re: any pointers please? combine words script
    ... [snip - don't parse environmental variables yourself] ... >ugly but i hate repeated code like that sub has. ... >never name a temp, temp. ...
    (comp.lang.perl.misc)