topic Re: PL/SQL query in Operating System - HP-UX

PL/SQL query

PRDEEP — Tue, 24 Mar 2009 09:59:15 GMT

Dear all,

I have two tables JOb_master and
Job_allocation.
Job_master columns
JOB_NO
SECTION
ISSUE_DATE

Job_alloction columns

JOB_NO
ALLOCATION_DT
SURVEYOR

Using the two tables I need the stament like
Surveyor,job_no,section

for this I wrote the query like
select surveyor,a.job_no,section from job_master a,job_allocation b where a.job_no in (select job_no from job_allocation) and a.job_no=b.job_no;

but it return duplicate rows,as per howmany times the job_no in job_allocation table.
could you plese find a solution.

Regards,
Pradeep

Re: PL/SQL query

T G Manikandan — Tue, 24 Mar 2009 10:31:29 GMT

select a.SECTION,a.JOB_NO,b.SURVEYOR from JOB_MASTER a, JOB_ALLOCATION b where a.JOB_NO=b.JOB_NO;

Re: PL/SQL query

Xiaogang Zheng — Tue, 24 Mar 2009 15:08:51 GMT

Hi Pradeep,

Because the the JOB_NO is unique on Job_master (I believe it is the primary key on master table) and multi rows for each JOB_NO on Job_allocation, you will get duplicate rows definitly.

use the following:
select distinct surveyor,a.job_no,section from job_master a,job_allocation b where a.job_no=b.job_no;

Regards,
Xiaoang

Re: PL/SQL query

Sajjad Sahir — Wed, 25 Mar 2009 06:31:41 GMT

Dear Pradeep

select surveyor,a.job_no,section from job_master a,job_allocation b where
a.job_no=b.job_no;

I think this query is sufficient
one is primary key and second one is foriegn
key both are related through constraint mechanism

thanks and regards

Sajjad Sahir

Re: PL/SQL query

PRDEEP — Sun, 29 Mar 2009 04:42:12 GMT

Thanks all of you,
but I got the same result as before,
and using distinct it return only one row.
For your reference I give the data as follows.
In Job Master table data as follows:-
Job_no Section Issue_date

5450 A 31-AUG-08
5450 B 31-AUG-08
5450 P 04-SEP-08
5450 Y 04-NOV-08
5450 Y 26-NOV-08
5450 A 30-NOV-08
5450 P 10-NOV-08

Job_allocation table

Job_no Allocation _date Surveyor

5450 04-NOV-08 1233445
5450 26-NOV-08 1233445

I need the result as follows

Surveyor Job_no Section

1233445 5450 Y
1233445 5450 Y

while run the following query
select b.SURVEYOR,a.JOB_NO, a.SECTION from JOB_MASTER a, JOB_ALLOCATION b where a.JOB_NO=b.JOB_NO and section='Y';

It give wrong result or duplicate rows.

Re: PL/SQL query

Frank de Vries — Mon, 06 Apr 2009 05:24:55 GMT

You can always take the duplicates out, by using the 'shadow' column rowid

eg.

create table owner.master_new
as
select * from owner.master x1
where x1.rowid <> (select max(x2.rowid) from history x2
where x1.ci_item = x2.ci_item)

If you want it to transpose to your concrete situation let us know