Re: Dev2000 - Report designer problem/question
Date: Sat, 17 Oct 1998 22:59:04 GMT
Message-ID: <36291ab6.4828054_at_news.siol.net>
On Sat, 17 Oct 1998 14:49:04 -0400, "Andrew Hendrickson" <ahendric_at_tampabay.rr.com> wrote:
>I will try to restate the problem in a clearer fashion.
>
>I have a situation that in sqlplus I would write as an outer join with a NVL
>on the child count column.
>
>In the report (due to the size of the tables and complexity) I chose to
>write two seperate queries. One for the parent record and another for the
>child. In the data model editor I data linked them by the key. What I want
>to have happen is when the child query returns no data then I want to
>display a zero. I have been unable to do this. For some reason when the
>data link doesn't exist between the two queries then nothing is done for the
>child query. So what I get is a parent row of data and a blank for the
>child.
If I understand you correctly, your child query looks something like:
SELECT c.col1 FROM child c
WHERE c.key = :p_key;
where :p_key is a join-key value returned by the parent query. When there is no matching row in a child table, this query returns no rows, but you would like it to display a row with '0' in it.
So why don't you use the outer join just like you would in sql*plus (as you mentioned)? Something like:
SELECT NVL(c.col1,0) FROM child c, parent p
WHERE c.key(+) = p.key
AND p.key = :p_key;
Is there some special reason why you can't use an outer join?
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
[Quoted] The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Oct 18 1998 - 00:59:04 CEST