
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-01-2017 06:52 PM
I'm a bit baffled by the fact that even though I specifically say I do not want to see records with a tblAssetCustom.Custom2 field like %retired%, I still get items with "Retired" in that field.
Here's the report:
That's not the only anomaly with my report, but I believe it's the most telling of what I'm doing wrong. I assume this is something obvious to a SQL-er, but I'm not seeing it.
Thanks,
-Jeff
Here's the report:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom2 As Status,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADComputers.Location,
tblAssets.Description,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblADComputers.OU,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where (tblAssetCustom.Custom3 Not Like '%companyx%' Or
tblAssetCustom.Custom3 Is Null) And (tblAssetCustom.Custom2 Not Like
'%retired%' Or tblAssetCustom.Custom2 Not Like '%secondary%' Or
tblAssetCustom.Custom2 Is Null) And tblAssetCustom.Model <>
'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
tblAssetCustom.Manufacturer Like '%microsoft%') And
tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate
That's not the only anomaly with my report, but I believe it's the most telling of what I'm doing wrong. I assume this is something obvious to a SQL-er, but I'm not seeing it.

Thanks,
-Jeff
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-02-2017 02:11 PM
I've adjusted your reports to replace some OR's with AND's, if you want to your report to exclude multiple values, using OR will not work, as illustrated above. I wasn't sure if you also wanted to exclude NULL values, or if you wanted to see NULL values. The report below currently filters out NULL values, if you wish to see them, you can remove the highlighted portion of the code.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom2 As Status,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADComputers.Location,
tblAssets.Description,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblADComputers.OU,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Custom3 Not Like '%companyx%'And
tblAssetCustom.Custom3 Is Not Null And tblAssetCustom.Custom2 Not Like
'%retired%' And tblAssetCustom.Custom2 Not Like '%secondary%'And
tblAssetCustom.Custom2 Is Not Null And tblAssetCustom.Model <>
'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
tblAssetCustom.Manufacturer Like '%microsoft%') And
tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom2 As Status,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADComputers.Location,
tblAssets.Description,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblADComputers.OU,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Custom3 Not Like '%companyx%'
tblAssetCustom.Custom3 Is Not Null
'%retired%' And tblAssetCustom.Custom2 Not Like '%secondary%'
tblAssetCustom.Custom2 Is Not Null
'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
tblAssetCustom.Manufacturer Like '%microsoft%') And
tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate
3 REPLIES 3

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-06-2017 07:40 PM
Thanks guys, that was indeed the problem with my query and the solution worked well. It was too many AND ORs for my brain, but incredibly obvious now that you spelled it out.
Thanks,
-Jeff
Thanks,
-Jeff
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-02-2017 02:11 PM
I've adjusted your reports to replace some OR's with AND's, if you want to your report to exclude multiple values, using OR will not work, as illustrated above. I wasn't sure if you also wanted to exclude NULL values, or if you wanted to see NULL values. The report below currently filters out NULL values, if you wish to see them, you can remove the highlighted portion of the code.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom2 As Status,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADComputers.Location,
tblAssets.Description,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblADComputers.OU,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Custom3 Not Like '%companyx%'And
tblAssetCustom.Custom3 Is Not Null And tblAssetCustom.Custom2 Not Like
'%retired%' And tblAssetCustom.Custom2 Not Like '%secondary%'And
tblAssetCustom.Custom2 Is Not Null And tblAssetCustom.Model <>
'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
tblAssetCustom.Manufacturer Like '%microsoft%') And
tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom2 As Status,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblState.Statename,
tblADComputers.Location,
tblAssets.Description,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblADComputers.OU,
tblComputersystem.Domainrole
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssetCustom.Custom3 Not Like '%companyx%'
tblAssetCustom.Custom3 Is Not Null
'%retired%' And tblAssetCustom.Custom2 Not Like '%secondary%'
tblAssetCustom.Custom2 Is Not Null
'Virtual Machine' And (tblAssetCustom.Manufacturer Like '%dell%' Or
tblAssetCustom.Manufacturer Like '%microsoft%') And
tblADComputers.OU Not Like '%denver%' And tblComputersystem.Domainrole < 2
Order By tblAssetCustom.PurchaseDate
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-01-2017 11:32 PM
Looking at your WHERE clause
if Custom2 is, say, "Retired", then
Since your condition is "If A or B or C", because B is true the block evaluates as true.
Assuming what you're after is filtering out a record if Custom2 contains either "retired" or "secondary", you might want to reformat that something like
WHERE
( tblAssetCustom.Custom3 Not Like '%companyx%'
OR tblAssetCustom.Custom3 Is Null
)
AND ( tblAssetCustom.Custom2 Not Like '%retired%'
OR tblAssetCustom.Custom2 Not Like '%secondary%'
OR tblAssetCustom.Custom2 Is Null
)
AND ( tblAssetCustom.Manufacturer Like '%dell%'
OR tblAssetCustom.Manufacturer Like '%microsoft%'
)
AND tblAssetCustom.Model <> 'Virtual Machine'
AND tblADComputers.OU Not Like '%denver%'
AND tblComputersystem.Domainrole < 2
if Custom2 is, say, "Retired", then
( tblAssetCustom.Custom2 Not Like '%retired%' => false
OR tblAssetCustom.Custom2 Not Like '%secondary%' => true
OR tblAssetCustom.Custom2 Is Null => false
)
Since your condition is "If A or B or C", because B is true the block evaluates as true.
Assuming what you're after is filtering out a record if Custom2 contains either "retired" or "secondary", you might want to reformat that something like
AND (
tblAssetCustom.Custom2 Is Null
OR ( tblAssetCustom.Custom2 Not Like '%retired%'
AND tblAssetCustom.Custom2 Not Like '%secondary%'
)
)
