I have a where clause like

"and uc.completion_status in ('[P....]') " 

here ('[P....]') is the user input from taken a dropdown list in the user interface. and dropdown list consists three options Y, N or NotStarted

not started is defined with nvl(uc.completion_status, 'NotStarted') therefore NotStarted is null actually. and null in quotes is not working. how can this problem can be handled?

thanks in advance.

  • Do you mean that when uc.completion_status is null then it should match the NotStarted value which came from UI ? where does the 'null' (null in quotes) come from ? – A.B.Cade Mar 13 '13 at 15:00
  • NotStarted comes from ui but since i have set this value with nvl(uc.completion_Status, 'NotStarted'), when NotStarted selected, the query returns nothing, – elimerv Mar 13 '13 at 15:17
  • if i wouldnt set NotStarted and then null value listed in the dropdown, null value will be in quotes – elimerv Mar 13 '13 at 15:20
  • Am I correct: When you created the list you set null to be 'NotStarted', so now you can get 'Y','N' and 'NotStarted'. When you query afterwards you have in your where clause uc.completion_status in (<the list that might contain 'Y','N' and 'NotStarted'>) so when uc.completion_status was null you fing no records, right? – A.B.Cade Mar 13 '13 at 15:29
  • yen you are right – elimerv Mar 13 '13 at 15:44

Simple! Put NULL without quotes.


INSERT INTO tableName (columnName1, columnName2) VALUES ('Value1', NULL)

We must always use NULL without quotes when we intend to use NULL values. Putting quotes around it, like 'NULL' makes it a string literal and it is treated as character data. Hence for using NULL values, we must always use NULL without quotes, be it a null string, a null number or a null BLOB. For every data type, we must use NULL without quotes.

  • when change where clause like where and uc.completion_status in ([P....]) then other Y and N values will be without qoutes too. – elimerv Mar 13 '13 at 15:08
  • @elimerv, then why not nvl(uc.completion_status, 'null') in ([P...]) ? (unless you have an index on completion_status and you don't want to use a function) – A.B.Cade Mar 13 '13 at 15:17
  • to clarify my problem; <pre><code> select myTasks, nvl(uc.completion_status, 'NotStarted') from myTable where uc.completion_status in ('[SelectedParameterByEndUser]')<code><pre> i have some tasks with statuses completed(stores as Y in DB), not completed(stored as N in DB), and Not Started (stored as null in DB) and when i select N or Y, query return ok but when i select null(or notStarted by defining it with nvl ) nothing returns. actually i dont want to display a null thing on the ui – elimerv Mar 13 '13 at 15:39

If you don't care for an index on the uc.completion_status, then you simply need to do:

select ...
from ...
where ... and nvl(uc.completion_status, 'NotStarted') in (<the list>)

Otherwise, you can create a function based index (as @LeighRiffel commented)

  • Thank you man! i have try to try this but some other mistake prevent me from the solutipon – elimerv Mar 13 '13 at 16:01
  • Neither of these would use an index on uc.completion_status. A function based index would work for the first query, see sqlfiddle.com/#!4/201c9/2 – Leigh Riffel Mar 13 '13 at 17:26
  • Thanks @LeighRiffel, as for the first query that's obvious, but I wonder if the second one will never use the index... anyway since the second one is wrong (as <the list> will not be a string in runtime) I'll update my answer – A.B.Cade Mar 13 '13 at 20:33
  • Correct, it will never use an index on completion_status because there is a function on it. – Leigh Riffel Mar 14 '13 at 14:53
  • Thanks @LeighRiffel, but I meant the second query which I've removed, there is no function on the column, though there is a 'IS NULL' so according to this AskTom post it should depend on other keys of the index – A.B.Cade Mar 17 '13 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.