Find posts in DB2 tables that does not exist 
Author Message
 Find posts in DB2 tables that does not exist

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



Fri, 07 May 2004 17:48:15 GMT
 Find posts in DB2 tables that does not exist

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.

Quote:

> 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

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

Jerry Stuckle
JDS Computer Training Corp.

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



Sat, 08 May 2004 00:43:06 GMT
 Find posts in DB2 tables that does not exist


Quote:
> 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

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



Sat, 08 May 2004 02:08:05 GMT
 Find posts in DB2 tables that does not exist


Quote:

> <br><font size=1 face="sans-serif">&gt;&gt; &nbsp;</font><font size=2 face="Courier New">You can't get something which doesn't exist!</font>
> <br>
> <br><font size=1 face="sans-serif">Ah but logically the values do exists. They are specifed by the datatype definition, being as it is a set of values...</font>
> <br><font size=1 face="sans-serif">However, it's true that &nbsp;the SQL standard doesn't mandate an enumeration function for every data type .</font>
> <br>
> <br>

Oops, HTML garbage. Please post in plain text.


Mon, 10 May 2004 02:27:45 GMT
 Find posts in DB2 tables that does not exist

Quote:
>>  You can't get something which doesn't exist!

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



Mon, 10 May 2004 04:21:08 GMT
 Find posts in DB2 tables that does not exist
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.

Quote:

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

> 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

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

Jerry Stuckle
JDS Computer Training Corp.

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



Wed, 12 May 2004 00:07:56 GMT
 Find posts in DB2 tables that does not exist
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



Sat, 15 May 2004 21:01:03 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. db2 7.1 W2k: SQL1585N A system temporary table space with sufficient page size does not exist

2. Why DBCC Can Not Find An Existing Table!?

3. Why DBCC Can Not Find An Existing Table!?

4. How to find out records not exist in one table from another through QBE

5. How To Find Records Existing In One table but not in Another

6. Progress 9.0B Not finding tables that exist

7. 2nd Post -- SQL Server does not exist or access denied - Help

8. where EXISTS / NOT EXISTS do not work (I am really pannic)

9. EXISTS/NOT EXISTS vs. IN/NOT IN

10. Another Data source not found posting

11. verifing whether a table with same name exist or not before creating new table

12. Select only rows from one Table which do not exist in another table


 
Powered by phpBB® Forum Software