- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- PL/SQL query
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-24-2009 02:59 AM
тАО03-24-2009 02:59 AM
PL/SQL query
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-24-2009 03:31 AM
тАО03-24-2009 03:31 AM
Re: PL/SQL query
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-24-2009 08:08 AM
тАО03-24-2009 08:08 AM
Re: PL/SQL query
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-24-2009 11:31 PM
тАО03-24-2009 11:31 PM
Re: PL/SQL query
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-28-2009 09:42 PM
тАО03-28-2009 09:42 PM
Re: PL/SQL query
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-05-2009 10:24 PM
тАО04-05-2009 10:24 PM
Re: PL/SQL query
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