Contracts expiring in 15 days

[visopenlive]

Hai

This is my query to give contractx expiring in 15 days but I am getting lot of repetitions. How could I avoid them?

select A.DATAID, A.Filename, to_char(A.ValDate,'Month DD, YYYY') "EXPIRY DATE", REPLACE(A.ExpireIn,'?','') "EXPIRE IN # OF DAYS" , A.Location LOCATION
FROM
(select D.DATAID, V.FILENAME Filename, L.ValDate,
L.ValDate-trunc(sysdate) ExpireIn, V.MimeType DocType,
L.ValDate-trunc(sysdate) "ExpireIn", LL_PATH(D.DATAID) Location
from DTree D, LLAttrData L, DVersData V
where D.DataID=V.DocID
and D.DataID=L.ID
and D.VersionNum=L.VerNum
and L.DefID=150061
and L.AttrID=7
and D.SUBTYPE=144
and L.ValDate-trunc(sysdate) between 0 and 30) A,
(select D.DATAID, V.FILENAME Filename, L.ValDate, V.MimeType DocType,
L.ValDate-trunc(sysdate) ExpireIn, LL_PATH(D.DATAID) Location
from DTree D, LLAttrData L, DVersData V
where D.DataID=V.DocID
and D.DataID=L.ID
and D.VersionNum=L.VerNum
and L.DefID=150061
and L.AttrID=3
and D.SUBTYPE=144
and L.VALINT='%1') B
WHERE A.DATAID = B.DATAID



Parameter is UserID.

Please help..thanks..

 

[appnair]

try creating a view on llatrdata according to this article.Then it may help you trach down the inconsistencies

https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=3498953&objAction=ArticleView

It looks to me that your A and B queries have the same SQL in them so it must be pulling a record for each of the matching rows

so in a way you are a saying

Give me from table A where L.AttrID=7 PLUS
Give me from table A where L.AttrID=3

Naturally you will have repeating dataids when the matching happens.

You can eliminate that situation by using the view on llattrdata where each dataid is presented side by side with the column you are interested.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[appnair]

I think you are covering yourself against it by doing this
WHERE A.DATAID = B.DATAID
What I think you are doing is creating a psuedotable A with values form dtree,dversdata and llattrdata and another table B with the same logic

Maybe keep dversdata out from the inner queries and when you have your results just pull it out like this remove all occurences of V in your query and if the query runs without duplicates then you should be able to just do your dataid to v.docid or if you want to keep V have v.Version equate with d.versionnum

select A.DATAID, A.Filename, to_char(A.ValDate,'Month DD, YYYY')
"EXPIRY DATE", REPLACE(A.ExpireIn,'?','') "EXPIRE IN # OF DAYS" ,
A.Location LOCATION
FROM
(select D.DATAID, V.FILENAME Filename, L.ValDate,
L.ValDate-trunc(sysdate) ExpireIn,
L.ValDate-trunc(sysdate) "ExpireIn", LL_PATH(D.DATAID) Location
from DTree D, LLAttrData L
where D.DataID=L.ID
and D.VersionNum=L.VerNum
and L.DefID=150061
and L.AttrID=7
and D.SUBTYPE=144
and L.ValDate-trunc(sysdate) between 0 and 30) A,
(select D.DATAID, V.FILENAME Filename, L.ValDate,
L.ValDate-trunc(sysdate) ExpireIn, LL_PATH(D.DATAID) Location
from DTree D, LLAttrData L
where D.DataID=L.ID
and D.VersionNum=L.VerNum
and L.DefID=150061
and L.AttrID=3
and D.SUBTYPE=144
and L.VALINT='%1') B
WHERE A.DATAID = B.DATAID


Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair