January 16, 2010 at 4:18 pm
I have a not-so-well designed database where there is a client table, and within the client table there is a status, multiple statuses can be assigned to the client and they are all stuck into a single string in the client table as follows:
: Number : FirstName : LastName : Status :
In the status column one of the clients could have 4 statuses '1ABG' for good client, bad client, active, etc. There is also a ClientStatus table which holds the descriptions for each status. A status description could be Active and so on.
: Status : Description :
I was looking for a way to see if clients had statuses that are not in the ClientStatus table, I can't do a NOT LIKE against a subquery that returns multiple values, so the only way around it I could think of was a FOR EACH cursor or a loop, throwing each status into a string and doing a NOT LIKE against that. Anyways, I was just looking for a simpler way to do this. If anyone has any suggestions, please feel free to post.
Thanks!
-Tristan
January 16, 2010 at 5:34 pm
Hi there,
Assuming that all your Stsus codes have only one character, you could do something like:
declare @client as table (Number int, FirstName varchar(10), LastName varchar(10), Status varchar(10))
declare @status as table (Status varchar(1), Description varchar (10))
insert into @client(Number, FirstName, LastName, Status)
select 1, 'FirsName1', 'LastName1', '1ABG' union all
select 2, 'FirsName2', 'LastName2', 'A' union all
select 3, 'FirsName3', 'LastName3', 'G' union all
select 4, 'FirsName4', 'LastName4', 'AB' union all
select 5, 'FirsName5', 'LastName5', 'BG' union all
select 6, 'FirsName6', 'LastName6', '1A'
insert into @Status (Status, Description)
select '1', 'Status 1' union all
select 'A', 'Status A' union all
select 'B', 'Status B'
select a.Number, FirstName, LastName, substring(a.status, Numbers.number, 1) StatusCode, s.Description
from @client a cross join (
select top 10 row_number() Over(order by id) as number
from syscolumns
) Numbers
left outer join @Status s on s.status = substring(a.status, Numbers.number, 1)
where substring(a.status, Numbers.number, 1) <> ''
order by a.number
José Cruz
January 18, 2010 at 6:02 am
Hello,
Thanks for the response. A lot different than I would have thought and I learned a few things here. Thanks for the information.
January 18, 2010 at 8:47 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply