Home » SQL & PL/SQL » SQL & PL/SQL » only answer my question if you have nothing better to do
icon8.gif  only answer my question if you have nothing better to do [message #252591] Thu, 19 July 2007 05:28 Go to next message
jheds
Messages: 3
Registered: July 2007
Location: pune
Junior Member
select name
from emp
where case when eid = 'ALL' then
(select distinct name from emp)
-------------------------------------------------------------

select name
from emp
where decode(eid,'ALL',select distinct name from emp)

using such a query i want to retrieve all names in the emp table if "ALL" is entered.
is there any other functionality?
the "ALL" parameter is passed through a report actually and if the user enters all then all data is displayed.

regards

[mod-edit) removed asinine arrogant calls for urgency.

[Updated on: Thu, 19 July 2007 07:39] by Moderator

Report message to a moderator

Re: Very Urgent please ! [message #252604 is a reply to message #252591] Thu, 19 July 2007 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is above URGENT that you read the stickies before posting:
How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then you have to know that nothing is urgent in a forum and that a meaningful title is useful for those who can answer and the others that have to find an answer.

URGENT is so useless that I didn't even read the question.

Regards
Michel

Re: only answer my question if you have nothing better to do [message #252633 is a reply to message #252591] Thu, 19 July 2007 08:36 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select name
from emp
where eid='ALL' or eid=name;
Re: only answer my question if you have nothing better to do [message #252650 is a reply to message #252633] Thu, 19 July 2007 09:50 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If "eid" is a report parameter, do you have something like this in mind?
SELECT name FROM emp
WHERE 1 = CASE
            WHEN :EID = 'ALL' THEN 1
            ELSE 2
          END;
Re: only answer my question if you have nothing better to do [message #252805 is a reply to message #252650] Fri, 20 July 2007 01:14 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
But that would mean "all or nothing" (if EID is not ALL than the where clause results to 1=2 -> no rows selected).

Maybe you mean:
SELECT *
FROM   employees e
WHERE  e.last_name = decode(upper('&eid')
                           ,'ALL'
                           ,e.last_name
                           ,'&eid')
Re: only answer my question if you have nothing better to do [message #252839 is a reply to message #252805] Fri, 20 July 2007 04:04 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, Sabine, all or nothing - that's how I translated OP's
OP
i want to retrieve all names in the emp table if "ALL" is entered
Re: only answer my question if you have nothing better to do [message #252931 is a reply to message #252839] Fri, 20 July 2007 13:42 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Haha, literally you are right. I admit I assumed he/she meant more than that, because what could be the use of "all or nothing"... But, according to specs, your code is correct Wink
Re: only answer my question if you have nothing better to do [message #252935 is a reply to message #252591] Fri, 20 July 2007 14:39 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Just an FYI,
I received the following email from the original poster of this thread


+++++++++++++++++++++++++++++++++++++++++
Subject: select name from emp where eid = 'ALL' or eid=name

Hi Bill,

the table structure is :

name varchar2(5)
eid varchar2(5)
dept number(5)

Data in the table is :

micael 1 10
roger 2 20
bill 3 30

now i want to retrieve all values
by passing a parameter eid from a report.
if the user enters a valid eid , ok.
but if the user enters 'ALL' then all data should be displayed

Regards
+++++++++++++++++++++++++++++++++++++++++

I sent him the following response

+++++++++++++++++++++++++++++++++++++++++
The name of the parameter should not be the same as a column in the table, it causes the parser to get confused. For our example, I am going to assume that the name of the parameter is EMP_EID


Select e.name,e.eid,e.dept
From emp
Where (EMP_EID = 'ALL'
or e.eid = EMP_EID);

+++++++++++++++++++++++++++++++++++++++++

He responded with the following note

+++++++++++++++++++++++++++++++++++++++++

Great Solution Sir,

Thanks for your precious time.

Regards

+++++++++++++++++++++++++++++++++++++++++
Re: only answer my question if you have nothing better to do [message #252942 is a reply to message #252935] Fri, 20 July 2007 16:15 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does it mean that this topic can be locked? ./fa/450/0/
Re: only answer my question if you have nothing better to do [message #252943 is a reply to message #252591] Fri, 20 July 2007 16:16 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I suspect so
Re: only answer my question if you have nothing better to do [message #253006 is a reply to message #252943] Sat, 21 July 2007 07:42 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Suspicious mind, eh?
Previous Topic: How to limit the ranking
Next Topic: Difference of between clause in number&varchar2 fields
Goto Forum:
  


Current Time: Wed Dec 07 12:37:05 CST 2016

Total time taken to generate the page: 0.08779 seconds