Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old August 9th, 2010, 11:40 AM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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!
  #2 (permalink)  
Old 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
Default

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.
  #3 (permalink)  
Old August 9th, 2010, 03:13 PM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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.
  #4 (permalink)  
Old 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
Default

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 = "&nbsp;"
        currentKSG = "&nbsp;"
    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).
  #5 (permalink)  
Old 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
Default

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>
  #6 (permalink)  
Old September 13th, 2010, 11:54 AM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

As usual, you're right on Old Pendant!


Similar Threads
Thread Thread Starter Forum Replies Last Post
Duplicate records while inserting the data ebindia0041 .NET Framework 1.x 0 May 23rd, 2007 07:15 PM
Duplicate Data IN DROPDOWNLIST. prasanta2expert ASP.NET 1.0 and 1.1 Professional 4 November 22nd, 2006 05:36 AM
Crystal Report: Wanted to eliminate space if data avbabu Crystal Reports 2 November 5th, 2004 06:32 AM
eliminating duplicate data erin SQL Language 2 April 22nd, 2004 11:49 AM
Eliminate duplicate entries - sql query nlicata SQL Server 2000 2 September 17th, 2003 10:37 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.