How to check zero values ​​when selecting the whole query?

Question

I have a situation in which I need to select all the columns from a table, and the query looks something like this:

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 to check zero values ​​in sr.* Columns and replace them with another value? There are many columns in sometable , and I try not to specify them explicitly.

Answer 1

I do not believe that you are then possible in standard SQL or MySQL (well, not directly on the table, but see below for a possible solution).

There is no way to perform general column processing on sr.* , You will have to do the columns individually, for example:

 select sr.column1, sr.column2, coalesce (sr.column3, 0), sr.column4, cs.subjectid ... 

---

One possibility, albeit a bit ragged, is to provide a view over the actual table, where each view column is similarly named, but defined as coalesce in the equivalent column of the table. By this, I mean something like:

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

This will not do it faster, but it will simplify your request, which seems to be what you need. I’m not quite sure why this is a requirement, since requests are usually set once and rarely change, so it’s unusual to worry about your length. But I'm going to suggest that you have a good reason for demanding until it says otherwise :-)

Answer 2

Use SELECT with the COUNT function to count all rows of a given column, including null values, using the ISNULL function. The ISNULL function can replace a null value with a valid value. Using the IsNULL function, NULL is replaced with 0.

 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 is excluded by a combination or other SET operation.

(1) - NULL values ​​are deleted. (2) - Using the IsNULL function, NULL is replaced with 0. (3) - NULL values ​​are deleted. (4) - Using the IsNULL function, NULL is replaced with 0. (5) - COUNT (*) calculates all rows, even those that are NULL.