plsql

PRDEEP — Wed, 01 Apr 2009 07:47:38 GMT

Dear All,

I have following tables
Table - job_tran

JOB_NO varchar2(10)
SECTION varchar2(3)
TDATE date

table - Job_alloc

SURVEYOR varchar2(10)
JOB_NO varchar2(10)
ALLOC_DATE date

Data in Job_transfer
SQL> select * from job_tran;

JOB_NO SEC TDATE
---------- --- --------
5450 A 31-08-08
5450 B 31-08-08
5450 C 04-09-08
5450 Y 10-11-08
5450 Y 26-11-08
5450 Y 30-11-08

Data in Job_alloc
SQL> select * from job_alloc;

SURVEYOR JOB_NO ALLOC_DA
---------- ---------- --------
011 5450 31-08-08
011 5450 04-09-08
011 5450 10-11-08
011 5450 30-11-08

using plsql I need the query as follows:-

SURVEYOR JOB_NO SECTION
---------- -------- ---------
011 5450 Y
011 5450 Y
011 5450 Y

BUT RUN THE QUERY AS FOLLOWS:-

select surveyor,a.job_no,a.section from job_tran a, job_alloc b where a.job_no=b.job_no
and section='Y' and a.job_no='5450'

THE RESULT AS FOLLOWS:- multiple rows return.
SURVEYOR JOB_NO SEC
---------- ---------- ---
011 5450 Y
011 5450 Y
011 5450 Y
011 5450 Y
011 5450 Y
011 5450 Y
011 5450 Y
011 5450 Y
011 5450 Y
011 5450 Y
011 5450 Y

SURVEYOR JOB_NO SEC
---------- ---------- ---
011 5450 Y

USING ANOTHER QUERY AS FOLLOWS:-
select DISTINCT surveyor,a.job_no,a.section from job_tran a, job_alloc b where a.job_no=b.job_no
and section='Y' and a.job_no='5450'

THE RESULT IS ONLY ONE ROW.

SURVEYOR JOB_NO SEC
---------- ---------- ---
011 5450 Y

BUT USING THE ABOVE DATA I NEED THE REULT
AS FOLLOWS:-

SURVEYOR JOB_NO SECTION
---------- -------- ---------
011 5450 Y
011 5450 Y
011 5450 Y

could you Plase find a solution ?

pradeep

topic plsql in Operating System - HP-UX

plsql