Case Else Statement from different tables

Question

I am trying to retrieve a name based on a certain type of claim. Would seem easy enough, but it's not working for me. I am working with 2 main tables, claim and exposure, and a contactname table. Each claim can have multiple exposures, but each exposure can have only one claim. Both tables contain a nameID field - claim.nameID and exposure.nameID. The nameID correlates to ContactName table.

Contact Name

ID Name
111 Jim
222 Bob
333 John
444 Sam
555 Walt

Normally, each claim can have multiple exposures and the exposures on a claim can have the same name or different names. The name ids are found on the exposure.

The exposure.nameID joins ContactName.ID

Claim claim.nameID ExposureNo exposure.nameID Name
A null 1 111 Jim
A null 2 222 Bob
A null 3 333 John
B null 1 444 Sam
B null 2 444 Sam

Workers comp claims are different. Each workers comp claim can have multiple exposures, but all the exposures have the same name. Since the exposures on a claim have the same name, the name ids are found, not on the exposure table, but the claim table. Go figure… The claim.nameID joins ContactName.ID AS ClaimContactName.

Claim claim.nameID ExposureNo exposure.nameID Name
C 111 1 null Jim
C 111 2 null Jim
C 111 3 null Jim
D 555 1 null Walt
D 555 2 null Walt

I can get the names for non workers comp claims and can get the names for workers comp claims. However, when I put the 2 queries together, I don’t get any results.

Select
CASE WHEN @Select(Claim\Policy Type) = 'Workers'' Compensation' THEN
CASE WHEN ClaimContact.Name IS NULL AND ClaimContact.Name.FirstName IS NULL THEN ClaimContact.LastName
WHEN ClaimContact.Name IS NOT NULL THEN ClaimContact.Name
ELSE ClaimContact.FirstName + Space(1) + ClaimContact.LastName END
ELSE
CASE WHEN Contact.Name IS NULL AND Contact.FirstName IS NULL THEN Contact.LastName 
WHEN Contact.Name IS NOT NULL THEN Contact.Name
ELSE Contact.FirstName + Space(1) + Contact.LastName END
END

Haven’t done much of this, but could really use the help to figure this out. Thank you in advance.

Answer 1

This was for an SAP Universe using Information Design Tool. I ended up creating a derived table that gave me exactly what I needed, including learning something new along the way.