cancel

Looking for a SQL query for the following

SOLVED
Go to solution
Highlighted
Joshua Hutley
Outstanding Contributor.

Looking for a SQL query for the following

Hi

 

Hoping a SQL/TRIM guru can help with this please.

 

One of our staff has asked me to generate the following query but it is not easily obtainable using the client search interface.

 

First query

 

All objects of a document type (uri=56)

With electronic attachment type other than VMBX / MSG

Inside folders with classification of (uri=2906) or (uri=2908)

 

second query

 

All document types

With electronic attachment type other than VMBX / MSG

Inside folders with retention schedule (uri=9)

 

I would be very appreciative of any help.

 

Josh 

7 REPLIES
Sander Hoogwerf
Outstanding Contributor.

Re: Looking for a SQL query for the following

Really frustrating here is that the NOT button is disabled for Electronic file type searches.

 

But you can still execute the search that way, capturing the the SQL generated by TRIM (Activity log => tab dataset => rightclick start - you must have profile Administrator).

Then just change the "reExtension IN ('VMBX','MSG')" to "reExtension NOT IN ('VMBX','MSG')" and you're done.

 

As for the within classification part, you can search and captur the same way. Then just combine both statements by limiting the container value of the first query to the results of the second.

 

 

Your second query handles the with only the first above method.

 

You also may want to tidy up the generated SQL's a bit to improve performance.


(Any opinions expressed in this forum are my own personal opinion and should not be construed as an official statement by DXC Technology.)

Analytics & Data Management
Application & Business Services
DXC Technology
Icehouse
Super Contributor.

Re: Looking for a SQL query for the following

Hi Josh, I'm no SQL guru, but I've come up with this via some other queries I had sitting handy.

Try this for your first query, just replace it with your numbers / file extensions.

Let me know how it works for you.

 

SELECT parent.recordId AS Container, child.recordId AS Record, parent.rcFilePlanUri, TSRECELEC.reExtension FROM TSRECORD AS child INNER JOIN TSRECELEC ON child.uri = TSRECELEC.uri RIGHT OUTER JOIN TSRECORD AS parent ON child.rcContainerUri = parent.uri WHERE (parent.isParent = 't') AND (parent.rcFilePlanUri = 1491 OR parent.rcFilePlanUri = 1490) AND (NOT (TSRECELEC.reExtension = 'doc') AND NOT (TSRECELEC.reExtension = 'xls')) AND EXISTS (SELECT uri FROM TSRECELEC AS TSRECELEC_1 WHERE (uri = child.uri)) AND (child.rcRecTypeUri = 7) ORDER BY Container

 

A few tweaks should get it to work for your second query, let me know if you need help with it.

Joshua Hutley
Outstanding Contributor.

Re: Looking for a SQL query for the following

Thanks guys

Both of your suggestions are very helpful.

Joshua Hutley
Outstanding Contributor.

Re: Looking for a SQL query for the following

I created this query. Now if you can help with filtered by date reg between x and x.

 

Thanks

 

SELECT

parent.recordId AS Container, child.recordId AS Record, parent.rcFilePlanUri, TSRECELEC.reExtension FROM TSRECORD AS child INNER JOIN TSRECELEC ON child.uri = TSRECELEC.uri RIGHT OUTER JOIN TSRECORD AS parent ON child.rcContainerUri = parent.uri WHERE (parent.isParent = 't') AND (parent.rcFilePlanUri = 2906 OR parent.rcFilePlanUri = 2908) AND (NOT (TSRECELEC.reExtension = 'vmbx') AND NOT (TSRECELEC.reExtension = 'msg')) AND EXISTS (SELECT uri FROM TSRECELEC AS TSRECELEC_1 WHERE (uri = child.uri)) AND (child.rcRecTypeUri = 56)

Icehouse
Super Contributor.
Solution

Re: Looking for a SQL query for the following

Try this (I've changed your numbers and things to test on my system).

Just remember in TRIM the dates are stored as a string so need the ' around them.

 

 

 

SELECT     parent.recordId AS Container, child.recordId AS Record, parent.rcFilePlanUri, TSRECELEC.reExtension, child.regDateTime
FROM         TSRECORD AS child INNER JOIN
                      TSRECELEC ON child.uri = TSRECELEC.uri RIGHT OUTER JOIN
                      TSRECORD AS parent ON child.rcContainerUri = parent.uri
WHERE     (parent.isParent = 't') AND (parent.rcFilePlanUri = 1491 OR
                      parent.rcFilePlanUri = 1492) AND (NOT (TSRECELEC.reExtension = 'vmbx')) AND (NOT (TSRECELEC.reExtension = 'msg')) AND (child.rcRecTypeUri = 7)
                      AND (child.regDateTime < '2010') AND (child.regDateTime > '2009') AND EXISTS
                          (SELECT     uri
                            FROM          TSRECELEC AS TSRECELEC_1
                            WHERE      (uri = child.uri))

Joshua Hutley
Outstanding Contributor.

Re: Looking for a SQL query for the following

Thanks Isaac

 

A little bit of tweaking was all that was needed.

 

This is perfect.

 

Joshua

 

EWillsey
Acclaimed Contributor.

Re: Looking for a SQL query for the following

wooorah!  Good onya Isaac!

 

As a side note, don't forget that the dates/times stored in the database are in GMT.  So if you're looking for precise results you need to factor in your time difference from GMT.

 

Cheers,

Erik