Case Statement Help

[muzz]

HI. I have to write a case statement ,that is for example in a table there are three feilds ID,First_nm,Last_nm. From this table I have to write a query based on if the id ends with a zero then that Id belongs to Employee. If it ends with anything else than zero then it belongs to his family member. below is the example id 1000 then employeename, id 1001 then wife or son name, similarly id 2000 another employee name , 2001 his sons name.so finally the whole family( fathername/employeename and son name in one row. Then fathername and daughter name in another row),should come in one row, and the columns that get displayed are firstname, lastname,familymember firstname , lastname. Please Help.

[skhanal]

Which database are you writing this against?. Post your statement here.

The syntax is something like

select

case x when v1 then z1
when v2 then z2
end case
from table

[muzz]

The database is Teradata. Also I have tried that case statement but what I get is Null values in the rows where data doesnt match.I want to eliminate the null values and put the two rows in one row.

[skhanal]

I am not familiar with Teradata, but there must be a ELSE option for all values not matched by WHEN statements.

case ..

when xx then
when yy then
else
end case

[SDas]

First of all, I think you are leaving some information out. You mention knowing Sons, Daughters, Wife, etc but give no indication how this is know from a table with only

ID, First_nm, Last_nm

I will make an assumption that
1000 = Father
1001 = Wife
1002 = Son
1003 = Daughter

Example Data:

1000, Joe, Smith
1001, Jane, Smith
1002, Joey, Smith
1003, Janey, Smith

Example output with the columns:

Father_FirstName, Father_LastName, FamilyMemberType, FamilyMember_FirstName, FamilyMember_LastName

Joe, Smith, Wife, Jane, Smith
Joe, Smith, Son, Joey, Smith
Joe, Smith, Daughter, Janey, Smith

You also don't mention whether ID is Character or Integer. I will assume character.
This query should get you what you want (based on my assumptions):

SELECT Father_FirstName
     , Father_LastName
     , FamilyMemberType
     , FamilyMember_FirstName
     , FamilyMember_LastName
FROM
     (SELECT SUBSTR(ID, 1, (LENGTH(RTRIM(ID)) -1)) AS Father_KeyField
           , First_nm                              AS Father_FirstName
           , Last_nm                               AS Father_LastName
      FROM table-name
      WHERE SUBSTR(ID, (LENGTH(RTRIM(ID)) -1), 1) = 0
     ) AS FatherTab

   ,
     (SELECT SUBSTR(ID, 1, (LENGTH(RTRIM(ID)) -1)) AS Family_KeyField
           , First_nm                              AS FamilyMember_FirstName
           , Last_nm                               AS FamilyMember_LastName
           , CASE SUBSTR(SUBSTR(ID, (LENGTH(RTRIM(ID)) -1), 1)
                  WHEN '1' THEN 'Wife'
                  WHEN '2' THEN 'Son'
                  WHEN '3' THEN 'Daughter'
                           ELSE 'UNKNOWN'
             END                                   AS FamilyMemberType
     FROM table-name
     WHERE WHERE SUBSTR(ID, (LENGTH(RTRIM(ID)) -1), 1) <> 0
     ) AS FamilyTab
WHERE Father_KeyField = Family_KeyField

PS As an aside, if my assumptions are correct, this is a bad design. The relationship between the rows is hidden at the end of another column. At the worst, this means it can't be indexed to improve performance.

Instead a table with these columns would be a better design:
FamilyNumber (ex 100)
FamilyType( ex. 0, 1, 2, 3)
Firstname
Lastname

You can either include the text Family type names in the table (ex Wife, Son, Daughter) as another column or create another table with the information in it and join the two tables. Putting the column in the same table is a denormalization practice, however.

This would make the query much easier as all the Substring, Length and Trim functions would be necessary.

[muzz]

Thank You so much DAS , this was what I was looking for. I really appreciate your help also I will let you know if I need some more help because there is also an amount feild attached to each member.

[Island1]

Did you manage with the addtional field?

Bill

[muzz]

Yup I did.Thanks
Muzz.