CODEWITHSUNDEEP
sqloracle-database
Kuurde
Kuurde

Reputation: 885

Query on number field in oracle db

My problem is probably so stupid that I'm missing something very obvious. I have a table with a number field that represents boolean values, 1 or 0. My table is created like this:

create table mytable ("summarized" NUMBER DEFAULT 0)

.. and I want to query for records where the number field 'summarized' equals 0. Now take a look at the queries below, actual output is placed behind the query:

select count(*) from mytable where summarized = 0; -- 0
select count(*) from mytable where summarized = 1; -- 0
select count(*) from mytable where summarized != 0; -- 88223
select count(*) from mytable where summarized != 1; -- 1594
select count(*) from mytable where to_char(summarized) = '0'; -- 1594
select summarized, count(*) from mytable group by summarized; -- 1 = 88223, 0 = 1594

My first instinct would be to create my query like the first one. I can't seem to understand why it's not working and why I have to use the to_char function if I want 'equals 0' in my where clause.

EDIT: As I was trying the answers I noticed I wasn't able to reproduce the problem, even with a column name with quotes. After making a copy of my table and removing the old one my query and my application were working like expected again. I'm not sure what caused the abnormality, but it's gone now.

I'm sorry to have taken up your time, but would like to thank you all for the useful remarks in the answers! (I wish I could give you an upvote for them, but my rep is too low still)

Upvotes: 2

Views: 106

Answers (2)

Nagaraj S
Nagaraj S

Reputation: 13484

Syntax for table creation

create table mytable (summarized NUMBER DEFAULT 0)

select count(*) from mytable where summarized != 0;

SQL FIDDLE

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52356

A good start would be to place a check constraint on that column:

alter table mytable add constraint chk_mytable_summarized check (summarized in (0,1))

... and a not null constraint also ...

alter table mytable modify (summarized  not null)

(Do not use double quotes around your object names, as Nagaraj says)

Upvotes: 2

Related Questions