Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    22

    Unanswered: What's wrong with my case statement?

    The way it's working now is if the child has no father then it simply comes out as blank even though there is a mother or guardian. Any thoughts?

    Code:
        Case
        When Father_First_Name <> ' ' THEN (Father_First_Name+' '+Father_Last_Name)
        When Father_First_Name = ' '  THEN (Mother_First_Name+' '+Mother_Last_Name)
        When Mother_First_Name = ' ' THEN (Guardian_First_Name+' '+Guardian_Last_Name)
        END as Guardian,

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Why are you testing against a single blank space?

    Are you 1,000,000,000,000,000% sure that all records without a father are populated with a single blank space?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Sep 2011
    Posts
    22
    No but if is use "IS NULL or IS NOT NULL" it returns even more black rows. How would you suggest I change it?

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    acidburn,

    Tell us what you KNOW to be in those fields.

    I'm still not sure why you chose a single space simply because you used some test with for a null which you determined did not work. You could be storing an empty string (''), or multiple spaces(' '). Who knows!

    YOU should KNOW what is in those columns. If you don't KNOW, then find out.

    So, if a record does not have a father's name in it, exactly what is stored in that column?

    You may want to look at LTRIM and RTRIM to reduce any entry of redundant spaces into a uniform column value that you can reliably test against.

    Good luck.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CASE
          WHEN Father_First_Name <> ' '  THEN (Father_First_Name   + ' ' + Father_Last_Name)
          WHEN Mother_First_Name <> ' '  THEN (Mother_First_Name   + ' ' + Mother_Last_Name)
          ELSE                                (Guardian_First_Name + ' ' + Guardian_Last_Name)
        END as Guardian,
    PracticalProgram makes a very good point. This code works, but it relies on "the kindness of strangers" in order to work. If a user accidentally types a single character or enters "Unknown" for the father's name, then this code will still use the father's name.

    There are many ways to do this and you'll need to find the one that works best for your users. I wouldn't leave this code "as is" longer than I needed to, it will eventually break and hurt you!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2012
    Posts
    12

    trim

    try this instead of ' '

    when len(rtrim(Father_First_Name)) != 0

Posting Permissions

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