Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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!
Reply With Quote
  #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.
Reply With Quote
  #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.
Reply With Quote
  #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).
Reply With Quote
  #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>
Reply With Quote
  #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!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:46 PM.


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