CODEWITHSUNDEEP
javasqljdbcderby
PeakGen
PeakGen

Reputation: 23025

Error in NULLIF()?

Please have a look at the following code

public List<String> getNames()
{
List<String> names = new ArrayList();


try
{
    createConnection();

    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("select NULLIF(names,' '),NULLIF(middleName,' '),NULLIF(lastName,' ') from PhoneData order by names asc");

    while(rs.next())
    {
        String fullName = rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3);
        names.add(fullName);
    }

    return names;

}
catch(Exception e)
{
    e.printStackTrace();
    return null;
}
finally
{
    closeConnection();
}

}

I am using Apache Derby, embedded version.

I am retrieving 3 items here, first name, middle name and last name. If any item is null, then I am attempting to fill it with a blank space. But, when I run this and add it into a combobox, I can see it hasn't replaced the null values with a blank space!!! All the null values are displayed as 'null'. For an example,

FirstName = "John", MiddleName="", LastName="Laxter"

As you can see, the MiddleName is null. What I am expecting is

"John Laxter"

What I get is "John null Laxter" !!!. Why is this? Please help! I have not had this issue when I am working with MS SQLServer (then NULLIF is isNull()). What's wrong here? Please help!!

Upvotes: 2

Views: 828

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

Try this:

select 
coalesce(names,' '),
coalesce(middleName,' '),
coalesce(lastName,' ') 
from PhoneData
order by names asc

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

select 
    coalesce(names,' '),
    coalesce(middleName,' '),
    coalesce(lastName,' ') 
from PhoneData 
order by names asc

nullif() does not do what you think.

Upvotes: 1

John Woo
John Woo

Reputation: 263733

better use COALESCE

SELECT COALESCE(names,' '),...

Upvotes: 3

Related Questions