Problem with numbering rows and repeating values.

18 May 2006 9:04 PM

rhaazy

I AM USING MS SQL 2000

INSERT INTO #temp
    SELECT * FROM openxml(@iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
            WITH(
                ID nvarchar(50) './@ID',
                ParentID nvarchar(50) './@ParentID',
                Name nvarchar(50) './@Name',
                scanattribute nvarchar(50) '.'
                )

    INSERT INTO #dup
    SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
tblScanAttribute, #temp
    WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'

    INSERT INTO tblTest3(instance, attid, sectionid, name)
    SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
    AND    #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
    FROM tblScanAttribute, #temp
    WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'

The Results of tblTest3 are:

2    151    18    5/4/2006 1:11 PM
9    151    18    5/4/2006 12:57 PM
10    151    18    5/4/2006 12:59 PM
1    151    18    5/4/2006 1:04 PM
6    151    18    5/4/2006 12:06 PM
7    151    18    5/4/2006 12:15 PM
8    151    18    5/4/2006 12:36 PM
3    151    18    5/4/2006 11:42 AM
4    151    18    5/4/2006 11:48 AM
5    151    18    5/4/2006 12:05 PM
5    152    18    Installed ClientScanServiceSetup
10    152    18    Removed ClientScanServiceSetup
5    152    18    Installed ClientScanServiceSetup
10    152    18    Removed ClientScanServiceSetup
5    152    18    Installed ClientScanServiceSetup
10    152    18    Removed ClientScanServiceSetup
5    152    18    Installed ClientScanServiceSetup
10    152    18    Removed ClientScanServiceSetup
5    152    18    Installed ClientScanServiceSetup
10    152    18    Removed ClientScanServiceSetup

As you can see the 'instance' column does what it is suppose to for
attid 151 because there are no repeating values in the colmn 'name' but
for attid 152 because there are repeating values it does not work
correctly. I'm not sure if the problem is in my select statment that
assigns a value to instance or if I will have to restructure this whole
mess. Someone Please help me.

18 May 2006 9:57 PM

ML

Unless you post proper DLL, sample data and expected results, any attempt at
helping you is impossible.

ML

---
http://milambda.blogspot.com/