September 1, 2004 at 4:37 pm
I have a temp table that I create that compiles userids and a text field with numerous "permissions" in the text field. It looks like:
user1 149=1 84=2 23=1...
user1 149=2 84=2 23=1...
user2 149=1 34=1 14=2...
The permissions are separated by carriage return and line feed in the text field (I know, this is a ridiculous way to handle something like this but this is the way the vendor did it so I have to live with it).
What I need to do is merge each permission for each user. If the values are different I need to update that particular value with the lowest existing value for that particular permission. I am not sure how to handle this though. I have a function that will parse the text field so that a column contains each permission and value but don't exactly how to handle this. I was thinking of joining the tables returned by the function (per user) to find the differences and then update accordingly. But the number records per user would vary from 1 to n. Also, I can't figure out how to call the function with a field name and use the results in join (if even possible).
Any idea on how I could handle this? Should I look in a different direction completely?
Thanks, George
BTW, I am able to cast the text field as varchar(8000) so no need to play with manipulating a text field.
September 7, 2004 at 8:45 am
Hi,
I have to admit that looking at the table as it is now I don't even want to try answering your question.
BUT how about applying some lateral thinking. Can you create a new table in the database that mirrors the contents of the first table, but is structured in a more sensible manner.
Example
UserID PermissionID Value
user1 149 1
user1 84 2
etc....
user2 149 1
I'm assuming that you are writing data to the first table, in which case you must be parsing all the information and in so doing you can also be maintaining the secondary table.
Another similar approach I can think of is to retrieve all the contents of the first table and parse the data into a temporary table as above.
Hope that helps
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply