Re: ISO: sql technique to select information from more than one table

From: Larry W. Virden <lvirden_at_gmail.com>
Date: Tue, 10 Mar 2009 09:48:59 -0700 (PDT)
Message-ID: <9fecd801-b9e7-44db-af47-95db3a5570bd_at_s31g2000vbp.googlegroups.com>

On Mar 10, 12:19 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> Larry W. Virden schreef:
> > The one thing that would be ideal is if I could 'merge' the outputs so
> > that instead of 2 reports, one after the other, I got
> > one report, with the 3 sets of names on one line.
>
> > I had expected that the union would do that.
>
> That's indeed what the union would do. Check for empty lines in your
> statement. There is one at the end, just before the ';'
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Ah - removing the blank line before the ; now actually causes the select to execute. However, what I get is two lines - the first column of the first line says "csi_security" followed by the csi_hr name and then the csi_security name. Then, 1348 lines later, there is a second line which says "csi_telecom" in the first column, followed by the csi_hr name and then the csi_telecom name.

The sql in question is

select 'csi_security' source,
        csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name,
        csi_security.first_name, csi_security.initials,

csi_security.last_name
from csi_hr,csi_security,csi_core
where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and

      csi_security.person_id=csi_core.sec_person_id and
      (upper(csi_hr.first_name) <> nvl(csi_security.first_name,'NOT
AVAIL')
      or upper(nvl(csi_hr.middle_name,'NOT AVAIL')) <> nvl

(csi_security.initials
,'NOT AVAIL')
      or upper(csi_hr.last_name) <> nvl(csi_security.last_name,'NOT AVAIL') )
union select 'csi_telecom' source,

        csi_hr.first_name, csi_hr.middle_name, csi_hr.last_name,
        csi_telecom.first_name, csi_telecom.middle_name,

csi_telecom.last_name
from csi_hr,csi_telecom,csi_core
where csi_hr.alt_emp_no=csi_core.hr_alt_emp_no and

      csi_telecom.person_id=csi_core.sec_person_id and
      (upper(csi_hr.first_name) <> nvl(csi_telecom.first_name,'NOT
AVAIL') or
      upper(nvl(csi_hr.middle_name,'NOT AVAIL')) <>
        nvl(csi_telecom.middle_name,'NOT AVAIL') or
      upper(csi_hr.last_name) <> nvl(csi_telecom.last_name,'Not

Avail') )
; Received on Tue Mar 10 2009 - 11:48:59 CDT