 |
| Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0 |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
August 9th, 2010, 11:40 AM
|
|
Authorized User
|
|
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
Eliminate Duplicate Data
I have a query that pulls server data along with script parameters, and there are lots of script parameters for each server, and it looks like this:
Code:
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_DataIndividual Collect individual processor utilization data? NULL n
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_DataOverall Collect total system utilization data? NULL n
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_DataQueue Collect processor queue data? NULL n
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_EventIndividual Create event if any individual CPU is high? NULL n
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_EventOverall Create event if total system CPU is high? NULL y
server01 NT_CpuLoaded KSG_Policy 7.5.208.0 PRM_EventSMTPServerMissing Create event if SMTP server is not accessible? NULL y
What I'd like to do is pull the server name and policy information just once, and then the unique set of paramters. My query looks like this:
Code:
SQL="select distinct server_name, script_name, KSG_Name, Version, Parameter_Name,
Parameter_Description, Parameter_ValueINT, Parameter_ValueStr, Override
from _JobInfo i
inner Join _JobParameters p on i.UID = p.UID
where server_name like '%server01%'"
Obviously, the distinct isn't going to work because each row does have distinct data, it also happens to have repeating data.
What's the best way to list the repeating data just once, along with the unique data?
Thanks!
|
August 9th, 2010, 02:36 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
Just like you are doing. And then use server side coding/scripting (ASP, ASP.NET, whatever) to produce prettified output.
If you *must* do this from the query, it's possible, with the addition of another field that hopefully/presumably you can simply hide when you do the report.
|
August 9th, 2010, 03:13 PM
|
|
Authorized User
|
|
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
Right, which is what I'm trying to do:
Code:
<tr>
<td width=150><font face="Verdana" size=1>
<%response.write rs("server_name")%>
</td>
<td width=150><font face="Verdana" size=1>
<%response.write rs("ksg_name")%>
</td>
<td width=150><font face="Verdana" size=1>
<%response.write rs("script_name")%>
</td>
<td width=150><font face="Verdana" size=1>
<%response.write rs("version")%>
</td>
<td width=300><font face="Verdana" size=1>
<%response.write rs("parameter_description")%><br>
Integer Value: <%response.write rs("parameter_ValueINT")%><br>
String Value: <%response.write rs("parameter_ValueSTR")%><br>
Override: <%response.write rs("override")%><br>
</td>
</tr>
However, I'm not sure how to get just the unique data.
|
August 9th, 2010, 03:35 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
Code:
<%
SQL="select distinct server_name, KSG_Name, script_name, Version, " _
& " Parameter_Name, Parameter_Description, Parameter_ValueINT, " _
& " Parameter_ValueStr, Override " _
& " from _JobInfo i Inner Join _JobParameters p on i.UID = p.UID " _
& " where server_name like '%server01%' " _
& " ORDER BY server_name, KSG_name "
Set RS = conn.Execute(SQL)
priorServer = ""
priorKSG = ""
Do Until RS.EOF
currentServer = RS("server_name")
currentKSG = RS("ksg_name")
If currentServer = priorServer AND currentKSG = priorKSG Then
currentServer = " "
currentKSG = " "
Else
priorServer = currentServer
priorKSG = currentKSG
End If
%>
<tr>
<td width=150><font face="Verdana" size=1><%=currentServer%></td>
<td width=150><font face="Verdana" size=1><%=currentKSG%></td>
<td width=150><font face="Verdana" size=1>%=rs("script_name")%></td>
<td width=150><font face="Verdana" size=1><%=rs("version")%></td>
<td width=300><font face="Verdana" size=1>
<%=rs("parameter_description")%><br>
Integer Value: <%=rs("parameter_ValueINT")%><br>
String Value: <%=rs("parameter_ValueSTR")%><br>
Override: <%=rs("override")%><br>
</td>
</tr>
<%
rs.MoveNext
Loop
%>
Don't forget the ORDER BY or all bets are off on whether the grouping works or not!
And there's no reason to use <%response.write xxx%>. <%=xxx%> truly is just "shorthand" for that (it's actually converted to response.write by the ASP engine before VBScript sees it).
|
August 9th, 2010, 03:39 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
p.s.: Actually, your HTML there is illegal. All those <font> tags with no matching </font> tags! Wouldn't it be better to use
Code:
<style type="text/css">
tr.info td {
font-family: Verdana, Arial, sans-serif;
font-size: small;
width: 150px;
}
</style>
and then just:
Code:
<tr class="info">
<td><%=currentServer%></td>
<td><%=currentKSG%></td>
<td><%=rs("script_name")%></td>
<td><%=rs("version")%></td>
<td style="width: 300px;">
<%=rs("parameter_description")%><br>
Integer Value: <%=rs("parameter_ValueINT")%><br>
String Value: <%=rs("parameter_ValueSTR")%><br>
Override: <%=rs("override")%><br>
</td>
</tr>
|
September 13th, 2010, 11:54 AM
|
|
Authorized User
|
|
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
As usual, you're right on Old Pendant!
|
|
Similar Threads
