Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
What is EXIST, ANY, ALL in subqueries

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
fayum

New User


Joined: 27 Feb 2004
Posts: 2
Location: chennai

PostPosted: Wed Mar 03, 2004 4:41 pm    Post subject: What is EXIST, ANY, ALL in subqueries
Reply with quote

1. pls let me known about the maximum no of columns& rows that can created in DB2 icon_question.gif

2. explain in detail about exist ,Any ,All in subqueries icon_idea.gif .
Back to top
View user's profile Send private message

Ananya

New User


Joined: 26 Feb 2004
Posts: 19
Location: Chennai

PostPosted: Thu Mar 04, 2004 10:05 am    Post subject: Exists-ALL-Any
Reply with quote

Hi there icon_rolleyes.gif

1. Max no of Cols and Rows in a DB2 Table.


the maximum number of columns in a table and the maximum byte size of a row in a table are determined by the page size.

Page Size Row Size Limit Column Count Limit

4K 4 005 500
8K 8 101 1 012
16K 16 239 1 012
32K 32 677 1 012


2. Exists, Any, All

You can use a subquery to test for the existence of a row that satisfies some condition. In this case, the subquery is linked to the outer-level query by the predicate EXISTS or NOT EXISTS.

When you link a subquery to an outer query by an EXISTS predicate, the subquery does not return a value. Rather, the EXISTS predicate is true if the answer set of the subquery contains one or more rows, and false if it contains no rows.

The EXISTS predicate is often used with correlated subqueries. The example below lists the departments that currently have no entries in the PROJECT table:

SELECT DEPTNO, DEPTNAME
FROM DEPARTMENT X
WHERE NOT EXISTS
(SELECT *
FROM PROJECT
WHERE DEPTNO = X.DEPTNO)
ORDER BY DEPTNO

You may connect the EXISTS and NOT EXISTS predicates to other predicates by using AND and OR in the WHERE clause of the outer-level query.



Quantified Predicates All & Any
A quantified predicate compares a value with a collection of values. If a fullselect returns more than one value, you must modify the comparison operators in your predicate by attaching the suffix ALL, ANY, or SOME. These suffixes determine how the set of values returned is to be treated in the outer-level predicate. The > comparison operator is used as an example (the remarks below apply to the other operators as well):


expression > ALL (fullselect)
The predicate is true if the expression is greater than each individual value returned by the fullselect. If the fullselect returns no values, the predicate is true. The result is false if the specified relationship is false for at least one value. Note that the <>ALL quantified predicate is equivalent to the NOT IN predicate.
The following example uses a subquery and a > ALL comparison to find the name and profession of all employees who earn more than all managers:

SELECT LASTNAME, JOB
FROM EMPLOYEE
WHERE SALARY > ALL
(SELECT SALARY
FROM EMPLOYEE
WHERE JOB='MANAGER')


expression > ANY (fullselect)
The predicate is true if the expression is greater than at least one of the values returned by the fullselect. If the fullselect returns no values, the predicate is false. Note that the =ANY quantified operator is equivalent to the IN predicate.

expression > SOME (fullselect)
SOME is synonymous with ANY.



Hope this Helps !!!

Cheers
-Ananya
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fixed output from delimited input, fi... madmartinsonxx DFSORT/ICETOOL 3 Thu Mar 17, 2011 2:53 am
No new posts to check file/dataset exist or not radhakrishnan82 JCL & VSAM 7 Sat Mar 05, 2011 6:04 pm
No new posts B204 SQL - DB2 subsystem "DB2Q&q... mugil CA Products 2 Wed May 19, 2010 3:38 pm
No new posts ChangeMan package already exist ? + Rexx sunil kumar purohit All Other Mainframe Topics 9 Tue Apr 06, 2010 6:55 pm
No new posts VSAM Data and Index part exist withou... nartcr JCL & VSAM 2 Thu Nov 12, 2009 11:03 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us