Monday, 16 July 2012

How the ALL keyword works in oracle

Multiset operators combine the results of two nested tables into a single nested table.
The examples related to multiset operators require that two nested tables be created and loaded with data as follows:
First, make a copy of the oe.customers table called customers_demo. We will add the nested table columns to customers_demo.

CREATE TABLE customers_demo AS
  SELECT * FROM customers;
Simple Example :

Condition               Equivalent expression
x > ALL (12)          x > 2
x < ALL (12)          x < 1
x = ALL (12)          (x = 1AND (x = 2)
x <> ALL (12)         (x <> 1AND (x <> 2)


SELECT  * FROM [table_name]
 WHERE  abc_cd < ALL (0097,0098)

Output :

It shows all abc_cd below 97,98


OR

SELECT BankerName, BillingNumber, BillingTotal FROM Billings JOIN Bankers ON Billings.BankerID = Bankers.BankerID
WHERE BillingTotal > ALL
(SELECT BillingTotal
FROM Billings
WHERE BankerID = 34)


Its very simple....try it...

No comments:

Post a Comment