May 11, 2010 at 1:17 pm
name table
type, first, mi, non residential name, name key
PK is type, name key
type=smallint
first = char(15)
mi = char
non residential name = char(60)
name key = char(30)
I realize that this tables layout is kind of messed up but this is the way it is coming at me.
the name key field is part of the PK and if the type = 1 then the first 15 characters of name key is the last name. if the type = 2 then the field uses the first 30 characters of the non residential name for a unique key structure.
name table data
2,null,null,'ABC Plumbing','ABC Plumbing'
1,'John',null,null,'SMITH John'
1,'Peter','S',null,'JOHNSON Peter'
2,null,null,'Triple A','Triple A'
I would like to try and query back
ABC Plumbing
SMITH, John
Johnson, Peter S
Triple A
May 11, 2010 at 2:14 pm
sorry it was just to easy to post in here rather than spend some time refreshing my memory
here is my solution
(CASE WHEN coc_name_type = 2 THEN coc_non_res_name ELSE ltrim(rtrim(cast(coc_name_key as char(15))))+ ', ' + coc_first + ISNULL(' ' + coc_mi, '') END) as Names,
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply