select /*+INDEX(filekeypair filekeypair2)*/ FD_RID, FKP_KEYWORD, FKP_VALUE, FD_FILENAME, FD_LASTMODIFIED from filekeypair,(
select distinct p.*, rownum rnum from (
select /*+INDEX(filedirectory filedirattrib_fd_rid)*/ FD_LASTMODIFIED,FD_RID,FD_FILENAME
from filedirectory,(select * from filekeypair where FKP_KEYWORD='CLI_RID') z
where z.FKP_FD_RID(+)=FD_RID
and FD_RID in (select /*+INDEX(B FILEKEYPAIR3)*/ b.FKP_FD_RID from filekeypair b
where (upper(b.FKP_KEYWORD) like upper('CLI_RID')||'%'
and upper(b.FKP_VALUE) like upper('1026697')||'%')
and (upper(b.FKP_KEYWORD) like upper('DISTRIBUTION')||'%'
and upper(b.FKP_VALUE) not like upper('INTERNAL')||'%'))
order by z.FKP_VALUE ASC ,FD_LASTMODIFIED ASC
) p where rownum<=100000
) where rnum>=1 and FKP_FD_RID=FD_RID order by rnum
Definitions:CREATE TABLE FILEKEYPAIR
(
FKP_FD_RID NUMBER,
FKP_KEYWORD VARCHAR2(100 BYTE),
FKP_VALUE VARCHAR2(110 BYTE),
FKP_INSUSER VARCHAR2(100 BYTE),
FKP_INSDATE DATE,
FKP_DELUSER VARCHAR2(100 BYTE),
FKP_DELDATE DATE
)
CREATE TABLE FILEDIRECTORY
(
FD_RID NUMBER(9),
FD_FILENAME VARCHAR2(1000 BYTE),
FD_LASTMODIFIED DATE,
FD_EVE_RID NUMBER(9),
FD_DELETED DATE,
FD_OLD CHAR(1 BYTE),
FD_CLI_RID NUMBER
)
This would be a typical record retrieved using the above query:FD_RID FKP_KEYWORD FKP_VALUE FD_FILENAME FD_LASTMODIFIED
---------- ------------ ---------------------------- --------------------------- -------------------
5741874 CLI_RID 1026697 \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM
5741874 DESIG CC \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM
5741874 PRINT_DATE 20150105 \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM
5741874 FILENAME \NewCCDesigMemo20150103.pdf \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM
5741874 DOC_NAME New CC Desig Memo \NEWCCDESIGMEMO20150103.PDF 1/5/2015 7:19:16 AM
It eventually comes out displayed like this in the UI:DOC_NAME FILENAME PRINT_DATE DESIG CLI_RID
------------------ --------------------------- ----------- ------- --------
New CC Desig Memo \NewCCDesigMemo20150103.pdf 20150105 CC 1026697
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.