Not Null

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.

Dear All,

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

<%= @genus_count %>

It works.

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

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] ]

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.