This query returns the three stratum/treatment combinations that have rows for them, but does not return the one combination that has no rows. I've tried using an NVL on the count but to no avail—it just seems to stop processing once it determines that there are no rows for a category. Is there any way to get it to report the zero row counts?

Yes, you simply need to write the LEFT OUTER JOIN query in the other direction (as it were). Your query, with the old-style outer joins using the "(+)" notation, ostensibly allows for rand_schedule rows to exist without matching rows in the strata or treatment_groups tables, which of course would never happen if foreign keys were being enforced.

The CROSS JOIN first obtains all combinations of strata and treatment_groups. Then, for each of these combinations, an attempt is made, using the LEFT OUTER JOIN, to find matching rows of the rand_schedule table. Where no such rows exist, of course, NULLs are returned for that combination. Thus, when the COUNT aggregate function attempts to count them, the NULLs aren't counted, and so the result is a count of zero.

Start the conversation

0 comments

Register

I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.