Home » SQL & PL/SQL » SQL & PL/SQL » Help needed
Help needed [message #401159] Fri, 01 May 2009 19:59 Go to next message
sirishachikka
Messages: 2
Registered: May 2009
Junior Member
In a table the rows to be fetch where if we give input as null then all rows to be output otherwise the appropriate row will be the output.

for above requirement i write below query

select * from emp
where
(comm= decode('&comm',NULL,comm,'&comm') or comm is null)
and (sal = decode('&sal',null,sal,'&sal') or sal is null)
/


from above query i'm getting correct output.

but my manager tell me to handle " or sal is null " in decode function it self.

how can i handle "is null" function in decode?



Thanks in advance

[Updated on: Fri, 01 May 2009 20:02]

Report message to a moderator

Re: Help needed [message #401160 is a reply to message #401159] Fri, 01 May 2009 20:04 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
http://www.dbforums.com/oracle/1641925-please-help-how-use-fetch-null-rows-using-decode.html#post6397022

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Help needed [message #401183 is a reply to message #401159] Sat, 02 May 2009 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but my manager tell me to handle " or sal is null " in decode function it self.

Your manager or your teacher? There is no need for a manager to tell you how just to tell you what.

The correct way is (using bind variables and not substitution ones):
:comm is null or comm = :commm

or if you always want rows with null comm
:comm is null or comm = :commm or comm is null


Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).


Regards
Michel

[Edit: Sorry Ana didn't see you posted a link to the same and answered topic.]

[Updated on: Sat, 02 May 2009 01:25]

Report message to a moderator

Re: Help needed [message #401224 is a reply to message #401183] Sat, 02 May 2009 19:04 Go to previous messageGo to next message
sirishachikka
Messages: 2
Registered: May 2009
Junior Member
Thanks
Re: Help needed [message #401246 is a reply to message #401183] Sun, 03 May 2009 04:27 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
There is no need for a manager to tell you how just to tell you what.
I disagree. I currently manage a project that uses a few guys who are less experienced than I. When there are multiple ways of performing a task, I will happily tell them which way I want it done, because I know which way is best/ fastest under the specifics of the problem. I don't want my staff to sit for half a day experimenting to find out for themselves, I want the task done within the time constraints.
Re: Help needed [message #401254 is a reply to message #401246] Sun, 03 May 2009 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Never say never. Wink
It was a very simplified rule, of course, a little bit like a provocation (not sure it is the correct word in english).

If the manager has a better experience and good reasons, of course he may, he must intefere.

Regards
Michel
Re: Help needed [message #401300 is a reply to message #401246] Sun, 03 May 2009 23:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pablolee wrote on Sun, 03 May 2009 11:27
Quote:
There is no need for a manager to tell you how just to tell you what.
I disagree. I currently manage a project that uses a few guys who are less experienced than I. When there are multiple ways of performing a task, I will happily tell them which way I want it done, because I know which way is best/ fastest under the specifics of the problem. I don't want my staff to sit for half a day experimenting to find out for themselves, I want the task done within the time constraints.

Although this may be true, I sincerely doubt if this can be compared to the problem of the original poster:
His/her manager orders him to use a given construct, apparently without explanation, and THEN leaves him dangling for days.
Somehow, I expect you to do this differently...
Previous Topic: Question
Next Topic: Fetching the unmatched records from two queries.
Goto Forum:
  


Current Time: Tue Dec 06 12:10:40 CST 2016

Total time taken to generate the page: 0.05167 seconds