Print a default page when data does not exist

[gritz]

I have an Oracle report whose query is grouped by a category. Sometimes there is no data for a particular category, and I would like to print a page that says there is no data for that category, rather than not printing a page at all for that category. Is there any way to do that in Oracle reports?

 

[SimonSellick]

You need to do this in the query rather than the report, because, assuming the report is based on a repeating frame and that frame is bound to a group from the query, then if the query doesn't return a particular group value, the report formatter won't know about that value.

Write your query so that it returns all categories, using a left outer join to get the details within each category. For example, instead of

SELECT c.code, c.name, d.desc, d.amount
FROM cat c, dtl d
WHERE c.code = d.cat

use

SELECT c.code, c.name, d.desc, d.amount
FROM cat c, dtl d(+)
WHERE c.code = d.cat

(I think that is correct syntax for an Oracle left join, but as I said it's been a while - look it up in the Oracle documentation if you don't already know it.)

Hope this helps.

 

[sem]

I don't agree with SimonSellick (BTW the syntax is also incorrect).
You may and should do it within report. You may create some grouping or calculated column to check whether data was found and then create flexible frame on the same level as your detail group. In format trigger you may check that grouping value and if it's not null (or not 0, depending on the way you use), i.e. data was found, don't print this frame.

Regards, Dima

 

[SimonSellick]

Sorry that my left join syntax was incorrect.

But how does the report know about the missing category? If the list of categories does not come from the query, then it has to be hard-coded in the report. Obviously the action taken for an empty category (no detail data) must be programmed into the report, but the categories certainly should not be, unless you want an unnecessary maintenance task when they change. The way to tell the report about all categories, empty or otherwise, is via the query. The way for the report to determine whether a category has detail data is to look for a null value in the target column of the left join.

 

[sem]

SimonSellick,
Master-detail report is normally based on 2 or more queries. If you know about execution plans, this is a strict analogue of nested loop. And in some cases detail query returns no rows.

Gritz, is my understanding correct?

Regards, Dima

 

[gritz]

The report is based on a single query which sums a value. It joins 2 tables, one of which contains all the categories that could possibly exist. The highest 'group by' field is category, and there are 4 other group by fields that follow it. Does this answer your question?

 

[sem]

Ok, in this case you may combine both advices.

Regards, Dima

 

[pelajhia]

btw:
SELECT c.code, c.name, d.desc, d.amount
FROM cat c, dtl d
WHERE c.code = d.cat(+)

 

[SimonSellick]

Thanks pelajhia. I knew it had a plus in it somewhere. I suppose Larry will sort out the ANSI join syntax eventually, but it doesn't work very well up to v8.