April 22, 2009 at 7:52 am
Hello
I wrote the following query to create a function:
CREATE FUNCTION [lookupTest]
(@prefix nvarchar(8))
RETURNS varchar(8)
AS
BEGIN
declare @Return varchar(8)
SET @return = case @prefix
WHEN'VIE'THEN'VIE'
WHEN'VSY'THEN'VSY'
WHEN'WAP'THEN'WAP'
WHEN'WCP'THEN'WCP'
WHEN'WOL'THEN'WOL'
WHEN'WPE'THEN'WPE'
WHEN'XHO'THEN'XHO'
WHEN'YAO'THEN'YAO'
WHEN'YDI'THEN'YDI'
WHEN'YOR'THEN'YOR'
WHEN'ZND'THEN'ZND'
WHEN'ZUL'THEN'ZUL'
ELSE 'unknown'
END
RETURN @return
END
GO
--------------------
When i execute the script it names every prefix with 'unknown'. And what i want to do is that, if the prefix is found it should change to the correct one, and if not, leave the prefix without changing it, or renaming it to 'Unknown'.
Can anyone help, and explain where am going wrong?
April 22, 2009 at 8:02 am
What would be an example value that you are passing the function? The first thing that I notice is that your prefix variable allows up to eight characters. If you are passing in more than three, you will need to substring on the first three characters.
April 22, 2009 at 8:10 am
Hello
There are three characters which is sent from end users in a flat file which is imported, within the file there are two sets of characters which need to be transformed and they are:
WHEN 'OTH' THEN 'OTL'
WHEN 'OTB' THEN 'OTL'
But instead of transforming the two sets of characters in question it updates every record with 'Uknown', which is what i dont want.
April 22, 2009 at 8:15 am
I don't see you converting those in your function. Did you post a trimmed down version? If not, you need to add those to the case statement.
April 22, 2009 at 8:21 am
Hi
I added them to the CASE statement, but it updated every record with "Uknown".
Is there something im missing here?
April 22, 2009 at 8:32 am
The data being passed in must not match for some reason (spaces or something). You may want to try to do an LTRIM on the prefix to make sure there are no leading spaces. Here is some sample code that shows you that the function works when it finds a match. The only record that returns unknown is ABC.
CREATE FUNCTION [lookupTest]
(@prefix nvarchar(8))
RETURNS varchar(8)
AS
BEGIN
declare @Return varchar(8)
SET @return = case @prefix
WHEN'VIE'THEN'VIE'
WHEN'VSY'THEN'VSY'
WHEN'WAP'THEN'WAP'
WHEN'WCP'THEN'WCP'
WHEN'WOL'THEN'WOL'
WHEN'WPE'THEN'WPE'
WHEN'XHO'THEN'XHO'
WHEN'YAO'THEN'YAO'
WHEN'YDI'THEN'YDI'
WHEN'YOR'THEN'YOR'
WHEN'ZND'THEN'ZND'
WHEN'ZUL'THEN'ZUL'
WHEN 'OTH' THEN 'OTL'
WHEN 'OTB' THEN 'OTL'
ELSE 'unknown'
END
RETURN @return
END
GO
Declare @test-2 Table (val nvarchar(8))
Insert Into @test-2 Values ('OTH')
Insert Into @test-2 Values ('OTB')
Insert Into @test-2 Values ('ABC')
Select dbo.lookupTest(val)
FROM @test-2
April 23, 2009 at 2:38 am
Thanks Ken
It was the spacing as you said that made all records updating to "Unknown". Do you also know how i can create an update function?
I used the following:
CREATE FUNCTION [prefix]
(@prefix nvarchar(3))
RETURNS varchar(3)
AS
BEGIN
declare @Return varchar(3)
UPDATE @prefix
SET @Return = OTL
WHERE @Return IN ('OTH','OTB')
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply