Showing posts with label section. Show all posts
Showing posts with label section. Show all posts

Monday, March 19, 2012

Crazy Row Numbering Poblem

I'm using ms sql 2000.
First here is a sample of my XML input:
- <scan ID="18.0" Section="System Restore">
- <scanattributes>
<scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
Time">5/4/2006 11:42 AM</scanattribute>
<scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
Time">5/4/2006 11:48 AM</scanattribute>
<scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
Time">5/4/2006 12:05 PM</scanattribute>
<scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
Time">5/4/2006 12:06 PM</scanattribute>
<scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
Time">5/4/2006 12:15 PM</scanattribute>
<scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
Time">5/4/2006 12:36 PM</scanattribute>
<scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
Time">5/4/2006 12:57 PM</scanattribute>
<scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
Time">5/4/2006 12:59 PM</scanattribute>
<scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
Time">5/4/2006 1:04 PM</scanattribute>
<scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
Time">5/4/2006 1:11 PM</scanattribute>
</scanattributes>
</scan>
Here is what I have so far:
declare @.iTree int
create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
create table #dup (attid nvarchar(50), name nvarchar (50), ID
nvarchar(50))
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
*/
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 are as follows for tblTest3:
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 I am recording each repeating instace of the second
column, however when the fourth column has a repeating value the method
does not work, this is what I want it to look like:
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
1 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
9 152 18 Installed ClientScanServiceSetup
2 152 18 Removed ClientScanServiceSetup
8 152 18 Installed ClientScanServiceSetup
3 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
7 152 18 Removed ClientScanServiceSetup
6 152 18 Installed ClientScanServiceSetup
4 152 18 Removed ClientScanServiceSetup
IF anyone can help me out with this I would appreciate it greatly.I forgot to include the other tbl where I get the attid from. so here
it is.
151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0|||It's not your main question, but I thought I'd point out that
the ordering of datetimes is probably not what you want:
1:04 PM
1:11 PM
11:42 AM
11:48 AM
...
If you have more than one year, it will get worse.
Steve Kass
Drew University
rhaazy wrote:

>I'm using ms sql 2000.
>First here is a sample of my XML input:
>- <scan ID="18.0" Section="System Restore">
>- <scanattributes>
> <scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
>Time">5/4/2006 11:42 AM</scanattribute>
> <scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
>Time">5/4/2006 11:48 AM</scanattribute>
> <scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
>Time">5/4/2006 12:05 PM</scanattribute>
> <scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
>Time">5/4/2006 12:06 PM</scanattribute>
> <scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
>Time">5/4/2006 12:15 PM</scanattribute>
> <scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
>Time">5/4/2006 12:36 PM</scanattribute>
> <scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
>Time">5/4/2006 12:57 PM</scanattribute>
> <scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
>Time">5/4/2006 12:59 PM</scanattribute>
> <scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
>Time">5/4/2006 1:04 PM</scanattribute>
> <scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
>Time">5/4/2006 1:11 PM</scanattribute>
> </scanattributes>
> </scan>
>Here is what I have so far:
>declare @.iTree int
>create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
>nvarchar(50), scanattribute nvarchar(50))
>create table #dup (attid nvarchar(50), name nvarchar (50), ID
>nvarchar(50))
>EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
>*/
> 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 are as follows for tblTest3:
> 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 I am recording each repeating instace of the second
>column, however when the fourth column has a repeating value the method
>does not work, this is what I want it to look like:
> 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
> 1 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 9 152 18 Installed ClientScanServiceSetup
> 2 152 18 Removed ClientScanServiceSetup
> 8 152 18 Installed ClientScanServiceSetup
> 3 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 7 152 18 Removed ClientScanServiceSetup
> 6 152 18 Installed ClientScanServiceSetup
> 4 152 18 Removed ClientScanServiceSetup
>IF anyone can help me out with this I would appreciate it greatly.
>
>|||rhaazy,
we need another column to break the ties. Try adding an identity column to
the temporary table #temp.
create table #temp (
[ID] nvarchar(50),
ParentID nvarchar(50),
[Name] nvarchar(50),
scanattribute nvarchar(50),
pk int not null identity unique -- new column
)
INSERT INTO #temp ([ID], ParentID, [Name], scanattribute)
SELECT * FROM openxml(...
...
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk < #temp)
)
),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM
tblScanAttribute, #temp
WHERE
#temp.ID like '18.%'
AND tblScanAttribute.Name = #temp.Name
AND tblScanAttribute.ScanSectionID like '18'
AMB
"rhaazy" wrote:

> I forgot to include the other tbl where I get the attid from. so here
> it is.
> 151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
> 152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
>|||NSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk <
#temp)
)
),
Does this imply I also need to add the pk column to the #dup table?|||You should be able to use your ID column to resolve duplicates.
Try changing
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'
to
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND ((#dup.name<#temp.scanattribute)
or (#dup.name=#temp.scanattribute) and
(#dup.ID<=#temp.ID))),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'|||rhaazy,
Sure, but here it will not have identity property. I will be populated from
#temp.
AMB
"rhaazy" wrote:

> NSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT
> instance = (
> select count(*)
> from #dup
> where
> #dup.attid = tblScanAttribute.ScanAttributeID
> AND
> (
> #dup.[name] <= #temp.scanattribute
> or
> (#dup.[name] = #temp.scanattribute and #dup.puk <
> #temp)
> )
> ),
> Does this imply I also need to add the pk column to the #dup table?
>|||I don't know who you are or where you come from but you are the
greatest person who ever lived... I have spent all w trying to
figure this out, I knew it needed an 'or' clause in there but I
couldn't quite get it right. I am an intern and I've only been
database programming for 2 ws and was pretty pleased I got as far as
I did. I just wanted to tell you how thankful I am for you helping me.
The fact you were able to make sense of all the crazy stuff I gave and
crank out exactly what I needed is truely amazing to me.
Thanks again.|||Good catch!!!
"markc600@.hotmail.com" wrote:

> You should be able to use your ID column to resolve duplicates.
> Try changing
> 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'
>
> to
>
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND ((#dup.name<#temp.scanattribute)
> or (#dup.name=#temp.scanattribute) and
> (#dup.ID<=#temp.ID))),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID,
> #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
Posted by glueerts at 7:39 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

Cr9.0

in my pageheader , got country and cityname.
below it, is a section that has district as a group.
below it, is detail section with game field in it.
query:
select country, CityName, district,
case when (population > 200) then game else null end
from country, province, city
group CityName, district
my problem is how to suppress the page header when game is null.You can conditionaly suppress your Page Header (or a PH section).
In the Section Expert, check the Suppress option for your PageHeader, click the 'x+2' button and print such a formula:

Count({table.game},{table.district})=0
Posted by glueerts at 7:21 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)