Home » RDBMS Server » Performance Tuning » Querry performance
Querry performance [message #360113] Wed, 19 November 2008 11:51 Go to next message
sreenu80
Messages: 50
Registered: July 2006
Location: bangalore
Member

Hi Friends,

I have a senario like this

select decode(name,'smith','Manager','N/A') from emp where
deptno = 10
union
select decode(name,'smith','Accotent','N/A') from emp where deptno = 10


I need to pick these two records in one single select statement. I mean with out union condition.

Please let me know.


thanks
sreenu
Re: Querry performance [message #360122 is a reply to message #360113] Wed, 19 November 2008 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Join one query with a "table" of 2 rows, for instance:
select 1 from dual union all select 2 from dual

Regards
Michel
Re: Querry performance [message #360134 is a reply to message #360113] Wed, 19 November 2008 13:12 Go to previous messageGo to next message
sreenu80
Messages: 50
Registered: July 2006
Location: bangalore
Member

hi Michel,

Thanks for quick replay. sorry i am not understand your explonation.

Let me tell my senario is

select decode(name,'smith','Manager','N/A') from emp where
deptno = 10 and mgr is null
union
select decode(name,'smith','Accotent','N/A') from emp where deptno = 10 and nvl(mgr,0)=0

My issue is if smit is exist in department no 10 and is mgr is null i need to get the record says " Manger" along with that if smith is exists in department no 10 and if mgr is null supress with "0" and compare with the "0" is mgr i need this record also.


empno name mgr deptno
1 smith 10
2 smith 0 10
3 smith 1 10

i need to get the first two rows. my issue is similar to this just i changed to simple example.

thanks
Re: Querry performance [message #360136 is a reply to message #360134] Wed, 19 November 2008 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Let me tell my senario is

This is NOT the same as the first post.
Different scenarii, different solutions.

Quote:
if mgr is null supress with "0"

What does this mean?

"nvl(mgr,0)=0" means is manager is null or 0. Is this what you want?

In the end, post a representative test case: create table and insert statements along with the result you want with these data and explain this result.

Regards
Michel
Re: Querry performance [message #360146 is a reply to message #360113] Wed, 19 November 2008 15:40 Go to previous messageGo to next message
sreenu80
Messages: 50
Registered: July 2006
Location: bangalore
Member

If mgr values is null supress with "zero" and check the condtion where mgr equla to "zero"

nvl function will supress the null value with "zero" where null in the mgr column and then it will check where deptno = 10 and mgr=0
Re: Querry performance [message #360163 is a reply to message #360146] Wed, 19 November 2008 22:58 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 19 November 2008 20:35

In the end, post a representative test case: create table and insert statements along with the result you want with these data and explain this result.

Regards
Michel


Previous Topic: parallel hint
Next Topic: Tuning a small query
Goto Forum:
  


Current Time: Sun Dec 11 06:25:58 CST 2016

Total time taken to generate the page: 0.18261 seconds