RE: Duplicate entries on reports

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Mick (Mick_at_discussions.microsoft.com)
Date: 07/02/04

  • Next message: Cathy Moya [MS]: "Re: locks on IP subnets in Network Discovery" 
    Date: Fri, 2 Jul 2004 11:49:02 -0700

    Yep, I'm getting duplicate entries as well. Here is the SQL statement for that report,

    SELECT Distinct SYS.Name0, IPSUB.IP_Subnets0, NETW.IPSubnet0,
              IP.IP_Addresses0, NETW.DefaultIPGateway0
    FROM v_RA_System_IPSubnets IPSUB, v_R_System SYS, v_GS_NETWORK_ADAPTER_CONFIGUR NETW, v_RA_System_IPAddresses IP
    WHERE SYS.ResourceID = IPSUB.ResourceID AND NETW.ResourceID = SYS.ResourceID AND
          IP.ResourceID = IPSUB.ResourceID AND IP_Subnets0 LIKE @variable
    Order by SYS.Name0

    The problem is created by the DISTINCT statement in addition to NULLs being present. I'm not sure why NULLs are present, or more accurately, being populated, in the v_GS_NETWORK_ADAPTER_CONFIGUR table, but here is a way to get around the problem. Add "and netw.defaultipgateway0 is not null" anywhere in the WHERE clause. I have changed that report on my site to use the following query:

    SELECT Distinct SYS.Name0, IPSUB.IP_Subnets0, NETW.IPSubnet0,
              IP.IP_Addresses0, NETW.DefaultIPGateway0
    FROM v_RA_System_IPSubnets IPSUB, v_R_System SYS, v_GS_NETWORK_ADAPTER_CONFIGUR NETW, v_RA_System_IPAddresses IP
    WHERE SYS.ResourceID = IPSUB.ResourceID AND NETW.ResourceID = SYS.ResourceID AND
          IP.ResourceID = IPSUB.ResourceID and netw.defaultipgateway0 is not null AND IP_Subnets0 LIKE @variable
    Order by SYS.Name

    HTH

    "Morne" wrote:

    > Hi. Thanks for the reply. The report is the standard report for "all machines in a specific subnet" which is installed by default when rolling out sms 2003.
    >
    > "Mick" wrote:
    >
    > > Need a little more information. Are these custom or canned reports? What report types are you referring to? Some reports should/could produce duplicate entries.
    > >
    > > "Morne" wrote:
    > >
    > > > Hi. Any idea why I would have duplicate entries for machines on the reports and how I can get rid of them.
    > > >
    > > > Thanks,
    > > >
    > > > Morne

  • Next message: Cathy Moya [MS]: "Re: locks on IP subnets in Network Discovery"

    Relevant Pages

    • Re: Storing Queries in Tables
      ... Either you should follow the method Oracle is using to store SQL ... It looks like one of those databases has a multibyte characterset ... User runs a report by running a generic ksh with parameters e.g. name ... the net effect would be to reduce the report SQL statement size by the ...
      (comp.databases.oracle.misc)
    • Re: Reports, Subreports & VBA
      ... Then create a new query, ... Allen Browne - Microsoft MVP. ... >> it should give the right results when the main report calls it. ... >>> In the example of your SQL Statement, ...
      (microsoft.public.access.reports)
    • Re: Reports, Subreports & VBA
      ... In the example of your SQL Statement, ... having the code run the main report or the subreport? ... this only works correctly if you always open the report through ... different fields appear on the user form. ...
      (microsoft.public.access.reports)
    • RE: SQL Qs + Self Contained Report Format Q
      ... I'm experiencing a few problems with the SQL statement below: ... In the report form's Data Environment.Before Open Tables event, I call my criteria form which returns a WHERE clause to be used by an SQL statement. ... Leave report cursor opened. ...
      (microsoft.public.fox.programmer.exchange)
    • Re: Ruby report generation tool
      ... > database and then get them to painless output to basically ... > see in a pure ruby report generation tool. ... one that returns the sql statement itself and one that makes ...
      (comp.lang.ruby)