Add attribute data into livereport but need include those without

Hi all,I have try to pull out the attribute data into the livereport. But those without the category will not display in the livereport. i need those document to be inside as well, anyone know how..select Dtree.*, a.ValStr "author", a.ID, a.AttrID from DTree, LLAttrData a where DTree.DataID=a.ID and a.AttrID=2 and DataID in (select DataID from DTree where Dtree.subtype=144) order by a.AttrID ascI was filter document in my query also..

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

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

Hi no name

Your current query 'joins' two tables DTree and LLAttrData (with an alias 'a') based on the join condition you specified which is DTree.DataID = a.ID

That means only rows from DTree with matching rows in LLAttrData will be returned (and vice versa).

Think of it as a Venn Diagram of two overlapping circles, returning the values in the intersection.

When you also want the rows from one side of the join (one table) which have no matching value in the other table, this is called an Outer Join.

For the Venn Diagram analogy, you now want all of once circle (the left or the right).

How you specify this depends upon the Relational Data Base system you use.

In Oracle this is simply done with (+) on the side where you want all rows.

So your join condition becomes

DTree.DataID(+) = a.ID

and the full statement is therefore

select Dtree.*, a.ValStr "author", a.ID, a.AttrID from DTree, LLAttrData a where DTree.DataID(+)=a.ID and a.AttrID=2 and DataID in (select DataID from DTree where Dtree.subtype=144) order by a.AttrID asc

This should also work in SQL server, but here the customary syntax would be more verbose

from table left outer join table2 on table1.id=table2.id

and the full statement would be

select Dtree.*, a.ValStr "author", a.ID, a.AttrID from DTree left outer join LLAttrData a on DTree.DataID=a.ID where a.AttrID=2 and DataID in (select DataID from DTree where Dtree.subtype=144) order by a.AttrID asc

Hope this helps and that it works for you.

Regards

Lindsay

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2010 March 04, Thu 01:57
To: eLink Recipient
Subject: Add attribute data into livereport but need include those without

Add attribute data into livereport but need include those without

Posted by dsta001 (dsta001, dsta001) on 2010/03/03 20:53

Hi all,

I have try to pull out the attribute data into the livereport. But those without the category will not display in the livereport. i need those document to be inside as well, anyone know how..

select Dtree.*, a.ValStr "author", a.ID, a.AttrID from DTree, LLAttrData a where DTree.DataID=a.ID and a.AttrID=2 and DataID in (select DataID from DTree where Dtree.subtype=144) order by a.AttrID asc

I was filter document in my query also..