SQL Existence Checking

Sam Baugh

SQL Existence Checking
Just for fun:

SELECT VALUE((SELECT 'ROW EXISTS'
FROM SYSIBM.SYSDUMMY1
WHERE IBMREQD = 'N'
),'ROW DOES NOT EXIST')
FROM SYSIBM.SYSDUMMY1
WITH UR

________________________________
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Mark Vickers
Sent: Wednesday, December 03, 2008 10:59 AM
To: [login to unmask email]
Subject: Re: [DB2-L] SQL Existance Checking


or SELECT "ROW DOES EXIST" FROM ...


"Grainger, Phil" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

12/03/2008 10:04 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To

[login to unmask email]

cc



Subject

Re: [DB2-L] SQL Existance Checking










In the interests of readability then, shouldn't we advocate

SELECT "DOES ROW EXIST?" FROM ..........

Phil G
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Peter Backlund
Sent: Wed 03/12/2008 12:29
To: [login to unmask email]
Subject: Re: [DB2-L] SQL Existance Checking


Just for fun, in my education I used to tell them to use "select 1 ..." in exists clauses.
One of my students asked "Doesn't that return the first column?" :-(

Since then I am always using "Select 0 ..."

Best regards,

Peter

Peter Vanroose wrote:

"SELECT *" in the subquery could (and should?) indeed be replaced by "SELECT
1", for readability purposes: that would make it clearer for the reader that
not data columns need to be obtained.

As to the two suggested alternatives (using SYSIBM.SYSDUMMY1 and using FETCH
FIRST 1 ROW ONLY) for the outer query: I see one good reason why your vendor
did not use one of these "standard DB2" solutions: thy are both
DB2-specific, while his solution is portable (to other SQL platforms).

-- Peter Vanroose
ABIS Training & Consulting.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG..ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

________________________________



No virus found in this incoming message.
Checked by AVG - http://www.avg.com < http://www.avg.com/ >
Version: 8.0.176 / Virus Database: 270.9.13/1825 - Release Date: 2008-12-02 20:44




--

Attend IDUG 2009, North America -- 11-15 May in Denver, Colorado
Attend IDUG 2009, Europe -- 5- 9 October in Rome, Italy

Learn more at http://www.idug.org < http://www.idug.org/ >


+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Rasundavagen 94 | Country Code (Sweden): 46 |
| S-169 57 SOLNA | Skype: BacklundDB2 |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+

________________________________


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms



This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.





________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information<http://www.idug.org/lsconf>, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services< http://www.idug.org/lsms >



-----------------------------------------

The information in this communication, including any attachments,
is intended only for the person or entity to which it is addressed
and may contain confidential, proprietary, and/or privileged
material. Any review, retransmission, dissemination or other use
of, or taking of any action in reliance upon, this information by
persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the
sender and delete the material from all computers.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms