Post reply

Can anyone spot the mistake????

  • March 23, 2005 at 6:55 am

    #65620

    Hi all,

    I've written the following script but it does not work.  When I run the whole statement i.e:

    select e.rollno + '|' + cast(e.optout as varchar(10)) + '|' + e.electorsurname + '|' + e.electorforename1 + '|' +

    e.electorforename2 + '|' + cast(p.houseno as varchar(10)) + '|' + p.housenosuffix + '|' +

    p.housename + '|' + p.flatname + '|' + p.institutionblockname + '|' + cast(p.streetref as varchar(10)) +

    '|' + p.pdcode + '|' + convert(varchar(10), e.dob, 103) as Electors

    from elector as e inner join property as p on e.propertyref = p.propertyref

    where e.whichregister = 'C'

    I get the following result:

    Electors

    NULL

    NULL

    NULL

    But when I just retrieve the first 4 columns I get the correct result.  I've taken the columns out one by one to try to spot the error but it is not becoming obvious.  Can anyone see what I am doing wrong?

    Also, does anyone know of a neat way to post code cos mine looks a mess!!!

    Many thanks....

  • March 23, 2005 at 7:05 am

    #548462

    Can it be that one or more column can contain NULL values?

    If so, put an ISNULL around them, since a string + NULL is NULL

    And, btw, you're mixing presentational stuff in here. That should really be done at the client.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 23, 2005 at 7:08 am

    #548465

    Paula, by first 4 columns I assume you mean e.rollno, e.optout, e.electorsurname and e.electorforename1. If these columns return data then look at the remaining columns. One ( or more ) must be null. Contatentating a any value with a null will result in a null. Try adding isnull(column name, '' )  for each of the columns.

     

    dab

  • March 23, 2005 at 7:18 am

    #548470

    Perfecto!  I did not realise that a string + a null equals a null.  But now I do!

    Thank you both!

  • March 23, 2005 at 7:27 am

    #548475

    Paula, not only that, but anything + null = null

    dab

  • March 23, 2005 at 8:50 am

    #548515

    Not necessarily. This behaviour is controlled by SET CONCAT_NULL_YIELDS_NULL ON|OFF

    Depending on if this setting is on or off,

    string + null may be:

    string

    or

    null

    /Kenneth

Viewing 6 posts - 1 through 6 (of 6 total)

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