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 08:04:53 -0700 (PDT)
Message-ID: <a1402e89-c96c-4677-b2c2-b624e220aafe_at_n30g2000vba.googlegroups.com>



Okay, here's the next thing that I have learned. I tried taking the pieces of the select individually and executing them. When I did this, the first select produces output. However, the second part of the statement just stoped . interestingly enough, if I then type a / , the select occurs. So, I tried this version of my select, which I tweaked to handle an issue with upper and lower case.
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') )

;

sqlplus reads the above query and doesn't execute it. Then I type a / and it does execute it.

This brings me much closer to my desired output, even if I do have to mess around a bit in the sql script file.

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. Received on Tue Mar 10 2009 - 10:04:53 CDT

Original text of this message