Home » SQL & PL/SQL » SQL & PL/SQL » how to select all when value is null
how to select all when value is null [message #427246] Wed, 21 October 2009 11:38 Go to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Help

I want to ask the question: If the parameter is left blank return all data if not just the parameter value but my query refuses to show all, what am I doing wrong?

[code]
SELECT PEO.EMPLOYEE_NUMBER EMPL_ID,
PEO.FULL_NAME,
PEO.ATTRIBUTE17
FROM HR.PER_ALL_PEOPLE_F PEO
WHERE (PEO.EFFECTIVE_START_DATE, PEO.EFFECTIVE_END_DATE) = (SELECT MAX(PEO1.effective_start_date),
MAX(PEO1.effective_end_date)
FROM HR.EMP_TABLE_F PEO1
WHERE PEO.PERSON_ID = PEO1.PERSON_ID)

AND PEO.ATTRIBUTE17 = NVL(:P_EMPL_TYPE, PEO.ATTRIBUTE17)





Re: how to select all when value is null [message #427248 is a reply to message #427246] Wed, 21 October 2009 11:43 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks ok.
Either :P_EMPL_TYPE isn't actually null (maybe it's got spaces in it) or it's really the sub-query that's causing the problem.
Re: how to select all when value is null [message #427249 is a reply to message #427246] Wed, 21 October 2009 11:44 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Oh and next time you post code you might want to use the preview message button to check you've got the code tags right.
Re: how to select all when value is null [message #427250 is a reply to message #427248] Wed, 21 October 2009 11:45 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

It may have spaces in it, how can I catch that?
Re: how to select all when value is null [message #427253 is a reply to message #427246] Wed, 21 October 2009 11:49 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
trim
Re: how to select all when value is null [message #427257 is a reply to message #427253] Wed, 21 October 2009 12:01 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

thanks for helping but that didnt work either. Weird if :P_EMPL_ATTEND is left blank it only brings back those records that have a value it does not bring back any NULLS.

NVL(:P_EMPL_ATTEND,trim(peo.attribute17))
Re: how to select all when value is null [message #427259 is a reply to message #427257] Wed, 21 October 2009 12:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Weird if :P_EMPL_ATTEND is left blank it only brings back those records that have a value it does not bring back any NULLS.
Not weird.
Expected behavior.

NULL never equals NULL

Re: how to select all when value is null [message #427267 is a reply to message #427246] Wed, 21 October 2009 12:23 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Oh yes - I forgot about nulls.
This is what you need:
AND PEO.ATTRIBUTE17 = :P_EMPL_TYPE OR :P_EMPL_TYPE IS NULL
Re: how to select all when value is null [message #427280 is a reply to message #427267] Wed, 21 October 2009 13:11 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Thanks. It works now.
Re: how to select all when value is null [message #427290 is a reply to message #427246] Wed, 21 October 2009 15:31 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
select * from employees 
where dept_id = decode(trim(in_dept_id),null, dept_id,trim(in_dept_id));


Thanks

[Updated on: Wed, 21 October 2009 15:33]

Report message to a moderator

Re: how to select all when value is null [message #427310 is a reply to message #427290] Wed, 21 October 2009 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/technology/oramag/oracle/09-jul/o49asktom.html

Regards
Michel
icon10.gif  Re: how to select all when value is null [message #427339 is a reply to message #427310] Thu, 22 October 2009 02:30 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sir, Thanks for sharing the info
Previous Topic: Update not detect table in Procedure
Next Topic: Please help
Goto Forum:
  


Current Time: Sat Dec 10 20:36:36 CST 2016

Total time taken to generate the page: 0.09872 seconds