Re: Finding period NOT in a table

Neff wrote:
> Is there an easy way to find which of a set of numbers isn't in a
> table? I'm trying to find financial periods that are missing from a
> set of data. I can do it if I have a table which just contains the
> valid period numbers 1- 12 in the following way:
>
> SELECT
> period_num
> FROM
> valid_periods
> WHERE
> period_num NOT IN (SELECT DISTINCT data_period FROM data_table)
>
> But is there a simple way to do it without the valid_periods table?

To do this you must have some means of defining what are valid periods. One way would be with a table. The other would be by outer joining to a pipelined table function that generates them on-the-fly.

For an example of this, using dates, go to Morgan's Library at www.psoug.org, look up Pipelined Table Functions and then scroll down to "Generate Date List."

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org