Re: Finding period NOT in a table

On Aug 21, 10:14 am, Neff <fgpsm..._at_gmail.com> 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?

If you are looking for a simple way to do this without using a table, something like this might work:
SELECT
  ROWNUM PERIOD_NUM
FROM
  DUAL
CONNECT BY
  LEVEL<=12;

The above generates a quick set of numbers from 1 to 12. You can then join that set of numbers to your table, like this:   PERIOD_NUM
FROM
  (SELECT
    ROWNUM PERIOD_NUM
  FROM
    DUAL
  CONNECT BY
    LEVEL<=12) VP,
  (SELECT DISTINCT
    DATA_PERIOD
  FROM
    DATA_TABLE) DT
WHERE
  VP.PERIOD_NUM=DT.DATA_PERIOD(+)
  AND DT.DATA_PERIOD IS NULL; An outer join is used between the two inline views, retrieving only those PERIOD_NUM values that cannot be found in the DATA_PERIOD table (specified by the AND DT.DATA_PERIOD IS NULL predicate).

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Aug 21 2007 - 11:40:39 CDT