Say I have following table A:

A.A     index     Primary key (int)
A.B     Name    Char(20)
A.C     Date      DateTime

With
        SELECT B, C from A
            GROUP BY A.C
            ORDER BY A.C

I'll get a list sorted on dates. My problem is that I want a record set
returned based on dates that doesn't exists in the record set returned from
the select. I.e. the select returns:

        1    Team1   01.02.2001
        2    Team1   02.02.2001   <-- Missing 03.02.2001
        3    Team1   04.02.2001
        4    Team2   01.02.2001   <-- Missing 02.02.2001
        5    Team2   03.02.2001
        6    Team2   04.02.2001

I want a recordset that returns

        Team1    03.02.2001
        Team2    02.02.2001

Any help would be appreciated

Stein-Bjarne,

You can't get something which doesn't exist!

With that in mind, you could build a record set with all possible
dates.  Then select from that record set anything which is not in A.C.
For the dates record set, you could use a common table exception such
as:

WITH ALLDATES (ONEDAY) AS (
   SELECT DATE('2/1/2001')
   FROM SYSIBM.SYSDUMMY1
   UNION ALL
   SELECT A.ONEDAY + 1 DAY
   FROM ALLDATES A
   WHERE ONEDAY < DATE('2/5/2001')
)
SELECT ONEDAY FROM ALLDATES
   WHERE ONEDAY NOT IN (SELECT C FROM A WHERE B = 'Team1');

Of course, you can modify this by using the beginning and ending date
for Team 1, such as:

WITH ALLDATES (ONEDAY) AS (
   SELECT MIN(C)
   FROM A
   UNION ALL
   SELECT A.ONEDAY + 1 DAY
   FROM ALLDATES A
   WHERE ONEDAY < (SELECT MAX(C) FROM A)
)
SELECT ONEDAY FROM ALLDATES
   WHERE ONEDAY NOT IN (SELECT C FROM A WHERE B = 'Team1');

You can get even fancier with this, but you get the idea.

--
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.

====================================

SQL is designed to show data that you have, not data that is missing.
However, you can get the result you want by creating an additional
(temporary) table that contains one row for every date in the period you
care about. For instance, if you are interested in all of the year 2001, you
can create a table that contains each of the dates in 2001, such as:
01.01.2001
02.01.2001
03.01.2001 etc. thru 31.12.2001.

If you call that table DATE_TABLE and its only column DATE_COLUMN, you can
revise your query as follows so that it says:
         SELECT B, C from A
         WHERE C NOT IN (SELECT DATE_COLUMN FROM DATE_TABLE WHERE
DATE_COLUMN IS NOT NULL)
             GROUP BY A.C
             ORDER BY A.C

This should produce the desired results. (Don't forget the WHERE DATE_COLUMN
IS NOT NULL part of the subquery; although you should never get put nulls in
the DATE_COLUMN in the first place, if you inadvertently do put a null in
the column, the WHERE clause will keep your query from returning an empty
set.)

Rhino

Oops, HTML garbage. Please post in plain text.

Ah but logically the values do exists. They are specifed by the datatype
definition, being as it is a set of values...
However, it's true that  the SQL standard doesn't mandate an enumeration
function for every data type. Therefore you'll need to create your own
functions. E.g.

CREATE FUNCTION ENUM.DATE () RETURNS TABLE (D DATE ) LANGUAGE SQL CONTAINS
          SQL NO EXTERNAL ACTION DETERMINISTIC RETURN
WITH A (D) AS (
   VALUES DATE('2001-01-01')
   UNION ALL
   SELECT A.D + 1 DAY
   FROM A
   WHERE D < DATE('2005-01-01')
) SELECT D FROM A

The above should really return all DB2 date values (3.65 million or so)
but limiting the range will let the thing run in a reasonable time

Paul,

Yes, the dates "logically exist".  But they do not PHYSICALLY exist in
his database.

That's why I posted code to create the physical values, and a way to
select from that table.  It didn't need an additional function.

--
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.

====================================

OK. But I claim that it is a better design to 'physically create' such
values using table functions.

The alternative is a 1-attribute table for each data type in your model
containing all the values of the type.

Logically, the two options are very similar, but the former has the
advantage of not cluttering up your datamodel with lots of redundant
foreign keys, doesn't have the habit of degenerating into 'code' tables
when used by the relationally challenged and if SQL ever properly supports
Domains, the former would be a good candidate for having direct DBMS
support and optimisation.

Regards.
Paul Vernon
IBM Global Services