Re: ISO: sql technique to select information from more than one table
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 10 Mar 2009 17:19:33 +0100
Message-ID: <49b692f1$0$198$e4fe514c_at_news.xs4all.nl>
Larry W. Virden schreef:
> 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.
Date: Tue, 10 Mar 2009 17:19:33 +0100
Message-ID: <49b692f1$0$198$e4fe514c_at_news.xs4all.nl>
Larry W. Virden schreef:
> 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.
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 Received on Tue Mar 10 2009 - 11:19:33 CDT