[DB2 V8 z/OS] Nice piece of SQL

Walter Janißen

[DB2 V8 z/OS] Nice piece of SQL
Hi

I want to share with you a nice little piece of SQL, to identify numbers,
which are not stored in a given table:

WITH N (NUMBER) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT NUMBER + 1
FROM N INNER JOIN SYSIBM.SYSDUMMY1
ON 1 = 1
WHERE NUMBER < (SELECT MAX(STRATEGY)
FROM DB2.ISTB0249
)
)
SELECT NUMBER
FROM N
WHERE NUMBER NOT IN
(SELECT STRATEGY
FROM DB2.ISTB0249
)
ORDER BY 1

Watch out for the end-condition, otherwise this query will run forever.
Hope you enjoy this SQL.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Peter Vanroose

Re: Nice piece of SQL
(in response to Walter Janißen)
--- Walter Janißen <[login to unmask email]> wrote:

> WITH N (NUMBER) AS
> (SELECT 1 FROM SYSIBM.SYSDUMMY1
> UNION ALL
> SELECT NUMBER + 1
> FROM N INNER JOIN SYSIBM.SYSDUMMY1
> ON 1 = 1
> WHERE NUMBER < (SELECT MAX(STRATEGY)
> FROM DB2.ISTB0249
> )
> )
> SELECT NUMBER
> FROM N
> WHERE NUMBER NOT IN
> (SELECT STRATEGY
> FROM DB2.ISTB0249
> )
> ORDER BY 1

Why the inner join?
The following should give the same result:

WITH N (NUMBER) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT NUMBER + 1 FROM N
WHERE NUMBER < (SELECT MAX(STRATEGY)-1
FROM DB2.ISTB0249
)
)
SELECT NUMBER
FROM N
WHERE NUMBER NOT IN
(SELECT STRATEGY
FROM DB2.ISTB0249
)
ORDER BY 1


-- Peter Vanroose
ABIS, Belgium.





_________________________________________________________
Flyger tiden iväg? Fånga dagen med Yahoo! Mails inbyggda
kalender. Dessutom 250 MB gratis, virusscanning och antispam. Få den på: http://se.mail.yahoo.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: Nice piece of SQL
(in response to Peter Vanroose)
IMO, NOT EXISTS in the subquery performs still
a little bit better. See below.

Kind regards

Bernd


WITH N (NUMBER) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT NUMBER + 1 FROM N
WHERE NUMBER < (SELECT MAX(STRATEGY)-1
FROM DB2.ISTB0249)
)
SELECT NUMBER
FROM N
WHERE not exists
(SELECT 1
FROM DB2.ISTB0249
where strategy = n.number)
ORDER BY 1



Am Dienstag, 5. Dezember 2006 19:31 schrieben Sie:
> --- Walter Janißen <[login to unmask email]> wrote:
> Why the inner join?
> The following should give the same result:
>
> WITH N (NUMBER) AS
> (SELECT 1 FROM SYSIBM.SYSDUMMY1
> UNION ALL
> SELECT NUMBER + 1 FROM N
> WHERE NUMBER < (SELECT MAX(STRATEGY)-1
> FROM DB2.ISTB0249
> )
> )
> SELECT NUMBER
> FROM N
> WHERE NUMBER NOT IN
> (SELECT STRATEGY
> FROM DB2.ISTB0249
> )
> ORDER BY 1
>
>
> -- Peter Vanroose
> ABIS, Belgium.
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: Nice piece of SQL
(in response to Bernd Oppolzer)
You are right, Peter. Even better. May be sometime the NOT IN should be
changed to a NOT EXISTS. Here explain data looks better for NOT EXISTS than
for NOT IN, but the elapsed time is nearly the same.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: Nice piece of SQL
(in response to Walter Janißen)
Peter

To be 100% correct, you do not have to subtract 1 from the max, because the
max must be in the table. And further more, I think the ORDER BY is
redundant, because the numbers are generated in the rigth sequence and
because there is no index for N, list prefetch can't be used.

WITH N (NUMBER) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT NUMBER + 1 FROM N
WHERE NUMBER < (SELECT MAX(STRATEGY)
FROM DB2.ISTB0249
)
)
SELECT NUMBER
FROM N
WHERE NUMBER NOT IN
(SELECT STRATEGY
FROM DB2.ISTB0249
)

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm