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
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE