Jump to content

SQL - Find all annotations


Andrew Coody
 Share

Recommended Posts

  • IPRO Employee

Warnings

One or more of the below resolutions require knowledge of T-SQL and/or SQL Management Studio; consult a qualified SQL administrator for assistance with these procedures. Back up any targeted SQL databases before executing any SQL statements. Assisting with these procedures is outside the scope of normal Ipro application support.


 

Here is an example of what the query will return: [ATTACH=CONFIG]n2206[/ATTACH]

 

 

 

 

 

 

 

 

 

EDIT 2019/05/02 - The query was updated to include what the redaction name is, if the 'annotation type' is 'redaction', as well as including the imagekey (page), if applicable. [ATTACH=CONFIG]n3614[/ATTACH]

 

 

 

 

 

 

 

EDIT 2019/06/16 - Cleaned up the query by separating the annotation type from the text. The new column contains any related annotation information. [ATTACH=CONFIG]n3669[/ATTACH]

 

 

 

 

 




/*

If you do not know the Eclipse case database name, run the below query first against your ADD/Nucleus database to get the case and database name.

use --ADD/Nucleus database
select [Name], DatabaseName from
[Enterprise].[CaseProductEnvironment]
where ProductId=3
order by [Name] asc

*/



use --Eclipse Case Database

select
D.DocId
,D.DocumentKey
,ISNULL(DP.ImageKey,'') as 'ImageKey (Page)'

,CASE
      when (DA.AnnotationType is null AND DA.RedactionCatId is not null) then 'REDACTION'
      when (DA.AnnotationType = 0 AND DA.RedactionCatId is null) then 'HIGHLIGHT'
      when  DA.AnnotationType = 1 then 'STICKY NOTE'
      when  DA.AnnotationType = 2 then 'EMBEDDED TEXT'
      when  DA.AnnotationType = 3 then 'MARKUP'
   end as "Annotation Type"
,CASE
      when (DA.AnnotationType is null AND DA.RedactionCatId is not null) then RC.Label
      when  DA.AnnotationType = 1 then DA.AnnotationText
      when  DA.AnnotationType = 2 then DA.AnnotationText
      else ''
   end as "Annotation Text"

from DocumentAnnotations DA

   left join Documents D
   on D.DocId=DA.DocId

   left join DocumentPages DP
   on DP.PageId = DA.PageId

   left join dbo.RedactionCategories RC
   on RC.RedactionCatId=DA.RedactionCatId



ORDER BY D.DocumentKey asc



Edited by Andrew C.
Link to comment
Share on other sites

  • IPRO Employee

Warnings

One or more of the below resolutions require knowledge of T-SQL and/or SQL Management Studio; consult a qualified SQL administrator for assistance with these procedures. Back up any targeted SQL databases before executing any SQL statements. Assisting with these procedures is outside the scope of normal Ipro application support.


 

Here is an example of what the query will return: Capture.PNG.94c81f13d030275b0874d4fb3e9e5b5e.PNG

 

 

 

 

 

 

 

 

 

EDIT 2019/05/02 - The query was updated to include what the redaction name is, if the 'annotation type' is 'redaction', as well as including the imagekey (page), if applicable. 1847148607_2019-05-0211_05_13.png.9692966db71cc54a4f73d175e0914d44.png

 

 

 

 

 

 

 

EDIT 2019/06/16 - Cleaned up the query by separating the annotation type from the text. The new column contains any related annotation information. Capture99.PNG.db6611448b302d8bc33ce40e3d1af048.PNG

 

 

 

 

 




/*

If you do not know the Eclipse case database name, run the below query first against your ADD/Nucleus database to get the case and database name.

use --ADD/Nucleus database
select [Name], DatabaseName from
[Enterprise].[CaseProductEnvironment]
where ProductId=3
order by [Name] asc

*/



use --Eclipse Case Database

select
D.DocId
,D.DocumentKey
,ISNULL(DP.ImageKey,'') as 'ImageKey (Page)'

,CASE
      when (DA.AnnotationType is null AND DA.RedactionCatId is not null) then 'REDACTION'
      when (DA.AnnotationType = 0 AND DA.RedactionCatId is null) then 'HIGHLIGHT'
      when  DA.AnnotationType = 1 then 'STICKY NOTE'
      when  DA.AnnotationType = 2 then 'EMBEDDED TEXT'
      when  DA.AnnotationType = 3 then 'MARKUP'
   end as "Annotation Type"
,CASE
      when (DA.AnnotationType is null AND DA.RedactionCatId is not null) then RC.Label
      when  DA.AnnotationType = 1 then DA.AnnotationText
      when  DA.AnnotationType = 2 then DA.AnnotationText
      else ''
   end as "Annotation Text"

from DocumentAnnotations DA

   left join Documents D
   on D.DocId=DA.DocId

   left join DocumentPages DP
   on DP.PageId = DA.PageId

   left join dbo.RedactionCategories RC
   on RC.RedactionCatId=DA.RedactionCatId



ORDER BY D.DocumentKey asc



Edited by Andrew C.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...