Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select query modification Expand / Collapse
Author
Message
Posted Friday, April 10, 2009 3:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 17, 2009 1:27 AM
Points: 34, Visits: 99
hi i am new bie to birt report wen i am giving query statement it is giving null values in two fields how to replace null values with strings in select statementcan any one help me........
Post #694675
Posted Friday, April 10, 2009 3:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:40 AM
Points: 366, Visits: 866
Hi

If I've understood you correctly, you are wanting to replace a NULL value in a SELECT statement, try using ISNULL, e.g.

SELECT ISNULL( , 'string')

Have a look at IsNull in books online for more information.

Thanks
-Matt
Post #694676
Posted Friday, April 10, 2009 3:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 17, 2009 1:27 AM
Points: 34, Visits: 99
hi matt thanx for ur reply but its not working iwill give u some idea about my table in my table adid, country, state ,city are the field names but in state and city i am getting null values can u please give me some code now
thanx
Post #694685
Posted Friday, April 10, 2009 3:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:40 AM
Points: 366, Visits: 866
Hi

My mistake I missed some info out of my previous example.

SELECT T1.ADID, T1.COUNTRY, ISNULL(T1.STATE,'the text you want instead of NULL')
FROM yourtable AS T1


Post #694692
Posted Friday, April 10, 2009 4:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 17, 2009 1:27 AM
Points: 34, Visits: 99
Hi
matt its giving same result i.e., it is giving same null value and in the field name state is replacing with UNNAMED_3 any more information regarding this
Thanx
Post #694698
Posted Friday, April 10, 2009 4:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:40 AM
Points: 366, Visits: 866
Hi

Can you reply with a sample of your code? What application are you writing this in?

Thanks
-Matt
Post #694699
Posted Friday, April 10, 2009 4:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 17, 2009 1:27 AM
Points: 34, Visits: 99
Hi
i 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
Post #694704
Posted Friday, April 10, 2009 4:51 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, Visits: 534
bvinay57 (4/10/2009)
Hi
i 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
Post #694715
Posted Friday, April 10, 2009 5:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 17, 2009 1:27 AM
Points: 34, Visits: 99
Thanku krayknot for ur reply.
Post #694724
Posted Monday, February 8, 2010 4:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 18, 2011 8:01 AM
Points: 221, Visits: 153
Select COALESCE(T1.State, 'String') as state
from table1 as T1
Post #862115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse