Home » SQL & PL/SQL » SQL & PL/SQL » query Filter
query Filter [message #196921] Mon, 09 October 2006 03:27 Go to next message
Messages: 65
Registered: November 2001
what should be the best way to extrcat a query :
if parameter is passed then filter otherwise return all.

For example
select * from emp
I have one parameter deptnum then there are 4 dept 10,20,30,40

If i pass 40 then display only data from 40 if pass it null then dispay all

I have tried with Union and also tried to decode the deptnum with Null value but could not end with desired result.

Can anyone throw some light
Re: query Filter [message #196923 is a reply to message #196921] Mon, 09 October 2006 03:32 Go to previous messageGo to next message
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

have u tried using nvl.

Re: query Filter [message #196927 is a reply to message #196921] Mon, 09 October 2006 03:39 Go to previous message
Messages: 2
Registered: October 2006
Junior Member

This will work for u

select * from emp where deptnum = NVL(:p1,deptnum)

assume 'deptnum' as column name and 'p1' as parameter.

[Updated on: Mon, 09 October 2006 03:39]

Report message to a moderator

Previous Topic: Index not getting used when LIKE '%..%' IS USED
Next Topic: Duplicate key error on merge with unique source data
Goto Forum:

Current Time: Fri Oct 28 15:00:13 CDT 2016

Total time taken to generate the page: 0.32596 seconds