Getting random dups in my LiveReport results

Hi All - I'm running a livereport based on categories and on only SOME of the returned compounds doc records I'm getting dups...so stuck on this one and any help would be much appreciated!

My code below: %1 is user select "Assigned To" and %2 and %3 are my Date range

select distinct dtree.dataid "Doc ID",
dtree.versionnum "ver number",
kuaf.name "Assigned To",
c.valstr "Case Status",
g.valDate "Closed Date",
b.valstr "Case Amount",
m.valstr "Currency",
n.valstr "Currency Equivalent",
DTree.CreateDate "Create Date",
l.valDate "Transaction Date",
p.valstr + '-' + q.valstr + '-' + r.valstr as "Case Date",
dtree.Name "Document Name",
e.valstr "Investigation Source",
d.valstr "Investigation Type",
f.valstr "Investigation Reason",
h.valstr "Information Types",
j.valstr "Remitting Bank",
k.valstr "Swift Code",
o.valstr "Beneficiary Bank",
s.valDate "Case Note Modification Date"
from dtree, llattrdata a, LLAttrData b, LLAttrData c, LLAttrData d, LLAttrData e, LLAttrData f, LLAttrData g, LLAttrData h, LLAttrData i, LLAttrData j, LLAttrData k, LLAttrData l, LLAttrData m, LLAttrData n, LLAttrData o, LLAttrData p, LLAttrData q, LLAttrData r, LLAttrData s,kuaf
where (dtree.dataid = a.id and dtree.versionnum = a.vernum and a.defid = '4432692' and a.attrid = '13' and a.ValInt = kuaf.ID and a.ValInt = %1)
and (dtree.dataid = b.id and dtree.versionnum = b.vernum and b.defid = '4432692' and b.attrid = '8')
and (dtree.dataid = c.id and dtree.versionnum = c.vernum and c.defid = '4432692' and c.attrid = '14')
and (c.valstr = 'Close') and (dtree.dataid = d.id and dtree.versionnum = d.vernum and d.defid = '4432692' and d.attrid = '11')
and (dtree.dataid = e.id and dtree.versionnum = e.vernum and e.defid = '4432692' and e.attrid = '18')
and (dtree.dataid = f.id and dtree.versionnum = f.vernum and d.defid = '4432692' and f.attrid = '12')
and (dtree.dataid = g.id and dtree.versionnum = g.vernum and g.defid = '4432692' and g.attrid = '23')
and (dtree.dataid = h.id and dtree.versionnum = h.vernum and h.defid = '4432692' and h.attrid = '24')
and (dtree.dataid = j.id and dtree.versionnum = j.vernum and j.defid = '4432692' and j.attrid = '16')
and (dtree.dataid = k.id and dtree.versionnum = k.vernum and k.defid = '4432692' and k.attrid = '17')
and (dtree.dataid = l.id and dtree.versionnum = l.vernum and l.defid = '4432692' and l.attrid = '22')
and (dtree.dataid = m.id and dtree.versionnum = m.vernum and m.defid = '4432692' and m.attrid = '9')
and (dtree.dataid = n.id and dtree.versionnum = n.vernum and n.defid = '4432692' and n.attrid = '10')
and (dtree.dataid = o.id and dtree.versionnum = o.vernum and o.defid = '4432692' and o.attrid = '15')
and (dtree.dataid = p.id and dtree.versionnum = p.vernum and p.defid = '4432692' and p.attrid = '5')
and (dtree.dataid = q.id and dtree.versionnum = q.vernum and q.defid = '4432692' and q.attrid = '6')
and (dtree.dataid = r.id and dtree.versionnum = r.vernum and r.defid = '4432692' and r.attrid = '7')
and (dtree.dataid = s.id and dtree.versionnum = s.vernum and s.defid = '4775290' and s.attrid = '3')
and DTree.CreateDate < %3 and DTree.CreateDate > %2 and dtree.SubType = '136' order by dtree.Name, dtree.versionnum

Find more posts tagged with

Comments

Colin_Schmidt

That always has to do with multiple records being returned on one of the Joins. If they're dupes and you don't want them, you can limit them to 1 record being returned, but if it was me doing it, I'd break all these duplicated tables out into separate "joins" so that I could put at the end: "and rownum = 1".

Colin J.

On Thu, Feb 20, 2014 at 10:11 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Getting random dups in my LiveReport results Posted bytportman@theprivatebank.com (Portman, Tiffany)On 02-20-2014 10:01

Hi All - I'm running a livereport based on categories and on only SOME of the returned compounds doc records I'm getting dups...so stuck on this one and any help would be much appreciated! My code below: %1 is user select "Assigned To" and %2 and %3 are my Date range select distinct dtree.dataid "Doc ID",
dtree.versionnum "ver number",
kuaf.name "Assigned To",
c.valstr "Case Status",
g.valDate "Closed Date",
b.valstr "Case Amount",
m.valstr "Currency",
n.valstr "Currency Equivalent",
DTree.CreateDate "Create Date",
l.valDate "Transaction Date",
p.valstr + '-' + q.valstr + '-' + r.valstr as "Case Date",
dtree.Name "Document Name",
e.valstr "Investigation Source",
d.valstr "Investigation Type",
f.valstr "Investigation Reason",
h.valstr "Information Types",
j.valstr "Remitting Bank",
k.valstr "Swift Code",
o.valstr "Beneficiary Bank",
s.valDate "Case Note Modification Date"
from dtree, llattrdata a, LLAttrData b, LLAttrData c, LLAttrData d, LLAttrData e, LLAttrData f, LLAttrData g, LLAttrData h, LLAttrData i, LLAttrData j, LLAttrData k, LLAttrData l, LLAttrData m, LLAttrData n, LLAttrData o, LLAttrData p, LLAttrData q, LLAttrData r, LLAttrData s,kuaf
where (dtree.dataid = a.id and dtree.versionnum = a.vernum and a.defid = '4432692' and a.attrid = '13' and a.ValInt = kuaf.ID and a.ValInt = %1)
and (dtree.dataid = b.id and dtree.versionnum = b.vernum and b.defid = '4432692' and b.attrid = '8')
and (dtree.dataid = c.id and dtree.versionnum = c.vernum and c.defid = '4432692' and c.attrid = '14')
and (c.valstr = 'Close') and (dtree.dataid = d.id and dtree.versionnum = d.vernum and d.defid = '4432692' and d.attrid = '11')
and (dtree.dataid = e.id and dtree.versionnum = e.vernum and e.defid = '4432692' and e.attrid = '18')
and (dtree.dataid = f.id and dtree.versionnum = f.vernum and d.defid = '4432692' and f.attrid = '12')
and (dtree.dataid = g.id and dtree.versionnum = g.vernum and g.defid = '4432692' and g.attrid = '23')
and (dtree.dataid = h.id and dtree.versionnum = h.vernum and h.defid = '4432692' and h.attrid = '24')
and (dtree.dataid = j.id and dtree.versionnum = j.vernum and j.defid = '4432692' and j.attrid = '16')
and (dtree.dataid = k.id and dtree.versionnum = k.vernum and k.defid = '4432692' and k.attrid = '17')
and (dtree.dataid = l.id and dtree.versionnum = l.vernum and l.defid = '4432692' and l.attrid = '22')
and (dtree.dataid = m.id and dtree.versionnum = m.vernum and m.defid = '4432692' and m.attrid = '9')
and (dtree.dataid = n.id and dtree.versionnum = n.vernum and n.defid = '4432692' and n.attrid = '10')
and (dtree.dataid = o.id and dtree.versionnum = o.vernum and o.defid = '4432692' and o.attrid = '15')
and (dtree.dataid = p.id and dtree.versionnum = p.vernum and p.defid = '4432692' and p.attrid = '5')
and (dtree.dataid = q.id and dtree.versionnum = q.vernum and q.defid = '4432692' and q.attrid = '6')
and (dtree.dataid = r.id and dtree.versionnum = r.vernum and r.defid = '4432692' and r.attrid = '7')
and (dtree.dataid = s.id and dtree.versionnum = s.vernum and s.defid = '4775290' and s.attrid = '3')
and DTree.CreateDate < %3 and DTree.CreateDate > %2 and dtree.SubType = '136' order by dtree.Name, dtree.versionnum
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Chris Meyer

Hi Tiffany: Multi-valued attributes are spanned over multiple rows in the LLAttrData table. This is likely the cause of the seemingly duplicate rows (the rows are slightly different, but will have a lot of overlap). You may need to modify your query to take multi-valued attributes into account. Good luck!