Cats/Atts Query by Date Range

I have posted before for help with a LiveReport based on Categories and have received a lot of help with it. In working with you all and a DBA here at work, I have almost the report I need. Our DBA is not familiar with the Livelink Schema but she did most of what I needed. The following SQL statement is what I have but it is not listing a value in two of the fields, Doc Number and Number of Hard Copies (just ?). I am attaching a document that shows the LiveReport, an abreviated copy of the results and a copy of the CateRegion Map table and Attributes dump for that Category.SQL Statement:SELECT dtree.dataid, dtree.SubType, dtree.GIF, dtree.PermID, dtree.Reserved, dtree.ReservedBy, dtree.name, max(decode(a.attrid,10,a.valdate)) as "Docreview", max(decode(a.attrid,12,a.valstr)) as "Docnum", max(decode(a.attrid,26,a.valint)) as "Number", max(decode(b.attrid,6,b.valstr, 'Corporate')) as "Facility", max(decode(c.attrid,5,c.valstr, 'Quality')) as "Dept" FROM llink.dtree dtree, llink.llattrdata a, (select a.valstr, a.defid,a.attrid, a.id, a.vernum from llink.llattrdata a where attrid=6 and valstr='Corporate')b, (select a.valstr, a.defid,a.attrid, a.id, a.vernum from llink.llattrdata a where attrid=5 and valstr='Quality')c WHERE a.valdate >= %1 and a.valdate <= %2 and a.defid=862314 and dtree.subtype = 144 and a.id = dtree.dataid and a.vernum = dtree.versionnum and a.attrid in (10, 12, 26) and b.id=dtree.dataid and b.vernum= dtree.versionnum and c.id=dtree.dataid and c.vernum=dtree.versionnum GROUP BY dtree.dataid, dtree.SubType, dtree.GIF, dtree.PermID, dtree.Reserved, dtree.ReservedBy, dtree.name ORDER BY "Docreview"Thank you for any help you may be able to provide.

Find more posts tagged with

Comments

Appu_Nair

I would like to offer my suggestions as I frankly don't think the query is any incorrect.For eg -Try to run this query.Take the dataid of your first result from your screen cap,the one that says(EJG stuff).Let's assume it is 12345The category you are after is 862314so we should be able to run this for raw dataselect a.name,a.dataid,l.id,l.attrid,l.valint,l.valstr,l.valdate from dtree a,llattrdata l wherea.dataid=l.id and l.defid=862314 and a.dataid=12345in the result concentrate on the attrid 12 and attrid 26See if they contain values that can be converted by those two functionsbecause if they do not get values then it cannot do decode,or do the max stuff after thatmax(decode(a.attrid,12,a.valstr)) as "Docnum", max(decode(a.attrid,26,a.valint)) as "Number",

Mary_Slane_Corona

AppuThank you for looking at the information and responding:I did the following query:select a.name, a.dataid, l.id, l.attrid, l.valint, l.valstr, l.valdate from dtree a, llattrdata l where a.dataid=l.id and l.defid=862314 and a.dataid=13557401. I have attached a screenprint of the results. I picked the first document on the list from the previous posting. There are 4 versions to the document, the highlighted items (on the screenprint) are from the latest version which shows values for Attrid 12 and 26.Mary

Mary_Slane_Corona

Appu, sorry forgot to add the attachment.

Mary_Slane_Corona

Between Appu and John Simon, they were able to help me with the query that I needed. Thank you both so much.Query is (you will need to substitute the attributes and the dataid to your situation--%1 & %2 are user inputs of date):select a.dataid, a.subtype, a.gif, a.permid, a.reserved, a.reservedby, a.name, b.valdate as "Docreview", c.valstr as "Docnum", d.valint as "Number", e.valstr as "Facility", f.valstr as "Dept" from dtree a, llattrdata b, llattrdata c, llattrdata d, llattrdata e, llattrdata f where a.dataid = b.id and a.versionnum = b.vernum and b.defid = 862314 and b.attrid=10 and a.dataid = c.id and a.versionnum = c.vernum and c.defid = 862314 and c.attrid=12 and a.dataid = d.id and a.versionnum = d.vernum and d.defid = 862314 and d.attrid=26 and a.dataid = e.id and a.versionnum = e.vernum and e.defid = 862314 and e.attrid=6 and a.dataid = f.id and a.versionnum = f.vernum and f.defid = 862314 and f.attrid=5 and a.subtype = 144 and e.valstr = 'Corporate' and f.valstr = 'Quality' and b.valdate >= %1 and b.valdate <= %2

Appu_Nair

Hi Mary,I am glad you have a query that works,but I am not seeing the MAX and decode etc.In the future try to create a view of the different categories you are reporting on.There is an excellent example in the knowledge base.It makes the query much more readable.As far as John helping out,hes is the most bright SQL mind I have seen out there(He mentored me a lot).I can never think in oracle or SQL like John does....and he is a great chap. I bookmark John's and Lindays postings because they are so good for future reference.