Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL - There is a field, not null, not Blank, but with a length of 0...

    Hi all,

    I have hit a rather unusual error, and really would like some advice. There is no rush, as there is a workround in place, but i dont want to leave it this way, if we come accross this error again elsewhere this may not work.

    Basically, we are using a table valued function to return a set of results, within this function there is an update statement. When we take out the Update statement, the column "NHS_Number" is NULL (in ssms2008, the cells are yellow with NULL in them).

    Both the source and destination columns have been defined as a VARCHAR(23) Datatype.

    The Update statement we used is as follows:
    Code:
    UPDATE @retAdmDis -- Update Demographic Data
    SET
     rad.NHS_Number = a.NHS_Number
    
    FROM  @retAdmDis rad
    JOIN 
    (
    SELECT
     p.crn
    ,p.nhsno AS NHS_Number
    FROM WSHmfrepos.dbo.patients p
    JOIN WSHmfRepos.dbo.admissions ad
    	ON p.crn = ad.crn
    ) AS a
    ON a.crn = rad.Local_Patient_Identifier
    after running this update, the cells either contain the correct data, or nothing at all.

    However, not everyone has an NHS_Number, due to various policies etc, we have to display "Unknown" Rather than a null or blank column.

    Altering the select code to this:
    Code:
    (
    SELECT
     p.crn
    ,ISNULL(p.nhsno,'Unknown') AS NHS_Number
    FROM WSHmfrepos.dbo.patients p
    JOIN WSHmfRepos.dbo.admissions ad
    	ON p.crn = ad.crn
    ) AS a
    ON a.crn = rad.Local_Patient_Identifier
    does nothing, the field returned is blank.

    so we tried a replace, to replace '' with 'Unknown' and nothing happened.

    Just to make sure it wasnt a space, or something similar, we did this.

    Code:
    (
    SELECT
     p.crn
    ,'*'+ p.nhsno + '*' AS NHS_Number
    FROM WSHmfrepos.dbo.patients p
    JOIN WSHmfRepos.dbo.admissions ad
    	ON p.crn = ad.crn
    ) AS a
    ON a.crn = rad.Local_Patient_Identifier
    what we get for the null rows is:
    **

    When we do a LENGTH of the column, it has a length of 0.

    So for now we have done a case statement of "CASE WHEN LEN(p.nhsno) = 0 THEN 'Unknown' ELSE nhsno END AS NHS_Number" and this works.

    As i said at the top, i would like a solution as a case statement might not always be feesable.

    This has had 3 experienced SQL Developers confused for abotu a week now, so please try to be gentle when you break the solution to us!

    Thanks!

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    It seems to me that somewhere along the update process, the column in question was set to a value of '' (zero length string). Why I can't really say, but it seems to be the case. Both the result of using LENGTH and the fact that last statement where the expression '*' + nhsno + '*' is used, results in a value of '**' for the column, show that. You probably knew that, anyway.

    So, you are sure the value of a.NHS_NUMBER is not a zero length string for those cases? If you are, can there be something else changing the value from a NULL to a zero length string (a trigger, maybe)?

  3. #3
    New Lounger
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi thanks for replying, sorry for the delay,

    At source, there is either nhsno is either populated or null.

    I dont know where in the update it would be getting '', seen as we have tried every way we can think of to handle it. On both sides of the update (Select, and in the ColA=colB bit)

    There are no triggers or anything on this table, as i think i said above, we are using a table valued function, and as far as i know, triggers can't be applied to these.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    The problem seems to lie with the p.nhsno column, right? That column's value is never changed, is it? Is it that column's value that you state is shown as NULL before the table valued function is called?

    Sorry, just trying to understand what the actual situation is and the previous posts are not that clear so far.

  5. #5
    New Lounger
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    very sorry for the unclear posts, im finding it hard to properly explain the problem, as we dont understand it ourselves!

    We have had another look at this, and discovered that the problem actually lies within the table, not the tvf, so apologies for misleading youw ith that.

    The column p.nhsno is a varchar(23) and contains 10Digit Numbers (0123456789 for example), NULL (as displayed in SSMS in a yellow shaded cell) and this character that is 0length but not '' or NULL.

    The database is case sensitive, im not sure if this makes a difference? Is there such a think as an uppercase blank?

    Sorry if this si, again, unclear. I am trying!

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Please do not understand my remark about clarity as a complaint. I was just trying to justify my own question.

    With the info you have provided, it's even harder for me than for you, I think. So, you run a select on the patients table and nshno is null. You use it to set the value of a column in the tvp and the value is a zero length string. No other code being run in between these two? Once this is done, nshno is again null?

  7. #7
    New Lounger
    Join Date
    Feb 2012
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    not quite,

    We have a table called patients, in the table is a column called nshno, the columns datatype is VARCHAR(23). The values within the column are either;
    1) A set of numbers [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] i.e 0123456789
    2) NULL (SELECT ISNULL(nhsno,'Unknown') works)
    3) '' (SELECT REPLACE(nhsno,'','Unknown') works.)
    4) ??????? (Not actual question marks, we don't know the value, to the eye it appears like '' would i.e. SELECT '' however SELECT REPLACE(nhsno,'','Unknown') doesn't work

    We are unable to identify the actual value of the column (option 4 above...) hence the only way we have found to return 'Unknown' for this particular value is SELECT CASE WHEN LEN(p.nhsno) = 0 THEN 'Unknown' ELSE nhsno END

    We have narrowed the problem down to the actual source table, the above mentioned TVF is not causing the problem.

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    So your problem are the columns in situation 4. Can't you simply update those to NULL or ''?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •