Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL
Hi
I think you're right about the outer join. As I can see there is two problems
with your select:
1) The (+) is to be placed on "the other side": ....e.emp_id = ea.emp_id(+)...
2) You need and outer join here too: ... ea.prim_addr (+) = 'T' ...
The second outer join is neccesary, but notice that you might have to deal with
other values than 'T' in ea.prim_addr. This you could do like this in the
select-part of the query:
Select ......decode(nvl(ea.phone_nbr,'X'),'T',ea.phone_nbr,null)....
This would mean that other values than 'T' in ea.phone_nbr would result in a
null-value in the selected value.
Hope this helps.
Regards,
Michael Ringbo, DDE A/S
Doug Stone wrote:
> Hi. I'm having a problem getting the results I need from the SQL statement
> below.
>
> I believe the problem centers on the 'e.emp_id (+)= ea.emp_id' part of this
> statement.
>
> I want to return all employees that meet the specified WHERE requirements.
>
> However, the SQL below will only return all employees that meed the WHERE
> requirements AND have an address.
>
> So, any employee that does not have an address (which is optional), does not
> get returned.
>
> Any suggestions / ideas are appreciated.
>
> Begin SQL ********************
>
> SELECT e.emp_id, e.prim_group, e.prim_skill, e.prim_scode, e.prim_shift,
> e.union_id,
> e.ot_rule_id, e.salutation_id, e.first_name, e.middle_initial,
> e.last_name,
> e.emp_nbr, e.is_agency, e.sex, e.seniority_dt, e.restricted,
> e.seniority_hrs,
> e.emp_status_id, g.descr, sk.short_descr, sh.descr, ea.phone_nbr,
> u.descr
> FROM employee e, groups g, skill_level sk, shifts sh, emp_address ea,
> unions u
> WHERE e.prim_group = g.group_id AND
> e.prim_skill = sk.skill_lvl_id AND
> e.prim_shift = sh.shift_id AND
> e.union_id = u.union_id AND
> e.emp_id (+)= ea.emp_id AND
> e.emp_id > 0 AND
> ea.prim_addr = 'T' AND
> e.agency_id IN (:agencies)
> ORDER BY e.last_name, e.first_name ASC
>
> End SQL ***************************
Received on Tue Dec 15 1998 - 05:26:29 CST