Hi Garth,
How are you?
Thanks a lot!
I did manage to write a correct piece of code....
select count(distinct a.resourceid)as [All Internet Systems] from v_r_system
as a inner join v_RA_System_SMSAssignedSites as b on
a.ResourceID=b.resourceID
inner join v_RA_System_IPSubnets as c on a.ResourceID=c.resourceID where
a.Obsolete0=0 and (b.SMS_Assigned_sites0='MIM' or a.Name0 like 'ttt%' or
a.Name0 like 'mtt%'
or c.IP_subnets0 like '172.16%')
it returns me 709 records!! :-)
JJ
"Garth" wrote:
> The queries are not the same, hence this is why you are getting
> different results. For example notice that within SQL query you are
> telling it to count all IP address and you are not doing the same within
> WQL.
>
> This query will show you the duplicates. (Look at the IP field)
>
> select
> *
> from
> v_r_system as a
> inner join v_RA_System_SMSAssignedSites as b on
> a.ResourceID=b.resourceID
> inner join v_RA_System_IPAddresses as c on a.ResourceID=c.resourceID
> where
> a.Obsolete0=0
> and (b.SMS_Assigned_sites0='MIM'
> or a.Name0 like 'ttt%'
> or a.Name0 like 'mtt%'
> or c.IP_addresses0 like '172.16%')
> ORDER BY
> a.ResourceID
>
>
>
> "Jaison Jose" <JaisonJose@discussions.microsoft.com> wrote in message
> news:2FCEDB97-1981-40AC-8B26-404D49B05EE8@microsoft.com:
>
> > Hi All,
> >
> > I have a collection created with the following code as a criteria.
> >
> > select
> > SMS_R_System.ResourceID,SMS_R_System.ResourceType, SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier ,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_Syst em.Client
> > from SMS_R_System where SMSAssignedSites = "MIM" or Name like "ttt%" or Name
> > like "mtt%" or IPSubnets like "172.16.%" order by Name
> >
> > If i do a 'show count' i get,
> >
> > Total No of resources in collection=760
> > Total number of clients in collection= 709
> > Total no of non-obsolete clients in collection=709
> >
> >
> > I have written a very similar SQL code in Query Analyzer, it vl look like
> > this:
> >
> > select count(*) from v_r_system as a inner join v_RA_System_SMSAssignedSites
> > as b on a.ResourceID=b.resourceID
> > inner join v_RA_System_IPAddresses as c on a.ResourceID=c.resourceID where
> > a.Obsolete0=0 and (b.SMS_Assigned_sites0='MIM' or a.Name0 like 'ttt%'
> > or a.Name0 like 'mtt%' or c.IP_addresses0 like '172.16%')
> >
> > This does return a count of 763.
> >
> > If you check this code, you can see that i have already given the condition
> > to avoid obsoletes. Then i should get the same count as my collection, which
> > should be 709 right? but i still get 763!!
> >
> > Is there any tuning can be done in my SQL code so that i get the same count
> > of non-obsolete machines as my collection??
> >
> >
> > Appreciate any help.
> >
> > Many thanks guys
> >
> > Jaison
>
>
> --
>
> Garth Jones
> Chief Architect
>
> www.Enhansoft.com
> Enhancing Your Business
>
>
>