bvinay57 (4/10/2009)
Hii am developing birt reports from mysql database wen i am retriving data from the table SELECT advid , country , state, city
FROM T1
some of my column values of state and country are giving null values now i want to change these null values with string values i have replaced ur code but it is giving the same null values result.
Thanx
Try out following examples:
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> create table tcount_tbl
-> (
-> tutorial_author varchar(40) NOT NULL,
-> tutorial_count INT
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tcount_tbl
-> (tutorial_author, tutorial_count) values ('mahran', 20);
mysql> INSERT INTO tcount_tbl
-> (tutorial_author, tutorial_count) values ('mahnaz', NULL);
mysql> INSERT INTO tcount_tbl
-> (tutorial_author, tutorial_count) values ('Jen', NULL);
mysql> INSERT INTO tcount_tbl
-> (tutorial_author, tutorial_count) values ('Gill', 20);
mysql> SELECT * from tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran | 20 |
| mahnaz | NULL |
| Jen | NULL |
| Gill | 20 |
+-----------------+----------------+
4 rows in set (0.00 sec)
mysql>
You can see that = and != do not work with NULL values as follows:
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
Empty set (0.01 sec)
To find records where the tutorial_count column is or is not NULL, the queries should be written like this:
mysql> SELECT * FROM tcount_tbl
-> WHERE tutorial_count IS NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahnaz | NULL |
| Jen | NULL |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * from tcount_tbl
-> WHERE tutorial_count IS NOT NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran | 20 |
| Gill | 20 |
+-----------------+----------------+
2 rows in set (0.00 sec)
kshitij kumar
kshitij@krayknot.com
www.krayknot.com