Hello,
I have a query A:
Select DISTINCT SYS.Netbios_Name0 From v_R_System SYS
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
WHERE
COL.CollectionID = 'UCP00020'
==============================================================================================
the result is giving 1,591 items...
then a query B:
Select DISTINCT SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion
From v_GS_SoftwareProduct SP
Join v_R_System SYS on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'
AND COL.CollectionID = 'UCP00020'
==============================================================================================
the result is giving 1,021 items
when I am doing the combined query A & B in a query C
Select DISTINCT SYS.Netbios_Name0 From v_R_System SYS
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
WHERE
COL.CollectionID = 'UCP00020'
AND NOT EXISTS (
Select DISTINCT SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion
From v_GS_SoftwareProduct SP
Join v_R_System SYS on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'
AND COL.CollectionID = 'UCP00020')
==============================================================================================
the result is 0 item ... what is wrong... 1,591-1,021= 570!!! ??
I tried as well
Select DISTINCT SYS.Netbios_Name0 From v_R_System SYS
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
WHERE
COL.CollectionID = 'UCP00020'
AND NOT EXISTS (
Select Distinct SYS.Netbios_Name0 from v_R_System SYS
join v_GS_SoftwareProduct SP on SP.ResourceID = SYS.ResourceID
Join v_FullCollectionMembership COL on COL.ResourceID = SYS.ResourceID
Where SP.ProductName Like 'Cisco AMP%'
AND COL.CollectionID = 'UCP00020')
==============================================================================================
still 0 item when I use NOT EXISTS selection....!!! Why ?
I tried NVL function but it says "NVL is not recognized as a built-in function name"!!!
Thanks,
Dom