What's wrong with my case statement?

acidburn85 · #1 (**permalink**) 02-08-12, 12:13

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,

PracticalProgram · #2 (**permalink**) 02-08-12, 12:18

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?

acidburn85 · #3 (**permalink**) 02-08-12, 12:59

No but if is use "IS NULL or IS NOT NULL" it returns even more black rows. How would you suggest I change it?

PracticalProgram · #4 (**permalink**) 02-08-12, 17:31

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.

Pat Phelan · #5 (**permalink**) 02-08-12, 17:47

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

TallKewlOnez · #6 (**permalink**) 02-13-12, 17:52

try this instead of ' '

when len(rtrim(Father_First_Name)) != 0

Internet Services

Web Development

Digital Marketing

Consumer Tech