Not Null

rubyonrails-talk
1

hi,

I tried to find the not null elements from database

@genus_counts = Table.count(:all, :conditions=> {:col1 => params[:gm], :col2 => nil}, :without => {:col3 => nil})

its not recognising “without” function.

and

@genus_counts = Table.count(:all, :conditions=> {:col1 => params[:gm], :col3 != nil :col2 => nil})

its not recognising “!=” operator… Kindly suggest me and correct above statement.

2 
Dear All,

@genus_count = Table.count(:all, :conditions => ['col3 is not null and col2 is null and col1 = ?', params[:gm])

<%= @genus_count %>

It works.

3

On which database(s)? This is something I have wrestled with before on SQLite deploying to MySQL. What works on one fails on the other, and vice-versa.

Walter

4

PalaniKannan K wrote in post #950423:

I tried to find the not null elements from database

@genus_counts = Table.count(:all, :conditions=> {:col1 => params[:gm], :col2 => nil}, :without => {:col3 => nil})

In SQL you don't use equality to find NULL values.

SELECT * FROM my_table WHERE my_col IS NULL; or SELECT * FROM my_table WHERE my_col IS NOT NULL;

Rails: :conditions => [ "col1 = ? and col2 IS NULL and col3 IS NOT NULL", params[:gm] ]

5

Walter Davis wrote in post #954658:

On which database(s)? This is something I have wrestled with before on SQLite deploying to MySQL. What works on one fails on the other, and vice-versa.

AFAIK this syntax work fine in both SQLite and MySQL and every other database backend I've used. I believe it is compliant with SQL '92 standard.