help retrieve data that is being excluded

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 2000 Forums

Transact-SQL (2000)

help retrieve data that is being excluded

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2008-01-22 : 18:05:34

What is happing right now is that one of the items (1207504) is
being excluded even though the data is different in column
5. There should be two rows with item (1207504).
Any help would be appreciated.

The results I would like to see:

Columns

1 |    2     |  3   |  4    |  5   |  6
------------------------------------------
1 | 1208172  | NULL | NULL  | NULL | NULL
2 | .1255555 | nut  | B3847 | 0002 | 1.000
2 | .1288888 | scr  | C9836 | 0003 | 1.000
3 | ..1299999| tbg  | T3854 | 0001 | 1.000
2 | .1207504 | tbg  | A1234 | 0032 | 1.000
2 | .1207504 | tbg  | A1234 | 0040 | 1.000


SET NOCOUNT ON

CREATE TABLE #temp (PINBR char(20), CINBR char(20), ITDSC varchar(30), ENGNO varchar(15), USRS1 nvarchar(4), 
	QTYPR decimal(6,3))
INSERT INTO #temp VALUES ('1208172', '1207504', 'tbg', 'A1234', '0032', '1')
INSERT INTO #temp VALUES ('1208172', '1207504', 'tbg', 'A1234', '0040', '1')
INSERT INTO #temp VALUES ('1208172', '1255555', 'nut', 'B3847', '0002', '1')
INSERT INTO #temp VALUES ('1208172', '1288888', 'scr', 'C9836', '0003', '1')
INSERT INTO #temp VALUES ('1288888', '1299999', 'tbg', 'T3854', '0001', '1')

DECLARE @current char(20)
SET @current = '1208172'
DECLARE @level int, @line char(20), @itdsc varchar(30), @engno varchar(15), @usrs1 nvarchar(4), @qtypr decimal(6,3)
CREATE TABLE #stack (item char(20), level int, ITDSC varchar(30), ENGNO varchar(15), USRS1 nvarchar(4), 
	QTYPR decimal(6,3))
INSERT INTO #stack VALUES (@current, 1, @itdsc, @engno, @usrs1, @qtypr)
SELECT @level = 1
WHILE @level >= 0
BEGIN
   IF EXISTS (SELECT * FROM #stack WHERE level = @level)
      BEGIN
         SELECT @level = @level, @current = item, @itdsc = ITDSC, @engno = ENGNO, @usrs1 = USRS1, @qtypr = QTYPR
         FROM #stack
         WHERE level = @level
         SELECT @line = space(@level - 1) + @current
         SELECT @level, REPLACE(@line, ' ', '.'), @itdsc, @engno, @usrs1, @qtypr
         DELETE FROM #stack
         WHERE level = @level AND item = @current
	 INSERT INTO #stack
	 SELECT CINBR, @level + 1, ITDSC, ENGNO, USRS1, QTYPR
	 FROM #temp
	 WHERE (PINBR = @current)
	 ORDER BY USRS1 DESC
         IF @@ROWCOUNT > 0
            SELECT @level = @level + 1
      END
   ELSE
      SELECT @level = @level - 1
END
DROP TABLE #stack
DROP TABLE #temp
RETURN
GO

Thanks,
Jose

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-22 : 18:32:05

Your problem is here:
DELETE FROM #stack
WHERE level = @level AND item = @current
You are deleting both records since both records have the same value in level and item.

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2008-01-23 : 11:03:44

I noticed that too but tried to change it to this:

DELETE FROM #stack
WHERE level = @level AND item = @current AND usrs1 = @usrs1

and it would then be stuck in some loop. Any other ideas on how I can go about fixing this?
Thanks.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-23 : 17:57:14

[code]SET NOCOUNT ON

CREATE TABLE #temp (PINBR char(20), CINBR char(20), ITDSC varchar(30), ENGNO varchar(15), USRS1 nvarchar(4),
QTYPR decimal(6,3))
INSERT INTO #temp VALUES ('1208172', '1207504', 'tbg', 'A1234', '32', '1')
INSERT INTO #temp VALUES ('1208172', '1207504', 'tbg', 'A1234', '40', '1')
INSERT INTO #temp VALUES ('1208172', '1255555', 'nut', 'B3847', '02', '1')
INSERT INTO #temp VALUES ('1208172', '1288888', 'scr', 'C9836', '03', '1')

DECLARE @current char(20)
SET @current = '1208172'
DECLARE @level int, @line char(20), @itdsc varchar(30), @engno varchar(15), @usrs1 nvarchar(4), @qtypr decimal(6,3)
CREATE TABLE #stack (item char(20), level int, ITDSC varchar(30), ENGNO varchar(15), USRS1 nvarchar(4),
QTYPR decimal(6,3))
INSERT INTO #stack VALUES (@current, 1, @itdsc, @engno, @usrs1, @qtypr)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @level = @level, @current = item, @itdsc = ITDSC, @engno = ENGNO, @usrs1 = USRS1, @qtypr = QTYPR
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
SELECT @level, REPLACE(@line, ' ', '.'), @itdsc, @engno, @usrs1, @qtypr
DELETE FROM #stack
WHERE level = @level AND item = @current
INSERT INTO #stack
SELECT CINBR, @level + 1, ITDSC, ENGNO, USRS1, QTYPR
FROM #temp
WHERE (PINBR = @current)
ORDER BY USRS1 DESC
END

SELECT @level = @level - 1
END
DROP TABLE #stack
DROP TABLE #temp
RETURN
GO[/code]

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2008-01-24 : 11:30:11

Thanks, but it doesn't give me the exact results I'm looking for as mentioned in my first post.
I also needed the '.' shown in the front of the item numbers to indicate what level in the
BOM (Build Of Material) that item is in. I'm going to add another INSERT so that the level of
this one item is two levels deep. I will show this in the example.

Jose

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2008-02-07 : 11:44:06

Still trying to figure this out. Anyone else have any ideas?

Jose

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-07 : 15:36:29

Took another look at it and it looks like the problem was in the DELETE FROM #stack WHERE clause. Try the following out and see if this doesnt give the desired output:

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#temp') IS NOT NULL 
    DROP TABLE #temp
CREATE TABLE #temp
    (
      PINBR char(20),
      CINBR char(20),
      ITDSC varchar(30),
      ENGNO varchar(15),
      USRS1 nvarchar(4),
      QTYPR decimal(6, 3)
    )
INSERT INTO #temp VALUES ('1208172', '1207504', 'tbg', 'A1234', '0032', '1')
INSERT INTO #temp VALUES ('1208172', '1207504', 'tbg', 'A1234', '0040', '1')
INSERT INTO #temp VALUES ('1208172', '1255555', 'nut', 'B3847', '0002', '1')
INSERT INTO #temp VALUES ('1208172', '1288888', 'scr', 'C9836', '0003', '1')
INSERT INTO #temp VALUES ('1288888', '1299999', 'tbg', 'T3854', '0001', '1')

DECLARE @current char(20)
SET @current = '1208172'

DECLARE @level int,
    @line char(20),
    @itdsc varchar(30),
    @engno varchar(15),
    @usrs1 nvarchar(4),
    @qtypr decimal(6, 3)

SELECT  @level = 1

IF OBJECT_ID('tempdb..#stack') IS NOT NULL 
    DROP TABLE #stack
CREATE TABLE #stack
    (
      item char(20),
      level int,
      ITDSC varchar(30),
      ENGNO varchar(15),
      USRS1 nvarchar(4),
      QTYPR decimal(6, 3)
    )
INSERT  INTO #stack
VALUES  (
          @current,
          1,
          @itdsc,
          @engno,
          @usrs1,
          @qtypr
        )

IF OBJECT_ID('tempdb..#out') IS NOT NULL 
    DROP TABLE #out
CREATE TABLE #out
    (
      level int,
      line char(20),
      itdsc varchar(30),
      engno varchar(15),
      usrs1 nvarchar(4),
      qtypr decimal(6, 3)
    )

WHILE @level >= 0
    BEGIN
        IF EXISTS ( SELECT  *
                    FROM    #stack
                    WHERE   level = @level ) 
            BEGIN
                SELECT  @level = @level,
                        @current = item,
                        @itdsc = ITDSC,
                        @engno = ENGNO,
                        @usrs1 = USRS1,
                        @qtypr = QTYPR
                FROM    #stack
                WHERE   level = @level

                SELECT  @line = space(@level - 1) + @current

                INSERT  #out
                        SELECT  @level,
                                REPLACE(@line, ' ', '.'),
                                @itdsc,
                                @engno,
                                @usrs1,
                                @qtypr

                DELETE  FROM #stack
                WHERE   level = @level
                        AND item = @current
                        AND (ITDSC = @itdsc OR ITDSC IS NULL)
                        AND (ENGNO = @engno OR ENGNO IS NULL)
                        AND (USRS1 = @usrs1 OR USRS1 IS NULL)
                        AND (QTYPR = @qtypr OR QTYPR IS NULL)

                INSERT  INTO #stack
                        SELECT  CINBR,
                                @level + 1,
                                ITDSC,
                                ENGNO,
                                USRS1,
                                QTYPR
                        FROM    #temp
                        WHERE   PINBR = @current
                        ORDER BY USRS1 DESC

                IF @@ROWCOUNT > 0 
                    SELECT  @level = @level + 1

            END
        ELSE 
            SELECT  @level = @level - 1

        PRINT @level

    END

SELECT * FROM #out

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2008-02-11 : 11:33:20

Thanks jdaman! That worked great and the results are what I expected.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources