February 13, 2015 at 2:20 pm
I'm struggling with this, comparing UNIQUEIDENTIFIER columns..
This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID]
WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT *
FROM [dbo].[Catalog]
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively). Any thoughts? I can't see what I'm missing!
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
February 13, 2015 at 3:09 pm
What does this return?
select * from [dbo].[ExecutionLogStorage] where [ReportID] IS NULL
a null report id could mess up the "not in" logic
February 13, 2015 at 3:29 pm
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
RIGHT OUTER JOIN [dbo].[ExecutionLogStorage] [xl]
on [c].[ItemID] = [xl].[ReportID]
and [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4
Russel Loski, MCSE Business Intelligence, Data Platform
February 13, 2015 at 4:44 pm
I am just trying to get a list of reports in the ReportServer catalog that have not been executed recently, knowing that execution log contains last run information (partial).
Simply stated, I'm looking at returning all rows from [catalog] table that do not exist in [executionLogStorage] table.
There happens to be 1 execution log storage row with a null ReportId, but that is only because the report was recently deleted, which should not affect this. I believe my issue is because the join/compare columns are both UNIQUEIDENTIFIER data types which appear to have limited compare options. The "book" says that UNIQUEIDENTIFIER columns cannot be compared to NULL, but does not specifically state how the IN clause works with them, but even casting them to a different data type doesn't seem to help, nor does any kind of join that I've tested. I would like a way to work around the issue.
Russel's query returns a Cartesian product. The other query returns the 1 row with a NULL reportId, which is not something I'm worried about, since the report merely doesn't exist in the [catalog] table and there isn't an actual FK between the 2 tables (Microsoft apparently doesn't care about "best practices" when it comes to data integrity!)
February 13, 2015 at 5:06 pm
Now try this:
SELECT *
FROM [dbo].[Catalog] c
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND NOT EXISTS (SELECT 1 FROM [dbo].[ExecutionLogStorage] e
WHERE c.[ItemID] = e.[ReportID])
There happens to be 1 execution log storage row with a null ReportId, but that is only because the report was recently deleted, which should not affect this.
IN and NOT IN don't know how to handle nulls, so your query to find the ones that have not run fails because your subselect thinks no rows are NOT IN it, because it can't compared them to NULL, which is in the set.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply