Re: Indented Bill of Materials

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Robert Schuldenfrei (schuldenfrei_at_comcast.net)
Date: 02/02/05 

Date: Wed, 2 Feb 2005 07:56:09 -0500

Hi Hugo and NG,

Thanks for the quick reply. I read your response and your answers really
helped me. I need to work on something else this morning, but I will try
your suggestions this afternoon or tomorrow. Currently I am using Query
Analyzer to learn these techniques, but eventually I will be calling SQL
Server from a C# application. I am sure I will have some more issues, but
it is back to work for me now. Can you recommend a good basic book for SQL
Server? I currently have SQL Server 2000, A Beginner's Guide by Petkovic.
I also have Trees and Hierarchies in SQL for Smarties by Joe Celko.

Cheers,

Bob 

-- 
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA  02026
bob@s-i-inc.com
781/329-4828
--
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message 
news:f34001tddbcnqk7eqkanuann46inl1ffav@4ax.com...
> 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
    ... 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: How to Create Local Temporary Table
    ... As Daniel pointed out, many times, it is locking that forces one to look at temp tables and Oracle does not share this problem. ... With the improvements in Oracle 9i and 10g and what Oracle has added to its SQL feature set, one can most often get by with just a single SQL statement, which is more efficient than writing data to a table and then reading it back again. ... If it places the group's minds at ease DB2 supports neither VARRAY nor local temps. ...
    (comp.databases.oracle.server)
  • 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: help: Timeout expired. The timeout period elapsed prior to completion of the operation or the se
    ... Actually we are putting records in a temp table which qualify according ... code fails very much even before transaction starts. ... unless really needed pls dont use lock hints as SQL ...
    (microsoft.public.sqlserver.server)