| How to apply outer join condition for 'IN' operator [message #103711] |
Tue, 24 February 2004 21:51  |
M.Chandra mouli
Messages: 4
Registered: February 2004
|
Junior Member |
|
|
How to apply outer join condition for 'IN' operator. In the given below query how to apply outer join at 'IN' operator.
SELECT em.esc_code, em.esc_name, em.esc_desc, es.hours, es.esc_id, es.usr_id
FROM escalation_mstr em, escalations es
WHERE em.esc_code = es.esc_code(+) AND em.esc_st = 'Y' AND comp_id(+) = 'BAA0007'
and usr_id in (select usr_id from usr_mstr where comp_id='BAA0006')
|
|
|
|
| Re: How to apply outer join condition for 'IN' operator [message #103749 is a reply to message #103711] |
Thu, 04 March 2004 08:20  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
In-line views can usually circumvent ORA-01719 errors.SELECT em.esc_code
, em.esc_name
, em.esc_desc
, es.hours
, es.esc_id
, es.usr_id
FROM escalation_mstr em
, escalations es
, (SELECT
DISTINCT um.usr_id
FROM usr_mstr um
WHERE um.comp_id = 'BAA0006') um6
WHERE em.esc_code = es.esc_code (+)
AND em.esc_st = 'Y'
AND 'BAA0007' = comp_id (+)
AND usr_id = um6.usr_id (+)
/
01719, 00000, "outer join operator (+) not allowed in operand of OR or IN"
// *Cause: An outer join appears in an or clause.
// *Action: If A and B are predicates, to get the effect of (A(+) or B),
// try (select where (A(+) and not B)) union all (select where (B)).
HTH,
A.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
