select query modification

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

  • 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

  • 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

  • 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

  • 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

  • Hi

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

    Thanks

    -Matt

  • 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

  • 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

  • Thanku krayknot for ur reply.

  • Select COALESCE(T1.State, 'String') as state

    from table1 as T1

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply