Re: Count(*)ing nested tables

John Currier wrote: 
I'm doing a simple "select count(*) from whatever".  I've modified the
code to not fail when it can't determine the count, but is there
anything more I can do?

When you mention * in the select-list of a table with nest tables, it tries to expand all the fields in nested table(s) too, which results in a messy multi-dimensional result set for each row, and seems to be ambiguous in the context of COUNT().

I don't have any direct experience with nested tables, but I'm supposing that you could try "SELECT COUNT(1) FROM WHATEVER". That should be a non-NULL value for each row of the non-nested table, without attempting to reference any of the nested tables within it. The value of 1 is arbitrary; any other non-NULL constant value would work equally well, since we're only counting them.

You could probably also do COUNT(pk) where pk is the name of a non-NULL column in the table. But for when you don't know the name of a non-NULL column (if any exists), stick with COUNT(1).

Also read this nice article on Oracle nested tables if you want to learn more:

http://www.dizwell.com/html/nested_tables.html

Regards,
Bill K.
.

Relevant Pages