February 11, 2002 at 1:29 pm
Hi:
I'm brand new to T-SQL and upgrading an Access-based application to a SQL Server-based application and I've just run into a snag with a current step that I don't know how to do using stored procedures.
I currently update a field by running that fields' value through a custom function. The table being updated has a field (Descrip_2) which will have the data similar to that on the left, and which when updated will have the data on the right. So not all rows are being updated with the same value. They are all being updated in a similar manner, but not with the same value.
Descrip_2(Original) Descrip_2(Updated)
------------------- -------------------
None None
v4.002 for HPUX 4.002
v5.001 5.001
V6.499 for SUNOS 6.499
None None
v6.002 6.002
1.000 for Linux 1.000
But the function I created in Access VBA doesn't work with SQL Server because the funtion isn't known or available to SQL Server.
I use a function because I can't guarantee the format of the data coming in. It's a free-form data entry field from an accounting database (bills of material module), and the data coming in (an agreed upon purpose and format) is no better than the attention to detail that the individual doing the data entry gives to it. So the current function does its best to deal with common and expected errors. And I don't know all of the values that are coming in. One of the reasons we use this field is so that I can identify EXACTLY when a new software revision begins to ship and on what order they being to ship. So I don't want to do something as simplistic as Right(Descrip_1,len(Descrip_2)-1) because the 'v' character may be missing from the Descrip_2 value and that would lop off the most significant digit of the version number.
So the tasks are:
-----------------
check the first character of Descrip_2 for 'v' (case insensitive) drop it if it exists RIGHT(LEN(Descrip_2)-1)
Test for a space character (CHARINDEX(' ',Descrip_2). If it is greater than 0 then return SUBSTRING(Descrip_2, 1, CHARINDEX(' ',Descrip_2))
Third and final test is to be certain that the final value can be numeric ISNUMERIC(Descrip_2).
Update the Descrip_2 field value
Now, I can do this using separate procedures for each test/update. But I can't figure out how to do this in a single stored procedure/update.
Thanks for any help. I'm not asking to have the code written for me, but a shove in the right direction is greatly appreciated.
JK
February 11, 2002 at 5:04 pm
JUst a hint then as this looks like you are trying to solve on your own. Conditional testing is best done in CASE statements in SQL.
Example:
CASE
WHEN Object=This THEN OuputThisValue
WHEN Object=That THEN OutputThisOtherValue
ELSE NonConditionValue
END
For a partial instance of what you have.
UPDATE tblX SET Descrip_2 = (CASE
WHEN LEFT(Descrip_2,1) = 'v' THEN RIGHT(Descrip_2,LEN(Descrip_2) - 1)
WHEN (CHARINDEX(' ',Descrip_2) != 0 THEN SUBSTRING(Descrip_2, 1, CHARINDEX(' ',Descrip_2))
ELSE WHATEVER
END)
You will of course need to find the best format and condition to meet your needs.
February 12, 2002 at 10:07 am
Antares686 has a good suggestion, but you can just apply multiple UPDATE clauses using the WHERE clause for each case. This might help.
Alternatively, you can use a second column that stores the standard date format and update that as each row is inserted.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 12, 2002 at 11:07 am
Hi:
Thank you Jim and Steve, for the responses.
No sooner than I sent that post than I came up with the following solution using (5) UPDATE queries:
-- Where no value now exists, update with 'NONE'
IF EXISTS (SELECT Descrip_2 FROM BOM WHERE ((Descrip_2 IS NULL OR Descrip_2= '') AND (Descrip_2<>'PACKAGE')))
UPDATE BOM SET Descrip_2 = 'NONE' WHERE ((Descrip_2 IS NULL OR Descrip_2= '') AND (Descrip_2<>'PACKAGE'))
-- Where the first character is 'v' or 'V', remove that character.
IF EXISTS (SELECT Descrip_2 From BOM WHERE ((UPPER(Left(Descrip_2,1)) = 'V') AND (Descrip_2<>'PACKAGE')))
UPDATE BOM SET Descrip_2 = RIGHT(Descrip_2, LEN(Descrip_2)-1) WHERE ((UPPER(Left(Descrip_2,1)) = 'V') AND (Descrip_2<>'PACKAGE'))
-- Where the first character is a number, and there is a space in the value, update using the value from the first character up to the first space
IF EXISTS (SELECT Descrip_2 From BOM WHERE ((ISNUMERIC(Left(Descrip_2,1)) = 1 AND CHARINDEX(' ', Descrip_2) > 0) AND (Descrip_2<>'PACKAGE')))
UPDATE BOM SET Descrip_2 = SUBSTRING(Descrip_2, 1, CHARINDEX(' ', Descrip_2)) WHERE ((ISNUMERIC(Left(Descrip_2,1)) = 1 AND CHARINDEX(' ', Descrip_2) > 0) AND (Descrip_2<>'PACKAGE'))
-- Where the remaining value can be a number, but there is no decimal point, update as invalid
IF EXISTS (SELECT Descrip_2 FROM BOM WHERE ((ISNUMERIC(Descrip_2) = 1 AND CHARINDEX('.',Descrip_2) = 0) AND (Descrip_2<>'PACKAGE')))
UPDATE BOM SET Descrip_2 = 'Invalid' WHERE ((ISNUMERIC(Descrip_2) = 1 AND CHARINDEX('.',Descrip_2) = 0) AND (Descrip_2<>'PACKAGE'))
-- Where the remaining value can be a number and there is a decimal point, but fewer than three decimal places, update as incomplete
IF EXISTS (SELECT Descrip_2 FROM BOM WHERE ((ISNUMERIC(Descrip_2) = 1 AND LEN(Descrip_2)-CHARINDEX('.',Descrip_2)<>3) AND (Descrip_2<>'PACKAGE')))
UPDATE BOM SET Descrip_2 = 'Incomplete' WHERE ((ISNUMERIC(Descrip_2) = 1 AND LEN(Descrip_2)-CHARINDEX('.',Descrip_2)<>3) AND (Descrip_2<>'PACKAGE'))
I had tried to do it in the manner Antares686 mentioned, but I was having a lot of difficulty. But I think most of that was how new I am to the syntax of CASE, use of commas as separators, etc., and also the need to set an expression that evaluates as a boolean to 1 or 0. In many cases, I just used the expression and expected it to be evaluated as is, either True or False. For example:
ISNUMERIC(Descrip_2)
vs.
ISNUMERIC(Descrip_2) = 1
So after receiving Antares686 message, I revisited it again, and had much more success. My solution currently is:
UPDATE BOM SET Descrip_2 =
CASE
-- When the first character is a 'V' (case insensitive) or a number, and there is a space is the value, update with the value up to the space, excluding the 'V' when it is found
WHEN UPPER(LEFT(Descrip_2,1)) = 'V' AND CHARINDEX(' ', Descrip_2) > 0 THEN SUBSTRING(Descrip_2, 2, CHARINDEX(' ', Descrip_2)-2)
WHEN ISNUMERIC(Left(Descrip_2,1)) = 1 AND CHARINDEX(' ', Descrip_2) > 0 THEN SUBSTRING(Descrip_2, 1, CHARINDEX(' ', Descrip_2)-1)
-- When the first character is a 'V' (case insensitive) or a number, and there is no space is the value, update with the value, excluding the 'V' when it is found
WHEN UPPER(LEFT(Descrip_2, 1)) = 'V' AND CHARINDEX(' ', Descrip_2) = 0 THEN RIGHT(Descrip_2, LEN(Descrip_2)-1)
WHEN ISNUMERIC(Left(Descrip_2,1)) = 1 AND CHARINDEX(' ', Descrip_2) = 0 THEN Descrip_2
WHEN Descrip_2 IS NULL OR Descrip_2= '' Then 'None'
ELSE
Descrip_2
END
WHERE Left(Descrip_2, 4) <>'PACK'
UPDATE BOM SET Descrip_2 =
CASE
WHEN ISNUMERIC(Descrip_2) = 1 AND CHARINDEX('.',Descrip_2) = 0 THEN 'Invalid'
WHEN ISNUMERIC(Descrip_2) = 1 AND LEN(Descrip_2)-CHARINDEX('.',Descrip_2)<>3 THEN 'Incomplete'
ELSE
Descrip_2
END
WHERE Left(Descrip_2, 4) <>'PACK'
I'm not sure that this is the most efficient yet. As you can see, there are now two separate UPDATE queries. The first takes it's best shot at whittling the content of the field down to a version number, and the second is a test for completeness and validity of a version number. It's not immediately obvious too me if I can do both in a single step or not. However, I do think the second solution is much more efficient than the first and it's a reasonable point to move on.
Again, thanks for your help. If you look at the above code and see room for improvement, I'm more that happy to listen.
JK
A row-based solution was always an option, but it wan't the way I wanted to go.
February 12, 2002 at 6:06 pm
As long as you get the results you want and can be sure the data is what is to be expected always go with what works. You can always fine tune later. In fact I today have queries I have rewritten dozens of times, every time I learn something new, and each time was better than the previous.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply