| how to combine two queries [message #379334] |
Tue, 06 January 2009 02:07  |
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
|
Member |
|
|
hi,
i have two queries like below
Query 1:
=======
select emp_id from emp_form where
(TO_CHAR(expected_emp_commission_d,'DD-MM-YYYY'))= TO_CHAR(sysdate+14,'DD-MM-YYYY')
Query2:
=======
select emp_id from emp_purchase_details where emp_form_raised='N';
I tried by using below query. I am getting the records but is it right way to do ?
select * from paf_form a, paf_purchase_details b where
(TO_CHAR(a.expected_first_commission_d,'DD-MM-YYYY'))= TO_CHAR(sysdate+14,'DD-MM-YYYY') and
b.aia_form_raised='N' and a.paf_id = b.paf_id;
do any one tell me better way to do this..
Thankq
|
|
|
|
| Re: how to combine two queries [message #379341 is a reply to message #379334] |
Tue, 06 January 2009 02:34  |
mailtonagaraja
Messages: 9
Registered: June 2007
|
Junior Member |
|
|
1 solution is you can give aliases to these 2 queries and combine ....
like
select * from
(select * from temp1_emp where .............) a,
(select * from temp2_emp where ...........) b
where a.emp_id=b.emp_id
|
|
|
|
| Re: how to combine two queries [message #379343 is a reply to message #379334] |
Tue, 06 January 2009 02:36 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
It depends. Do you want to only see emp_id's that fulfill both premises, or do you want to combine the two resultsets?
In the first case use a join (like you did); in the second case use a union.
[Edit: rewrite your logic to compare the two dates.
(TO_CHAR(a.expected_first_commission_d,'DD-MM-YYYY'))= TO_CHAR(sysdate+14,'DD-MM-YYYY')
equals
trunc(a.expected_first_commission_d) = trunc(sysdate + 14)
If a.expected_first_commission_d does not contain any times (i.e. all records have a time of 00:00:00), you can even remove the first trunc, thereby enabling any index-usages, if there is an index on that column.
[Updated on: Tue, 06 January 2009 02:39] Report message to a moderator |
|
|
|
|
|
| Re: how to combine two queries [message #379627 is a reply to message #379354] |
Wed, 07 January 2009 07:00 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You could try this: select emp_id
from emp_form
where (TO_CHAR(expected_emp_commission_d,'DD-MM-YYYY'))= TO_CHAR(sysdate+14,'DD-MM-YYYY')
intersect
select emp_id
from emp_purchase_details
where emp_form_raised='N';
[tidy up query]
[Updated on: Wed, 07 January 2009 07:01] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
