Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL

Re: Help with SQL

From: Michael Ringbo <mri_at_dde-nospam.dk>
Date: Tue, 15 Dec 1998 12:26:29 +0100
Message-ID: <36764764.1013B50E@dde-nospam.dk>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US