nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Max Audit Date

Ravi_Bheemarao

-1down vote favorite

Need assistance with Oracle query , Identify documents which is not accessed between the date provided and show only one latest action on document (either view, fetch, reserve, which is mentioned by AuditID in below query) -

I have the query prepared its returning data but for some dates its not returning valid data - please assist.

Below is the query I have written..

SELECT dt.dataid "Data ID", dt.name "Document Name", kt.LastName "Last Name", kt.FirstName "First Name", DA.AUDITSTR "Acess Description" FROM dtree dt, dauditnew da, kuaf kt

WHERE dt.dataid = da.dataid

AND kt.id = da.PERFORMERID

AND da.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003)

AND da.auditdate BETWEEN to_date('01-Sep-2000', 'dd-mon-yyyy HH:MI:SS') AND to_date('01-Sep-2016', 'dd-mon-yyyy HH:MI:SS')

AND da.auditdate = (SELECT MAX(da2.auditdate) FROM dauditnew da2, dtree dt2 WHERE dt2.dataid = da2.dataid AND dt2.name = dt.name GROUP BY dt2.name)

AND dt.DATAID IN (SELECT DATAID FROM DTREE START WITH DATAID = '7547034' CONNECT BY PRIOR DATAID = PARENTID)

With this query, I am getting multiple hits for the same document like Fetch, Reserve and View document type. I need to get latest action lets say - if Fetch is the last happened for that particular document then that should be shown ONLY, ignoring other 2 actions. So, I need to get latest list of documents for the given date with last action.

Find more posts tagged with

Comments

John Simon

I would use eventid in place of your "da.auditid = " line. Also I would use dtreeancestors instead of the connect by if you are on a content server version after V971.

select dt.dataid, dt.name, k.lastname, k.firstname, da.eventid, da.auditdate, da.auditstr
from dtree dt
join dauditnew da on dt.dataid = da.dataid
join kuaf k on k.id = da.performerid
where da.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003)
AND da.auditdate BETWEEN to_date('01-Jul-2016', 'dd-mon-yyyy HH:MI:SS') AND to_date('01-Sep-2016', 'dd-mon-yyyy HH:MI:SS')
and da.eventid = (select max(eventid) from dauditnew da2 where da.dataid = da2.dataid)
and dt.dataid in (select dataid from dtreeancestors where ancestorid = XXXXXX);

Colin_Schmidt

For one thing, since DATAID is a number, you don’t need quotes around it.

The use of a Function in Oracle (To_Char, to-number, to_date, etc.) always adds enormous time to the Selection. It’s difficult to escape using them.

I’d use the DTREEANCESTORS as well, but you could use either EVENTID or AUDITDATE. EVENTID is sequential.

If you don’t put the same AUDITID action restrictions on the subquery, you could get anything other than the list you have elsewhere in the query (including the “Admin” ID). Do you also want “Admin” to be included in these actions? If not, then you would exclude with “da.performer <> 1000”.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, August 15, 2016 4:28 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Max Audit Date

Max Audit Date

Posted by Bheemarao, Ravi On 08/15/2016 04:27 PM

-1down votefavorite

I have the query prepared its returning data but for some dates its not returning valid data - please assist.

Below is the query I have written..

SELECT dt.dataid"Data ID", dt.name"Document Name", kt.LastName"Last Name", kt.FirstName"First Name", DA.AUDITSTR"Acess Description" FROM dtree dt, dauditnew da, kuaf kt

WHERE dt.dataid= da.dataid

AND kt.id= da.PERFORMERID

AND da.auditidIN (1,2, 3, 4,5, 6, 7,8, 10, 11,12, 14, 15,23, 25, 26,28, 34, 35,173, 174, 636002,636003)

AND da.auditdateBETWEEN to_date('01-Sep-2000','dd-mon-yyyy HH:MI:SS')AND to_date('01-Sep-2016','dd-mon-yyyy HH:MI:SS')

AND da.auditdate= (SELECT MAX(da2.auditdate)FROM dauditnew da2, dtree dt2WHERE dt2.dataid= da2.dataidAND dt2.name= dt.nameGROUP BY dt2.name)

AND dt.DATAIDIN (SELECT DATAIDFROM DTREE STARTWITH DATAID ='7547034' CONNECTBY PRIOR DATAID = PARENTID)

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.

Colin_Schmidt

This part bothers me in that you define DTREE in the Subquery, but it has no value. It needs to refer back to the DATAID that is found and worked on at that moment. The DATAID here would be different than that in the “FROM”. You wouldn’t even need to define DTREE here, but just equate the DA2.DATAID to the DA.DATAID.

(SELECT MAX(da2.auditdate)FROM dauditnew da2,dtree dt2 WHERE dt2.dataid= da2.dataidAND dt2.name= dt.nameGROUP BY dt2.name)

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, August 16, 2016 9:35 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE Max Audit Date

RE Max Audit Date

Posted by Schmidt, Colin On 08/16/2016 09:34 AM

For one thing, since DATAID is a number, you don’t need quotes around it.

The use of a Function in Oracle (To_Char, to-number, to_date, etc.) always adds enormous time to the Selection. It’s difficult to escape using them.

I’d use the DTREEANCESTORS as well, but you could use either EVENTID or AUDITDATE. EVENTID is sequential.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Max Audit Date

Posted by Bheemarao, Ravi On 08/15/2016 04:27 PM

-1down votefavorite

I have the query prepared its returning data but for some dates its not returning valid data - please assist.

Below is the query I have written..

SELECT dt.dataid"Data ID", dt.name"Document Name", kt.LastName"Last Name", kt.FirstName"First Name", DA.AUDITSTR"Acess Description" FROM dtree dt, dauditnew da, kuaf kt

WHERE dt.dataid= da.dataid

AND kt.id= da.PERFORMERID

AND da.auditidIN (1,2, 3, 4,5, 6, 7,8, 10, 11,12, 14, 15,23, 25, 26,28, 34, 35,173, 174, 636002,636003)

AND da.auditdateBETWEEN to_date('01-Sep-2000','dd-mon-yyyy HH:MI:SS')AND to_date('01-Sep-2016','dd-mon-yyyy HH:MI:SS')

AND da.auditdate= (SELECT MAX(da2.auditdate)FROM dauditnew da2, dtree dt2WHERE dt2.dataid= da2.dataidAND dt2.name= dt.nameGROUP BY dt2.name)

AND dt.DATAIDIN (SELECT DATAIDFROM DTREE STARTWITH DATAID ='7547034' CONNECTBY PRIOR DATAID = PARENTID)

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Colin_Schmidt

Once again, I modified it to use the DTREEANCESTORS and the last remark and it ran and completed in a folder structure that ran for over an hour before.

SELECT dt.dataid "Data ID", dt.name "Document Name", kt.LastName "Last Name", kt.FirstName "First Name", da.eventid, DA.AUDITSTR "Access Description"

FROM dtreeancestors dtan

Join dauditnew da on dtan.dataid = da.dataid and da.subtype = 144

and da.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003)

AND to_char(da.auditdate, 'yyyy-mm-dd') BETWEEN '2000-09-01' AND '2016-09-01'

AND da.eventid = (SELECT MAX(da2.eventid) FROM dauditnew da2 WHERE da2.dataid = dtan.dataid and da2.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003))

join dtree dt on dtan.dataid = dt.dataid

join kuaf kt on kt.id = da.PERFORMERID

WHERE dtan.ancestorid = <dataid>

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

RE Max Audit Date

Posted by Schmidt, Colin On 08/16/2016 09:34 AM

For one thing, since DATAID is a number, you don’t need quotes around it.

The use of a Function in Oracle (To_Char, to-number, to_date, etc.) always adds enormous time to the Selection. It’s difficult to escape using them.

I’d use the DTREEANCESTORS as well, but you could use either EVENTID or AUDITDATE. EVENTID is sequential.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Max Audit Date

Posted by Bheemarao, Ravi On 08/15/2016 04:27 PM

-1down votefavorite

I have the query prepared its returning data but for some dates its not returning valid data - please assist.

Below is the query I have written..

SELECT dt.dataid"Data ID", dt.name"Document Name", kt.LastName"Last Name", kt.FirstName"First Name", DA.AUDITSTR"Acess Description" FROM dtree dt, dauditnew da, kuaf kt

WHERE dt.dataid= da.dataid

AND kt.id= da.PERFORMERID

AND da.auditidIN (1,2, 3, 4,5, 6, 7,8, 10, 11,12, 14, 15,23, 25, 26,28, 34, 35,173, 174, 636002,636003)

AND da.auditdateBETWEEN to_date('01-Sep-2000','dd-mon-yyyy HH:MI:SS')AND to_date('01-Sep-2016','dd-mon-yyyy HH:MI:SS')

AND da.auditdate= (SELECT MAX(da2.auditdate)FROM dauditnew da2, dtree dt2WHERE dt2.dataid= da2.dataidAND dt2.name= dt.nameGROUP BY dt2.name)

AND dt.DATAIDIN (SELECT DATAIDFROM DTREE STARTWITH DATAID ='7547034' CONNECTBY PRIOR DATAID = PARENTID)

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Ravi_Bheemarao

Hi John,

Thanks for your reply.. When I replace my query with EventID and AncestorsID line of code, its not returning any results.

When I replace my query back it works but not giving all the data .

Please let me know, if you have any suggestions.

Thanks

Ravi_Bheemarao

Hi Colin,

New UPDATED query what you sent is not returning any results.

Can you please check

Thanks

Colin_Schmidt

It returned on my execution of it.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, August 16, 2016 11:57 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE Max Audit Date

RE RE Max Audit Date

Posted by Bheemarao, Ravi On 08/16/2016 11:54 AM

Hi Colin,

New UPDATED query what you sent is not returning any results.

Can you please check

Thanks

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Ravi_Bheemarao

Hi Colin,

We are using LL 9.7.1 SP1.

When I try to query dtreeancestors, it's not returning any data with that particulare id.

Select * from dtreeancestors where ancestorid = 7547034 - does not return any results, not sure dtreeancestors has right data in it for this query.

I haven't used dtreeancestors before, not sure how that traverses thru sub-folders.

Most of the time I used CONNECT BY PRIOR to get results from sub-folder level.

Thanks

Colin_Schmidt

That’s probably what you have to put back and get rid of DTREEANCESTORS.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, August 16, 2016 2:00 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE Max Audit Date

RE RE Max Audit Date

Posted by Bheemarao, Ravi On 08/16/2016 01:58 PM

Hi Colin,

We are using LL 9.7.1 SP1.

When I try to query dtreeancestors, it's not returning any data with that particulare id.

Select * from dtreeancestors where ancestorid = 7547034 - does not return any results, not sure dtreeancestors has right data in it for this query.

I haven't used dtreeancestors before, not sure how that traverses thru sub-folders.

Most of the time I used CONNECT BY PRIOR to get results from sub-folder level.

Thanks

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Colin_Schmidt

This worked for me using the “Connect by”:

SELECT dt.dataid "Data ID", dt.name "Document Name", kt.LastName "Last Name", kt.FirstName "First Name", da.eventid, DA.AUDITSTR "Access Description"

FROM dtree dt

Join dauditnew da on dt.dataid = da.dataid and da.subtype = 144

and da.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003)

AND to_char(da.auditdate, 'yyyy-mm-dd') BETWEEN '2000-09-01' AND '2016-09-01'

AND da.eventid = (SELECT MAX(da2.eventid) FROM dauditnew da2 WHERE da2.dataid = dt.dataid and da2.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003))

join kuaf kt on kt.id = da.PERFORMERID

WHERE dt.DATAID IN (SELECT dtd.DATAID FROM DTREE dtd START WITH dtd.DATAID = <DataID> CONNECT BY PRIOR dtd.DATAID = dtd.PARENTID)

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Knowledgelink@capitalone.com

Internal: 433-8910

External: 804-284-8910

Cell: 918-904-9223

Don’t waste space with attachments – follow thesestepsto e-mail links to documents stored in Knowledgelink

RE RE Max Audit Date

Posted by Bheemarao, Ravi On 08/16/2016 01:58 PM

Hi Colin,

We are using LL 9.7.1 SP1.

When I try to query dtreeancestors, it's not returning any data with that particulare id.

Select * from dtreeancestors where ancestorid = 7547034 - does not return any results, not sure dtreeancestors has right data in it for this query.

I haven't used dtreeancestors before, not sure how that traverses thru sub-folders.

Most of the time I used CONNECT BY PRIOR to get results from sub-folder level.

Thanks

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Ravi_Bheemarao

Hi Colin,

Looks like the query what we have now, gives the list of documents that have been "hit/used" between the 2 dates.

I want to get list of documents which have not had a "use/hit" between the dates provided.

I am thinking in below lines - Get list of all documents in the system within the date provided and MINUS documents, which have been "hit/used".

Please see below query and advice.

SELECT dt1.dataid "Data ID", dt1.name "Document Name"
FROM dtree dt1
WHERE dt1.DATAID IN (SELECT dtd1.DATAID FROM DTREE dtd1 START WITH dtd1.DATAID = '7547034' CONNECT BY PRIOR dtd1.DATAID = dtd1.PARENTID)
MINUS
SELECT dt.dataid "Data ID", dt.name "Document Name"
FROM dtree dt
Join dauditnew da on dt.dataid = da.dataid and da.subtype = 144
          and da.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003)
          --AND to_char(da.auditdate, 'yyyy-mm-dd') BETWEEN '2000-09-01' AND '2016-09-01'
          AND to_char(da.auditdate, 'yyyy-mm-dd') <= '2013-09-01'
          AND da.eventid = (SELECT MAX(da2.eventid) FROM dauditnew da2 WHERE da2.dataid = dt.dataid and da2.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003))
join kuaf kt on kt.id = da.PERFORMERID
WHERE dt.DATAID IN (SELECT dtd.DATAID FROM DTREE dtd START WITH dtd.DATAID = '7547034' CONNECT BY PRIOR dtd.DATAID = dtd.PARENTID)

Thanks

Colin_Schmidt

Then I would put the final qualification here:

AND(to_char(da.auditdate, 'yyyy-mm-dd') BETWEEN '2000-09-01' AND '2016-09-01'
AND to_char(da.auditdate, 'yyyy-mm-dd') <= '2013-09-01'
AND da.eventid = (SELECT MAX(da2.eventid) FROM dauditnew da2 WHERE da2.dataid = dt.dataid and da2.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003))) is NULL

This would then say, “if nothing is returned between these dates then no User Action was executed”. I haven’t executed this, but it would seem to be a natural opposite of showing “what was found”.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, August 18, 2016 11:55 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE RE Max Audit Date

RE RE RE Max Audit Date

Posted by Bheemarao, Ravi On 08/18/2016 11:48 AM

Hi Colin,

Looks like the query what we have now, gives the list of documents that have been "hit/used" between the 2 dates.

I want to get list of documents which have not had a "use/hit" between the dates provided.

I am thinking in below lines - Get list of all documents in the system within the date provided and MINUS documents, which have been "hit/used".

Please see below query and advice.

Thanks

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Ravi_Bheemarao

Placing IS NULL, is giving SQL errors..

SELECT dt.dataid "Data ID", dt.name "Document Name", (to_char(da.auditdate , 'dd-mon-yyyy HH:MI:SS' )) "Audit Date",DA.PERFORMERID "Performer ID", kt.LastName "Last Name", kt.FirstName "First Name",

DA.AUDITSTR "Acess Description"

FROM dtree dt

Join dauditnew da on dt.dataid = da.dataid and da.subtype = 144

and da.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003)

AND (to_char(da.auditdate, 'yyyy-mm-dd') BETWEEN '2000-09-01' AND '2016-09-01'

join kuaf kt on kt.id = da.PERFORMERID

WHERE dt.DATAID IN (SELECT dtd.DATAID FROM DTREE dtd START WITH dtd.DATAID = '7547034' CONNECT BY PRIOR dtd.DATAID = dtd.PARENTID)

Colin_Schmidt

If you run this in SQL Developer, it’ll indicate the Line with the Problem. CS’ LiveReports doesn’t give enough of any detail. I assumed I put the “(“ & “)” in the right place. I hadn’t time to test it.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, August 18, 2016 2:18 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE RE Max Audit Date

RE RE RE Max Audit Date

Posted by Bheemarao, Ravi On 08/18/2016 02:14 PM

Placing IS NULL, is giving SQL errors..

SELECT dt.dataid"Data ID", dt.name"Document Name", (to_char(da.auditdate , 'dd-mon-yyyy HH:MI:SS' )) "Audit Date",DA.PERFORMERID"Performer ID", kt.LastName"Last Name", kt.FirstName"First Name",

DA.AUDITSTR "Acess Description"

FROMdtree dt

Joindauditnew da on dt.dataid= da.dataid and da.subtype =144

and da.auditidIN (1,2, 3, 4,5, 6, 7,8, 10, 11,12, 14, 15,23, 25, 26,28, 34, 35,173, 174, 636002,636003)

AND(to_char(da.auditdate,'yyyy-mm-dd') BETWEEN '2000-09-01' AND '2016-09-01'

AND da.eventid= (SELECT MAX(da2.eventid)FROM dauditnew da2WHERE da2.dataid = dt.dataid and da2.auditidIN (1,2, 3, 4,5, 6, 7,8, 10, 11,12, 14, 15,23, 25, 26,28, 34, 35,173, 174, 636002,636003))) IS NULL

joinkuaf kt on kt.id= da.PERFORMERID

WHERE dt.DATAIDIN (SELECT dtd.DATAIDFROM DTREE dtd START WITH dtd.DATAID= '7547034' CONNECT BY PRIOR dtd.DATAID= dtd.PARENTID)

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Ravi_Bheemarao

I am using TOAD, its giving error as ORA-00933: SQL command not properly ended.

This is pretty vague error.

Thanks

CBancroft

Sounds like you are missing a semi-colon (;) at the end of your statement

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, August 18, 2016 4:13 PM
To: eLink Recipient
Subject: RE RE RE Max Audit Date

RE RE RE Max Audit Date

Posted by Bheemarao, Ravi On 08/18/2016 04:07 PM

I am using TOAD, its giving error as ORA-00933: SQL command not properly ended.

This is pretty vague error.

Thanks

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Colin_Schmidt

The way I put the “(“ …. “) is NULL” is wrong. Try this. Experiment. This executes, but I don’t have the time to determine if it’s exactly what you want.

SELECT dt.dataid "Data ID", dt.name "Document Name", kt.LastName "Last Name", kt.FirstName "First Name", da.eventid, DA.AUDITSTR "Access Description"

FROM dtree dt

Join dauditnew da on dt.dataid = da.dataid and da.subtype = 144

and (da.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003)

AND to_char(da.auditdate, 'yyyy-mm-dd') BETWEEN '2000-09-01' AND '2016-09-01'

AND (SELECT MAX(da2.eventid) FROM dauditnew da2 WHERE da2.dataid = da.dataid

and da2.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003)) is NULL)

join kuaf kt on kt.id = da.PERFORMERID

WHERE dt.DATAID IN (SELECT dtd.DATAID FROM DTREE dtd START WITH dtd.DATAID = <DATAID> CONNECT BY PRIOR dtd.DATAID = dtd.PARENTID)

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

RE RE RE Max Audit Date

Posted by Bheemarao, Ravi On 08/18/2016 04:07 PM

I am using TOAD, its giving error as ORA-00933: SQL command not properly ended.

This is pretty vague error.

Thanks

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Ravi_Bheemarao

I am using Toad, its kind of giving vague error eventhough its terminated properly.

I used "("&")" and other delimiters to recognie IS NULL

ORA-00933: SQL command not properly ended

John Simon

Ravi,

I don't think "AND da.eventid = (SELECT MAX(da2.eventid) FROM dauditnew da2 WHERE da2.dataid = dt.dataid and da2.auditid IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174,636002, 636003))) IS NULL" will work. High level that is saying is "where the eventid = some number = null". I think your original instinct is correct...select the population minus the records to remove...and you can't just select your population from audit because what if that dataid has not been audited recently. Also as your population grows this will take longer and longer to execute so keep that in mind. Anyway, my 2 cents worth.

Cheers...

Ravi_Bheemarao

I am working on all your sugestions and will get back soon..

In mean time, looks like user wants to see document path to be listed in query

For Ex: Enterprise -> Folder A -> Folder B -> Test.doc

Need to show folder path in query, I am trying to compare parentid with data id (listed code in RED BOLD),. Please advice.

Thanks

SELECT dt.dataid "Data ID", dt.name "Document Name", b.name "Parent Name", d.name "Parent Folder 2",

(to_char(da.auditdate , 'dd-mon-yyyy HH:MI:SS' )) "Audit Date",

DA.PERFORMERID "Performer ID", kt.LastName "Last Name", kt.FirstName "First Name", DA.AUDITSTR "Acess Description"

FROM dtree dt Join dauditnew da on dt.dataid = da.dataid

AND to_char(da.auditdate, 'dd-mon-yyyy') <= '2013-09-01'

AND da.eventid = (SELECT MAX(da2.eventid)

FROM dauditnew da2 WHERE da2.dataid = dt.dataid and da2.auditid IN (2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003))

join kuaf kt on kt.id = da.PERFORMERID

join dtree b on dt.parentid = dt.dataid

join dtree d on b.parentid = dt.dataid

WHERE dt.DATAID IN (SELECT dtd.DATAID FROM DTREE dtd START WITH dtd.DATAID = '7547034' CONNECT BY PRIOR dtd.DATAID = dtd.PARENTID)

eLink User

Very difficult to decipher your query!

Oracle has a SYS_Connect_by_path function which is useful in your endeavor. In ourorganization we have a simple function that when invoked like this

Select dataid,cop_dtree_obj_path(dataid)from dtree where dataid =12345

Inline image 1

I believe I have posted our function in this forum.I use TOAD and will never use the LR text area to work with developing a query...

WR has much better tools.

..... appnair appunair( Livelink Rocks )

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Mon, Aug 22, 2016 at 3:47 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE RE RE Max Audit Date 2 Posted byBheemarao, RaviOn 08/22/2016 04:44 PM

I am working on all your sugestions and will get back soon..In mean time, looks like user wants to see document path to be listed in queryFor Ex: Enterprise -> Folder A -> Folder B -> Test.docNeed to show folder path in query, I am trying to compare parentid with data id (listed code in RED BOLD),. Please advice.ThanksSELECT dt.dataid "Data ID", dt.name "Document Name", b.name "Parent Name", d.name "Parent Folder 2",(to_char(da.auditdate , 'dd-mon-yyyy HH:MI:SS' )) "Audit Date",DA.PERFORMERID "Performer ID", kt.LastName "Last Name", kt.FirstName "First Name", DA.AUDITSTR "Acess Description" FROM dtree dt Join dauditnew da on dt.dataid = da.dataidAND to_char(da.auditdate, 'dd-mon-yyyy') <= '2013-09-01' AND da.eventid = (SELECT MAX(da2.eventid) FROM dauditnew da2 WHERE da2.dataid = dt.dataid and da2.auditid IN (2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 14, 15, 23, 25, 26, 28, 34, 35, 173, 174, 636002, 636003)) join kuaf kt on kt.id = da.PERFORMERID join dtree b on dt.parentid = dt.dataid join dtree d on b.parentid = dt.dataid WHERE dt.DATAID IN (SELECT dtd.DATAID FROM DTREE dtd START WITH dtd.DATAID = '7547034' CONNECT BY PRIOR dtd.DATAID = dtd.PARENTID)
[To post a comment, use the normal reply function]Topic:Max Audit DateForum:Content Server LiveReports ForumContent Server:Knowledge Center

image.png

John Simon

Since you are already doing a "connect by" query look into sys_connect_by_path.

Ravi_Bheemarao

I am not able to add sys_connect_by_path(Name,':') as "Path" into my existing query, as the error says

ORA-01788: CONNECT BY clause required in this query block

I tried modifying several ways, looks like something I am missing to add this in my query.

Any suggestion would be appreciated.

Thanks

eLink User

select sys_connect_by_path(Name,':') as "Path" from dtree start with dataid=170276103 connect by prior dataid=parentid

for e.g that is how you can use

it to find parentage for a dataid.

if you do an abs on those then it will even crawl negative hierarchies very inefficient so i did not show that.Thousands of connect by

queries are explained here.Lindsay Davies,John Simon all of these guys are huge DB experts and query experts.

When you are developing queries you want to know how the queries work inner/outer etc Your problem stems from the fact that your are trying desperately to fill in bits and pieces of

some working query.Looking at a sys_connect_by_path will work only if it is predicated by a connect by .Hence what we do is

we create a function inside the function we return the path either thru sys_connect_by_path or even pre pending the last name known to a variable.

Hopefully you take this in the right spirit.Anyways I am used to people mis interpreting a earnest contributor's post

but that is how I learned when i did not know things.

..... appnair appunair( Livelink Rocks )

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Tue, Aug 23, 2016 at 10:57 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE RE RE Max Audit Date 2 Posted byBheemarao, RaviOn 08/23/2016 11:50 AM

I am not able to add sys_connect_by_path(Name,':') as "Path" into my existing query, as the error saysORA-01788: CONNECT BY clause required in this query blockI tried modifying several ways, looks like something I am missing to add this in my query.Any suggestion would be appreciated.Thanks
[To post a comment, use the normal reply function]Topic:Max Audit DateForum:Content Server LiveReports ForumContent Server:Knowledge Center

Colin_Schmidt

Another important point: in addition to looking in the KC, look somewhere else. Search online for these functions. Look at other examples, buy a manual. I have two: an O’Reilly pocket guide published in 2004, which I always reference(my memory fails me by the minute). I’ve another more introductory book, but I rarely reference it. Seeking help on this shouldn’t be just here where the DB is known. It needs to be anywhere else too.

Phrasing your question in Google or Bing will get you very similar links to what you want.

I took no course, but learned OJT….what little I know and still don’t know. A course is good, but again don’t restrict your queries for help just here in the KC concerning SQL power, functions, etc.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, August 23, 2016 12:38 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Re RE RE RE Max Audit Date 2

Re RE RE RE Max Audit Date 2

Posted by eLink On 08/23/2016 12:38 PM

select sys_connect_by_path(Name,':') as "Path" from dtree start with dataid=170276103 connect by prior dataid=parentid

for e.g that is how you can use

it to find parentage for a dataid.

if you do an abs on those then it will even crawl negative hierarchies very inefficient so i did not show that.Thousands of connect by

queries are explained here.Lindsay Davies,John Simon all of these guys are huge DB experts and query experts.

When you are developing queries you want to know how the queries work inner/outer etc Your problem stems from the fact that your are trying desperately to fill in bits and pieces of

some working query.Looking at a sys_connect_by_path will work only if it is predicated by a connect by .Hence what we do is

we create a function inside the function we return the path either thru sys_connect_by_path or even pre pending the last name known to a variable.

Hopefully you take this in the right spirit.Anyways I am used to people mis interpreting a earnest contributor's post

but that is how I learned when i did not know things.

..... appnair appunair( Livelink Rocks )

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Tue, Aug 23, 2016 at 10:57 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE RE RE Max Audit Date 2

Posted by Bheemarao, Ravi On 08/23/2016 11:50 AM

I am not able to add sys_connect_by_path(Name,':')as "Path" into my existing query, as the error says
ORA-01788: CONNECT BY clause required in this query block
I tried modifying several ways, looks like something I am missing to add this in my query.
Any suggestion would be appreciated.
Thanks
[To post a comment, use the normal reply function]
Topic:
Max Audit Date
Forum:
Content Server LiveReports Forum
Content Server:
Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Ravi_Bheemarao

Thanks for all your help guys.

Yes, I started the query from scratch and tried to fit in other requirement and make it work.

Query is giving me desired result except for that folder structure.

This SQL will give the right path of the folder -

SELECT DataID, Name, sys_connect_by_path(Name,':') as "Path" FROM DTree

start with DataID=7547034 connect by prior DataID=ParentID

but adding sys_connect_by_path to my huge query is not working.

Currently it shows the document but if the document is 5 or 10 folder deep, its becoming nightmare for the admins to locate document.

Document location along with folder structure will make life easy

I was thinking, one or other would have done this already, thats why I was dependent on this forum.

Hope I will figure out soon.

Thanks

Colin_Schmidt

I would think the “sys_connect_by_path(Name,':')” would work all the way down or up any path. An alternative for them to use that is to use the “lpad”. You’d need to include the above, regardless.

lpad('..', 2*level-1, '_')||dt.name "Folder" It’ll show the path indented like a Windows Folder tree. If you copy it out or export the report, you can change the type of this column to a fixed font for a more easier read. We’ve had requests for this because merely showing the path like the command “sys_connect_by_path” is confusing because of the crowded nature of it and the indented look provides an easier and more familiar view.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, August 24, 2016 2:09 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Re RE RE RE Max Audit Date 2

Re RE RE RE Max Audit Date 2

Posted by Bheemarao, Ravi On 08/24/2016 02:07 PM

Thanks for all your help guys.

Yes, I started the query from scratch and tried to fit in other requirement and make it work.

Query is giving me desired result except for that folder structure.

This SQL will give the right path of the folder -

SELECT DataID,Name, sys_connect_by_path(Name,':')as "Path" FROM DTree

startwith DataID=7547034connect byprior DataID=ParentID

but adding sys_connect_by_path to my huge query is not working.

Currently it shows the document but if the document is 5 or 10 folder deep, its becoming nightmare for the admins to locate document.

Document location along with folder structure will make life easy

I was thinking, one or other would have done this already, thats why I was dependent on this forum.

Hope I will figure out soon.

Thanks

[To post a comment, use the normal reply function]
Topic:	Max Audit Date
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

John Simon

Ravi,

The connect by path only works in a connect by query. Your connect by query is a subquery and that is why it is not working in your overall query. You either need to change your overall query to have the connect by or include another subquery just to get the path...something like "select dataid, (select sys_connect_by_path from dtree where dataid = dt2.dataid connect by...) as path, subtype, etc from dtree dt2 where ...", that type of idea.

"Currently it shows the document but if the document is 5 or 10 folder deep, its becoming nightmare for the admins to locate document.". Another way, you can plug the dataid into search and find the document that way.

Cheers...