Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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.orgReceived on Tue Aug 21 2007 - 10:38:11 CDT
![]() |
![]() |