Home » SQL & PL/SQL » SQL & PL/SQL » query
icon5.gif  query [message #257371] Wed, 08 August 2007 03:48 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi,

the emp table has 5 rows as below

100 ram 10000.00 10
200 kumar 15000.00 10
300 william 20000.00 10
400 ravi 25000.00 10
500 victor 30000.00 10

i execute the below query

select ename,sal from emp_test where case when sal < 10000 then sal + 1000
when sal < 20000 then sal + 2000
else sal
end < 20000
it gives the below output
ram 10000.00
kumar 15000.00

How the above query is working?
Please explain. thanks in advance
Re: query [message #257375 is a reply to message #257371] Wed, 08 August 2007 03:59 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

First of all format your post please.

For know How to format see http://www.orafaq.com/forum/t/85036/0/

Regards
Sanka

[Updated on: Wed, 08 August 2007 04:00]

Report message to a moderator

Re: query [message #257377 is a reply to message #257371] Wed, 08 August 2007 04:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Move the CASE statement to the SELECT part and remove it from the WHERE clause. Then you'll see what the output is of the CASE statement. It's not that hard.

MHE
Re: query [message #257379 is a reply to message #257371] Wed, 08 August 2007 04:02 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi sanka,


From the beginning itself, i send the message by this way
Re: query [message #257380 is a reply to message #257379] Wed, 08 August 2007 04:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
balaji23_d wrote on Wed, 08 August 2007 11:02
hi sanka,


From the beginning itself, i send the message by this way

What are you saying? You don't have to format your posts?

MHE
Re: query [message #257381 is a reply to message #257371] Wed, 08 August 2007 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, a query is easier to read and understand if it is formatted.
What don't you understand?

Regards
Michel
Re: query [message #257383 is a reply to message #257380] Wed, 08 August 2007 04:07 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

It is not the paid forum. This is also not for kidding and joking place. There are so senior members gives us the answers for free. So, please show them some respect.

Regards
Sanka
Re: query [message #257395 is a reply to message #257383] Wed, 08 August 2007 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a serious question:
Quote:
What don't you understand?

And a serious remark: if you format your query it is easier to read and understand.

Why select, from and first part of where clause in 1 line?
Why where clause is broken in a middle of a condition when the rest is in single line?
And so on.

Regards
Michel
Re: query [message #257401 is a reply to message #257371] Wed, 08 August 2007 04:45 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
sorry guys,

select ename,sal from emp_test 
where 
      case when sal < 10000 then sal + 1000
      when sal < 20000 then sal + 2000 
else sal 
      end < 20000


pls is this ok? Cool

[Updated on: Wed, 08 August 2007 05:09] by Moderator

Report message to a moderator

Re: query [message #257404 is a reply to message #257401] Wed, 08 August 2007 05:00 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
pls is this ok?

No, it's not.
You clearly did not read the link that Sana_yanka (and who knows how many other people have) sent you to.
You did not use code tags and you are still using IM speak.
So no, it's not ok
Re: query [message #257406 is a reply to message #257377] Wed, 08 August 2007 05:10 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Maaher wrote on Wed, 08 August 2007 11:02
Move the CASE statement to the SELECT part and remove it from the WHERE clause. Then you'll see what the output is of the CASE statement. It's not that hard.
Please do.

MHE
Re: query [message #257432 is a reply to message #257401] Wed, 08 August 2007 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select ename, sal 
from emp_test 
where case when sal < 10000 then sal + 1000
           when sal < 20000 then sal + 2000 
           else sal 
      end < 20000

Explain what you don't understand in this.

Regards
Michel
Re: query [message #257436 is a reply to message #257371] Wed, 08 August 2007 06:35 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi guys,

now i got it

select ename, (case when sal < 10000 then sal + 1000
when sal < 20000 then sal + 2000
else sal
end )sal from emp_test
where (case when sal < 10000 then sal + 1000
when sal < 20000 then sal + 2000
else sal
end )< 20000

thank u guys,
Re: query [message #257459 is a reply to message #257436] Wed, 08 August 2007 07:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Great that you got it.
Saves us the trouble of formatting your post yet again.
I for me, still don't understand what the initial problem was, but hey, it's all about keeping the customer happy!
Re: query [message #257481 is a reply to message #257436] Wed, 08 August 2007 08:04 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
balaji23_d wrote on Wed, 08 August 2007 07:35
hi guys,

now i got it

select ename, (case when sal < 10000 then sal + 1000
when sal < 20000 then sal + 2000
else sal
end )sal from emp_test
where (case when sal < 10000 then sal + 1000
when sal < 20000 then sal + 2000
else sal
end )< 20000

thank u guys,


Another arrogant lazy member who thinks they can follow their own rules. See how long you keep getting answers.
Previous Topic: Accept Command in SQL*PLUS
Next Topic: ORA-20000: Table not found in db_rolling_partitions_meta
Goto Forum:
  


Current Time: Sat Dec 03 18:08:18 CST 2016

Total time taken to generate the page: 0.19966 seconds