Including archive records in view

Transact-SQL
1

Hi There,
I have an existing view and I would like to include records from an archive table too.
I cannot seem to be able to achieve this.
The fields in myTable1 have an exact equivalent in myTable1Archive. How would I include the records from myTable1Archive into the view too.

This is the current views Sql.
SELECT dbo.myTask.TaskId AS Expr1, dbo.myTable1.TaskId, dbo.myTable1.LocationId, dbo.myTable1.Description, dbo.myTable1.TypeId, dbo.myTable1.Risk, dbo.myTable1.DateCreated, dbo.myTable1.Status, dbo.myTable1.SourceId
dbo.myTask.AutoId, dbo.myTask.TypeId
FROM dbo.myTable1 INNER JOIN
dbo.myTask ON dbo.myTable1.TaskId = dbo.myTask.TaskId

Thanks for any help you can give.

Best Regrads,

2

It should be a simple matter of a UNION between the two. If you have the potential for duplicates that you want eliminated, use UNION where if you don't care, use UNION ALL because it won't do a DISTINCT on the results and can perform a little better.

This code assumes you also have an archive table for the Task but that may not be the case so tailor to your needs:

SELECT dbo.myTask.TaskId AS Expr1, 
	   dbo.myTable1.TaskId, 
	   dbo.myTable1.LocationId, 
	   dbo.myTable1.Description, 
	   dbo.myTable1.TypeId, 
	   dbo.myTable1.Risk, 
	   dbo.myTable1.DateCreated, 
	   dbo.myTable1.Status, 
	   dbo.myTable1.SourceId 
	   dbo.myTask.AutoId, 
	   dbo.myTask.TypeId
  FROM dbo.myTable1 
  JOIN dbo.myTask 
    ON dbo.myTable1.TaskId = dbo.myTask.TaskId
 UNION ALL
SELECT dbo.myTaskArchive.TaskId AS Expr1, 
	   dbo.myTable1Archive.TaskId, 
	   dbo.myTable1Archive.LocationId, 
	   dbo.myTable1Archive.Description, 
	   dbo.myTable1Archive.TypeId, 
	   dbo.myTable1Archive.Risk, 
	   dbo.myTable1Archive.DateCreated, 
	   dbo.myTable1Archive.Status, 
	   dbo.myTable1Archive.SourceId 
	   dbo.myTask.AutoId, 
	   dbo.myTask.TypeId
  FROM dbo.myTable1Archive 
  JOIN dbo.myTaskArchive
    ON dbo.myTable1Archive.TaskId = dbo.myTaskArchive.TaskId
3

Hi James,
Thanks for the reply.
I think your suggestion will work,

I do not have an archive table for the task.

Is there a way I can sort by TaskId, I get an error when I try to sort.

Best Regards,

Steve.

4

No problem. Make sure the ORDER BY is at the bottom and not in the middle with the UNION ALL operator.

SELECT dbo.myTask.TaskId AS Expr1, 
	   dbo.myTable1.TaskId, 
	   dbo.myTable1.LocationId, 
	   dbo.myTable1.Description, 
	   dbo.myTable1.TypeId, 
	   dbo.myTable1.Risk, 
	   dbo.myTable1.DateCreated, 
	   dbo.myTable1.Status, 
	   dbo.myTable1.SourceId 
	   dbo.myTask.AutoId, 
	   dbo.myTask.TypeId
  FROM dbo.myTable1 
  JOIN dbo.myTask 
    ON dbo.myTable1.TaskId = dbo.myTask.TaskId
 UNION ALL
SELECT dbo.myTaskArchive.TaskId AS Expr1, 
	   dbo.myTable1Archive.TaskId, 
	   dbo.myTable1Archive.LocationId, 
	   dbo.myTable1Archive.Description, 
	   dbo.myTable1Archive.TypeId, 
	   dbo.myTable1Archive.Risk, 
	   dbo.myTable1Archive.DateCreated, 
	   dbo.myTable1Archive.Status, 
	   dbo.myTable1Archive.SourceId 
	   dbo.myTask.AutoId, 
	   dbo.myTask.TypeId
  FROM dbo.myTable1Archive 
  JOIN dbo.myTaskArchive
    ON dbo.myTable1Archive.TaskId = dbo.myTaskArchive.TaskId
 ORDER BY Expr1 /* Order by column alias */
5

Hi James,
Thanks for the quick reply.

When I try to save the view I get an error message saying:
"The Order BY clause is invalid in views, inline functions, derived tables, subqueries and common table expressions, unless TOP or FOR XML is also specified."

Any ideas?

Best Regards,

Steve.

6

Ah, yes, my bad. Can't do ORDER BY in VIEWS. There is a work around but it's a hack.

This is not recommended but you can do

SELECT TOP 100 PERCENT <column names here>

and that allows you to have an ORDER BY. Again, it's a hack. It would be better to have the ordering done in the SQL using the VIEW but I know that is not always possible.

Sorry about the oversight.

7

Something like this using sys.columns as an example. Again, not a best practice. I am sure this violates some commandment somewhere.

CREATE VIEW dbo.TestMe As
SELECT TOP 100 PERCENT *
  FROM sys.columns /* your tables here */
 UNION ALL
SELECT TOP 100 PERCENT *
  FROM sys.columns /* your tables here */
 ORDER BY object_id, column_id /* your order by columns here */
8

I believe that the ORDER BY will not be honoured in some situations, so as you suggest best not to use it.

The ORDER BY should be in the SELECT statement that uses the View, and not in the View itself.

9

P.S. Do you have something in the Archive table that will allow you to sort in Chronological order?

We have two additional columns in our Archive Tables:
Action - whether it was an Update or a Delete (we don't store the inserted/current record in the Archive table)
Archive Data - the Date/Time that the row was Updated / Deleted

So in our reports we provide a "dummy" value for these two columns, for the Parent table, of "*" and the Update Date of the record (which is typically a few ms earlier than the archive record, which is created by an After Trigger)

We also have Create and Update Date/Time and User, in all tables, and also an Edit Number (incremented each time the row is updated, used for Optimistic record locking on web pages)

We store Archive Records for a period of time (based on data retention policy [for THAT table]), so it is possible that there are a lot of rows in the archive table, for a given PKey, so for our normal User Report we only show the most recent 100 rows.

Given that the Current Record may have an Update Date/Time that is slightly older than the latest row in the Archive Table we force the sort of the Current Record first (for a given PKey ID)

So our query for the Current and Archive records on a single, specific, PKey value looks like this:

SELECT	Act		-- Action = U)pdate, D)elete and "*" for Current Record
	, ArchiveDate
	, EditNo	-- Edit Number / Record Version
	, CreateDate
	, CreateUser
	, UpdateDate
	, UpdateUser
	, MyID
	, MyCol1
	, MyCol2
	, ...
FROM
(

	SELECT	[Act] = '*'
		, [ArchiveDate] = UpdateDate
		, EditNo, CreateDate, CreateUser, UpdateDate, UpdateUser, MyID
		, MyCol1, MyCol2, ...
	FROM	dbo.MyTable
	WHERE	    MyID = @MyID
	UNION ALL
	SELECT	*
	FROM
	(
		SELECT TOP 100
			Act, ArchiveDate	-- Columns specific to Archive Table
			, EditNo, CreateDate, CreateUser, UpdateDate, UpdateUser, MyID
			, MyCol1, MyCol2, ...
		FROM	dbo.MyTableArchive
		WHERE	    A_MyID = @MyID
		ORDER BY ArchiveDate DESC
	) AS X
) AS X
ORDER BY 
--	X.MyID,		-- Only required if multiple PKey rows included
	CASE WHEN [Act] = '*' THEN 1 ELSE 2 END
	, X.[ArchiveDate] DESC, EditNo DESC

This doesn't translate well to a VIEW because if it is necessary to restrict the ID on both the MainTable and the Archive Table, I suspect performance would be impaired (removing the TOP 100, and nesting of that sub-select) would probably solve that, if "all" archive records could be included, or perhaps using ROW_NUMBER ... OVER would perform well)