Problem using SQL query as Live Report...

I have designed a report in MS SQL Query Analyser which returns relevant category information relating to objects within a certain container within the Livelink system. It doesn't return the objects themselves. Here is the report:use livelinkselect a.valstr as Name, b.valstr as Phone_Number, c.valstr as Department, d.valstr as Job_Title, e.valstr as Location from livelink.dtree, livelink.llattrdata as a, livelink.llattrdata as b, livelink.llattrdata as c, livelink.llattrdata as d, livelink.llattrdata as e where (livelink.dtree.dataid = a.id and parentid = 110665 and a.attrid = 2)and(livelink.dtree.dataid = b.id and parentid = 110665 and b.attrid = 4)and(livelink.dtree.dataid = c.id and parentid = 110665 and c.attrid = 5)and(livelink.dtree.dataid = d.id and parentid = 110665 and d.attrid = 6)and(livelink.dtree.dataid = e.id and parentid = 110665 and e.attrid = 7)order by a.valstrNow I have got this working as a SQL query I am having trouble getting the correct results when i copy the query into the SQL field in a Live Report. Could someone help to advice me how I need to amend the report to get the same results using a Live Report.ThanksIan

Find more posts tagged with

Comments

volvostephen

Message from via eLinkI am not sure but the 'use livelink' could be the problem. In any case, Ijust simplified your SQL a bit. Should do the exact same thing. One otherthing is to make sure that there is a space after the last character beforeline breaks. If there isn't, after pasting the SQL in, things will getconcatenated. I hope this helps...select a.valstr Name, b.valstr Phone_Number, c.valstr Department, d.valstrJob_Title, e.valstr Location from livelink.dtree X, livelink.llattrdata a, livelink.llattrdata b, livelink.llattrdata c, livelink.llattrdata d, livelink.llattrdata e where x.dataid = a.id and x.dataid = b.id and x.dataid = c.id and x.dataid = d.id and x.dataid = e.id and parentid = 110665 and a.attrid = 2 and b.attrid = 4 and c.attrid = 5 and d.attrid = 6 and e.attrid = 7 order by a.valstr-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: June 15, 2005 12:17 PMTo: eLink RecipientSubject: Problem using SQL query as Live Report...Problem using SQL query as Live Report...Posted by Bennett, Wayne on 06/15/2005 12:02 PMI have designed a report in MS SQL Query Analyser which returns relevantcategory information relating to objects within a certain container withinthe Livelink system. It doesn't return the objects themselves. Here is thereport:use livelinkselect a.valstr as Name, b.valstr as Phone_Number, c.valstr as Department,d.valstr as Job_Title, e.valstr as Location from livelink.dtree, livelink.llattrdata as a, livelink.llattrdata as b,livelink.llattrdata as c, livelink.llattrdata as d, livelink.llattrdata as e where (livelink.dtree.dataid = a.id and parentid = 110665 and a.attrid = 2)and(livelink.dtree.dataid = b.id and parentid = 110665 and b.attrid = 4)and(livelink.dtree.dataid = c.id and parentid = 110665 and c.attrid = 5)and(livelink.dtree.dataid = d.id and parentid = 110665 and d.attrid = 6)and(livelink.dtree.dataid = e.id and parentid = 110665 and e.attrid = 7)order by a.valstrNow I have got this working as a SQL query I am having trouble getting thecorrect results when i copy the query into the SQL field in a Live Report.Could someone help to advice me how I need to amend the report to get thesame results using a Live Report.ThanksIan[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from Alex Kowalenko via eLinkIn your case you should use the "Auto LiveReport" report format.To make sure that you do not get multiple results from multi-version nodes,you should add relations between LLAttrData.VerNum and DTree.VersionNum. Iassume that you have only one category attached to these nodes so you do notneed to filter by LLAttrData.DefID.I have reformatted your query:select a.valstr Name, b.valstr Phone_Number, c.valstr Department, d.valstr Job_Title, e.valstr Location from dtree x, llattrdata a, llattrdata b, llattrdata c, llattrdata d, llattrdata e where x.dataid = a.id and x.versionnum = a.vernum and x.dataid = b.id and x.versionnum = b.vernum and x.dataid = c.id and x.versionnum = c.vernum and x.dataid = d.id and x.versionnum = d.vernum and x.dataid = e.id and x.versionnum = e.vernum and x.parentid = 110665 and a.attrid = 2 and b.attrid = 4 and c.attrid = 5 and d.attrid = 6 and e.attrid = 7 order by a.valstrI use this format when documenting long SQL queries. It can be pasted intothe LiveReport SQL field without worrying about null concatenations. It isgood practice to keep text files of your complex SQL statements. (Note thatif you are using the LiveReport extensions module that comes with theWebReports module then the formatting is preserved in the SQL field) Using aprogrammer's editor you can highlight SQL syntax to make editing easier. Youcan add comments to aid understanding. Here is a link to a Word document with a table format that I have used todescribe LiveReports in design documents:https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=4016047&objAction=propertiesNotice the SQL Query that reports from 2 categories and has embeddedcomments for better understanding.-alex------Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Wednesday, June 15, 2005 2:54 PMTo: eLink RecipientSubject: RE Problem using SQL query as Live Report...RE Problem using SQL query as Live Report...Posted by Fisher, Stephen J on 06/15/2005 02:53 PMMessage from via eLinkI am not sure but the 'use livelink' could be the problem. In any case, Ijust simplified your SQL a bit. Should do the exact same thing. One otherthing is to make sure that there is a space after the last character beforeline breaks. If there isn't, after pasting the SQL in, things will getconcatenated. I hope this helps...select a.valstr Name, b.valstr Phone_Number, c.valstr Department, d.valstrJob_Title, e.valstr Location from livelink.dtree X, livelink.llattrdata a, livelink.llattrdata b, livelink.llattrdata c, livelink.llattrdata d, livelink.llattrdata e where x.dataid = a.id and x.dataid = b.id and x.dataid = c.id and x.dataid = d.id and x.dataid = e.id and parentid = 110665 and a.attrid = 2 and b.attrid = 4 and c.attrid = 5 and d.attrid = 6 and e.attrid = 7 order by a.valstr-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: June 15, 2005 12:17 PMTo: eLink RecipientSubject: Problem using SQL query as Live Report...Problem using SQL query as Live Report...Posted by Bennett, Wayne on 06/15/2005 12:02 PMI have designed a report in MS SQL Query Analyser which returns relevantcategory information relating to objects within a certain container withinthe Livelink system. It doesn't return the objects themselves. Here is thereport:use livelinkselect a.valstr as Name, b.valstr as Phone_Number, c.valstr as Department,d.valstr as Job_Title, e.valstr as Location from livelink.dtree, livelink.llattrdata as a, livelink.llattrdata as b,livelink.llattrdata as c, livelink.llattrdata as d, livelink.llattrdata as e where (livelink.dtree.dataid = a.id and parentid = 110665 and a.attrid = 2)and(livelink.dtree.dataid = b.id and parentid = 110665 and b.attrid = 4)and(livelink.dtree.dataid = c.id and parentid = 110665 and c.attrid = 5)and(livelink.dtree.dataid = d.id and parentid = 110665 and d.attrid = 6)and(livelink.dtree.dataid = e.id and parentid = 110665 and e.attrid = 7)order by a.valstrNow I have got this working as a SQL query I am having trouble getting thecorrect results when i copy the query into the SQL field in a Live Report.Could someone help to advice me how I need to amend the report to get thesame results using a Live Report.ThanksIan[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Problem using SQL query as Live Report...https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=4016144&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Alex_Kowalenko_(x-eu0016039_-_(deleted))

In the eLink reply the reformatted query had prefixed indenting blanks before each subsequent line of the SQL statement. See the document link for an example.

Wayne_Bennett_(sacadmin_-_(deleted))

Thanks both of you for your quick responses and great advice. The main problem was that I was using Live Report instead of Auto LiveReport. This is the first report I've created since installing the system and wasn't familiar with the difference. Also, thanks for the advice re: formatting of query etc.I now have a working report which I just need to tweak a bit more :)Thanks Ian