CODEWITHSUNDEEP
sqloracle-database
sidra rizvi
sidra rizvi

Reputation: 21

Oracle SQL - Not null code is not working

I am trying to retrieve the monetary amount associated with project IDs, however I only want data where a project ID exists (not blank)

When I type my SQL code below... 

SELECT project_id, monetary_amount, journal_line_date 
FROM PS_JRNL_LN 
where project_id is not null
  and journal_line_date BETWEEN to_date ('2020/01/01','yyyy/mm/dd') 
                            AND TO_DATE ('2020/03/04','yyyy/mm/dd')

this query works however, I am still getting blank values in my result

Upvotes: 2

Views: 9190

Answers (5)

SreeKiran Yeeli
SreeKiran Yeeli

Reputation: 1

Try using filter condition:

ltrim(rtrim(project_id)) <> ''

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35553

Inner join that journal table to the source of truth for Project ID's. Assuming there are no "blank" ID's in that table, then you won't get "blanks" in your result.

e.g.

SELECT j.project_id, j.monetary_amount, j.journal_line_date 
FROM PS_JRNL_LN J

  INNER JOIN PROJECT_MASTER P ON j.project_id = p.id /* should remove "blanks" */

where j.journal_line_date >= to_date ('2020/01/01','yyyy/mm/dd') 
and  j.journal_line_date < TO_DATE ('2020/03/05','yyyy/mm/dd')

Note also, I never use between for date ranges, the above pattern using >= & < is more reliable (as it works regardless of the time precision of the data).

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Few things you must implement in your table design to prevent the problem at first place than struggling with the data:

  1. Add a NOT NULL constraint to the column.
  2. Add a CHECK constraint to prevent unwanted characters like whitespaces etc. and only allow the data you want to load.
  3. If you don't want a check constraint, then handle it during loading the data using TRIM.
  4. If necessary, make the PROJECT_ID column the primary key, that would implicitly not allow NULL values. Usually, the ID column in a table suggests it's a primary key but it could vary in your use case.

If you are not allowed to alter the design by doing none of the above, then at least you could handle the data insertion at application level where you might be taking it as input.

Upvotes: 0

user5683823
user5683823

Reputation:

Here is something that can help you find out what is happening in the project_id column. (Most likely, a bunch of ' ' values, meaning non-empty string consisting of a single space.)

select project_id, dump(project_id)
from   ps_jrnl_ln
where  ltrim(project_id, chr(32) || chr(9)) is null
  and  project_id is not null
;

DUMP shows you exactly what is stored in your table. 32 is the ASCII code for a single space; 9 (or 09) is the code for horizontal tab. I expect you will get rows where the DUMP column shows a single character, with code 32. But - who knows; you may find other things as well.

That will help you understand what's in the column. (You may also check describe ps_jrnl_ln - you may find out that the column is declared not null!!!)

If you find a bunch of rows where the project id is a single space, of course, in your actual query you will have to change

where project_id is not null

to

where ltrim(project_id, chr(32) || chr(9)) is not null

Or, perhaps, if indeed a single space is used as placeholder for null:

where project_id != ' '

Upvotes: 0

Himanshu
Himanshu

Reputation: 3970

You dont have nulls but blank spaces add below in your query

 SELECT project_id, monetary_amount, 
  journal_line_date 
  FROM PS_JRNL_LN 
    where ( project_id is not null or
   ( project_id is not 
     null   
    and  LTRIM( RTrim(project_id)) not 
       like '') 
     and 
     journal_line_date BETWEEN 
     to_date ('2020/01/01','yyyy/mm/dd') 
                        AND TO_DATE 
   ('2020/03/04','yyyy/mm/dd')

Upvotes: 3

Related Questions