My friend wanted to use IN command to check the value from a table used in an outer join but Oracle does not allow the usage of IN command for tables connected by outer join. I used regular expressions to overcome this.
Here is an example below with 2 tables (Main and Sometime):
a. Table Main with 1 column 'ID'
ID
10
20
30
b. Table Sometime with 2 columns, ID and Status
ID STATUS
20 ALIVE
10 DEAD
SELECT m.id , s.status
FROM main m, sometime s
WHERE m.id = s.id (+)
AND REGEXP_LIKE (s.status(+), 'ALIVE|DEAD');
The SQL query above would return the following result:
ID STATUS
20 ALIVE
10 DEAD
30
Wednesday, September 23, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment