nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

IF statement in livereport

Katia_Vermeulen

Hi, Is it possible to use an if statement in a livereport?I wrote a livereport with this code:SELECT D.NAME "DocID", A1.VALSTR "Doc Ver.", D.DCOMMENT "Doc Title", A2.VALDATE "Review Date"FROM (SELECT DTREE.*, (SELECT DISTINCT A.ID FROM LLATTRDATA A WHERE A.ID = DTREE.dataid And A.DEFID = 801739 And A.ATTRID = 3 ) AS QAHD FROM DTREESTART WITH dataid=812190 CONNECT BY PRIOR dataid=parentid) D, LLATTRDATA A1, LLATTRDATA A2, DversdataWHERE D.QAHD IS NOT NULL AND(D.DATAID = A1.ID) AND (D.DATAID = A2.ID) AND (A1.DEFID = 801739) AND (A1.ATTRID = 299) AND (A2.DEFID = 801739) AND (A2.ATTRID = 3) AND (A2.VALDATE >= %2) AND (A2.VALDATE <= %3) And ORDER by D.NameThis works fine.The output of this report are generations and documents.Now I would like to have only the latest verseions of the documents so I would like to add this statement but it doesn't work.if (D.Subtype = 144)((D.DataID = DVersData.DocID) And(D.VersionNum = DVersdata.Version) And(A1.VerNum = DVersData.Version) And(A2.VerNum = DVersData.Version))Can someone help me with this?Kind regards, Ils

Find more posts tagged with

Comments

Appu_Nair

This article gets the latest of a category applied version without using the dversdata tablehttps://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=3498953&objAction=ArticleView&viewType=1also the filter I would think would work hereSELECT DISTINCT A.ID FROM LLATTRDATA AWHERE A.ID = DTREE.dataid AndA.DEFID = 801739 AndA.ATTRID = 3 ) AS QAHDFROM DTREE where subtype=144START WITH dataid=812190 CONNECT BY PRIOR dataid=parentid) D, Here's an example that I use similary to filter objects that I wantcreate view [interlluser].[vInsuranceCategory](name,dataid, gen,auto,excess,workers,safety,contractor,region) as select t.name, a.id, a.valdate,b.valdate,c.valdate,d.valdate,e.valdate,f.valstr,g.valstr from LLAttrData a,llattrdata b,llattrdata c,llattrdata d,llattrdata e,llattrdata f,llattrdata g, DTree t where t.subtype=31067 and(t.DataID=a.ID and a.DefID=49898 and a.AttrID=13 and t.VersionNum=a.VerNum)and(t.DataID=b.ID and b.DefID=49898 and b.AttrID=14 and t.VersionNum=b.VerNum)and(t.DataID=c.ID and c.DefID=49898 and c.AttrID=15 and t.VersionNum=c.VerNum)and(t.DataID=d.ID and d.DefID=49898 and d.AttrID=16 and t.VersionNum=d.VerNum)and(t.DataID=e.ID and e.DefID=49898 and e.AttrID=18 and t.VersionNum=e.VerNum)and(t.DataID=f.ID and f.DefID=49886 and f.AttrID=17 and t.VersionNum=f.VerNum)and(t.DataID=g.ID and g.DefID=49889 and g.AttrID=58 and t.VersionNum=g.VerNum)and I use this to query my viewselect name,dataid,contractor,region,case when gen <='2009-01-19 00:00:00.000' then CONVERT(varchar(10),gen, 101) end "gen" ,case when auto <='2009-01-19 00:00:00.000' then CONVERT(varchar(10),auto, 101) end "auto",case when excess <='2009-01-19 00:00:00.000' then CONVERT(varchar(10),excess, 101) end "excess" ,case when workers <='2009-01-19 00:00:00.000' then CONVERT(varchar(10),workers, 101) end "workers",case when safety <='2009-01-19 00:00:00.000' then CONVERT(varchar(10),safety, 101) end "safety"from intermediate.interlluser.vinsurancecategory where gen <='2009-01-19 00:00:00.000'or auto <='2009-01-19 00:00:00.000'or excess <='2009-01-19 00:00:00.000'or workers <='2009-01-19 00:00:00.000'or safety <= '2009-01-19 00:00:00.000'order by region

Katia_Vermeulen

Thank you for your respons Appu.If I would use the code that you provided:SELECT DISTINCT A.ID FROM LLATTRDATA AWHERE A.ID = DTREE.dataid AndA.DEFID = 801739 AndA.ATTRID = 3 ) AS QAHDFROM DTREE where subtype=144START WITH dataid=812190 CONNECT BY PRIOR dataid=parentid) D, my output would only be documents.What I need in my output is all latest versions of the documents AND all generations.

Appu_Nair

so isnot subtype for genaertion pluggable in an in clause.I don't know what it is but I guees you could do this corrcetSELECT DISTINCT A.ID FROM LLATTRDATA AWHERE A.ID = DTREE.dataid AndA.DEFID = 801739 AndA.ATTRID = 3 ) AS QAHDFROM DTREE where subtype in (144,another one ,and another one)START WITH dataid=812190 CONNECT BY PRIOR dataid=parentid) D,

Katia_Vermeulen

Yes, that's what i thought before but the problem is that generations do not have versions so if I use the code this way I won't get any generations in my results.

Appu_Nair

That I did not know so is generation tied to a specific version of a livelink document ?Here's a relatively stupid way of finding it psuedo code.First find the documents and then union it with a second query for generations somehow would that work.Where all the LR SQL gurus ?

Katia_Vermeulen

Now I have two separate livereports.One for the generations and an other for the documents.I thought that here would be al least one LR SQL guru that could help me combining these two reports into one ;-)thank you Appu!!!!

Jim_Coursey

Message from Coursey, Jim <jim.coursey@ngc.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

A generation is tied to a specific version of a Livelink document. If you create a generation to a later version, it is a new generation, not a new version of the old one. This can be especially useful if a document is frequently updated throughout its life but only some versions are "Releassed" or "Baselined". You can keep the version history and audit trail with the one document while providing a separate generation for each release. If a new generation to a document supersedes an older generation to a previous version, the old generation can be deleted (if you don't have serious traceability requirements) or can be moved to an "Obsolete Documents" sub-folder. Anytime a user clicks on the old generation, they are served the exact version that was previously released.

Meantime, there is only one Livelink document in one location to provide version history and to serve as the basis of the next update. Whenever the versions are purged, the Released versions are retained since their generation locks those versions from being deleted. To delete one of the released versions, its generation must be deleted first. To delete the document from Livelink, all its generations must be deleted first.

Jim_Coursey

Message from Coursey, Jim <jim.coursey@ngc.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Can't you join the two queries to create one temporary table? I'm not a SQL practitioner, but I seem to remember that from a long ago class.

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, January 08, 2009 9:46 AM
To: eLink Recipient
Subject: two seperate livereports

two seperate livereports		Posted by genzymeuser20 (Vermeulen, Katia) on 2009/01/08 09:43
In reply to: That I did not know so is generation tied to a specific version of a livelink...		Posted by anair@alitek.com (Nair, Appu) on 2009/01/08 09:37

Now I have two separate livereports.
One for the generations and an other for the documents.

I thought that here would be al least one LR SQL guru that could help me combining these two reports into one ;-)

thank you Appu!!!!

Katia_Vermeulen

I don't know how to do that

Tim_Hunter

try using UNION ALL to combine the two queries, just make sure the columns matchselect col1, col2, col3 from sometableUNION ALLselect col1, col2, col3 from someothertable