I've got a situation by which I have to choose all posts from the table and also the totally something similar to:
select sr.*, cs.subjectid, cs.priority, cs.subjectname from sometable sr, otherTable cs where sr.col1='E2011FT0' and sr.col2='5' and sr.col3= '66018' and cs.col1=sr.col1 order by cs.col2;
How do you look for null values within the
sr.* posts and replace all of them with another value? You will find lots of posts within the
sometable table and I am attempting to avoid listing all of them clearly.
I do not believe what you are after can be done either in standard SQL or MySQL (well, in a roundabout way up for grabs, but see below for any possible solution).
There is no option to carry out a common column manipulation on
sr.*, you'll have to perform the posts individually, something similar to:
select sr.column1, sr.column2, coalesce (sr.column3, 0), sr.column4, cs.subjectid ...
One possibility, climax a little kludgy, is use a view within the actual table where each column from the view is similarly named but understood to be a
coalesce around the equivalent table column.
What i mean is something similar to:
create view sometableview (column1, column2, column3, column4) as select column1, column2, coalesce (column3, 0), column4 from sometable; select srv.*, cs.subjectid, ... : from sometableview srv, otherTable cs where ...
That will not allow it to be any faster but it'll keep the query simpler, which appears to become what you are after. I am not entirely certain why this is a requirement since queries are usually setup once and transformed rarely therefore it is unusual to bother with their length. But I am likely to assume you've got a valid reason for that requirement until informed otherwise :-)
Use Choose using the COUNT function to count all rows of the given column, such as the null values, make use of the ISNULL function. The ISNULL function can replace the null value having a valid value. Using the IsNULL function, NULL is changed with .
CREATE TABLE tabcount ( pkey int IDENTITY NOT NULL CONSTRAINT pk_tabcount PRIMARY KEY, col1 int NULL) GO INSERT tabcount (col1) VALUES (10) GO INSERT tabcount (col1) VALUES (15) GO INSERT tabcount (col1) VALUES (20) GO INSERT tabcount (col1) VALUES (NULL) GO SELECT AVG(col1) A1, (1) AVG(ISNULL(col1,0)) A2, (2) COUNT(col1) C1, (3) COUNT(ISNULL(col1,0)) C2, (4) COUNT(*) C3 (5) FROM tabcount GO A1 A2 C1 C2 C3 ----------- ----------- ----------- ----------- --- 15 11 3 4 4
--Null value is removed by an aggregate or any other SET operation.
(1) - NULL values are removed. (2) - Using the IsNULL function, NULL is changed with . (3) - NULL values are removed. (4) - Using the IsNULL function, NULL is changed with . (5) - COUNT(*) computes all rows, even individuals with NULLs.