September 21, 2016 at 1:03 pm
I am tring to implement a inline tvf to concatenate two strings, the first one in uppercase and second one is not neccesarily uppercase. Here's what I have so far. I get an error near the keyword 'return'.
Basically, I intend to concatenate if el_id>0 else return'00'. Can someone please point me in the right direction. Thanks
Code:
CREATE FUNCTION [hsip].[getMatchedFlags]
(
@nFY NUMERIC(4,0) = 0,
@sResponse_String varchar(250),
@nEL_ID NUMERIC(5,0) = 0
)
RETURNS @sResult TABLE(FY NUMERIC(4,0),
Response_string varchar(250),
el_id numeric(5,0))
AS
BEGIN
insert into @sResult
SELECT CASE
when (@nEL_ID>0) then
CONCAT(
(select count(*) as upperMatched
from HSIP.FY_ORT_VALUES ov
where ov.FY = @nFY and OV.EL_ID = @nEL_ID
and upper(OV.VAL_DESCRIPTION) = upper(@sRESPONSE_STRING)),
(SELECT COUNT(*) as valueMatched
FROM hsip.FY_ORT_VALUES OV
WHERE OV.FY= @nFY AND OV.el_id= @nEL_ID
AND OV.VAL_DESCRIPTION= @sResponse_String))
else
--ELSE
--when (@nEL_ID<0) then
--@sresult= '00';
return;
Thanks,
September 21, 2016 at 1:16 pm
There are several issues here.
1) By default, SQL Server is case insensitive. Unless you've overridden these defaults, the two subqueries are going to return the same results.
2) You say that you want to concatenate strings, but the values that you are returning are integers.
3) Multi-line table valued functions perform horribly.
If you want help rewriting this, we'll need some sample data and expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 21, 2016 at 1:19 pm
Also, the ELSE clause within your CASE statement is missing an expression, and your CASE statement is missing an END.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 21, 2016 at 1:20 pm
Your code makes no sense.
Why are you concatenating row counts? You'll end up with a number that means nothing. You're also not telling what are you returning. You mention that you want an inline TVF, but you're using multiple statements.
Check the following example on what you could end up with.
CREATE TABLE SampleData(
FY NUMERIC(4,0),
EL_ID NUMERIC(5,0),
VAL_DESCRIPTION varchar(250) COLLATE Latin1_General_Bin
);
INSERT INTO SampleData
VALUES
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'WHOLE UPPER'),
( 1, 1, 'whole upper'),
( 1, 1, 'whole upper'),
( 1, 1, 'whole upper'),
( 1, 1, 'whole upper');
GO
CREATE FUNCTION [getMatchedFlags]
(
@nFY NUMERIC(4,0) = 0,
@sResponse_String varchar(250),
@nEL_ID NUMERIC(5,0) = 0
)
RETURNS TABLE
AS
RETURN
SELECT CASE when (@nEL_ID>0)
then CONCAT(
(select count(*) as upperMatched
from SampleData ov
where ov.FY = @nFY and OV.EL_ID = @nEL_ID
and upper(OV.VAL_DESCRIPTION) = upper(@sRESPONSE_STRING))
,
(SELECT COUNT(*) as valueMatched
FROM SampleData OV
WHERE OV.FY= @nFY AND OV.el_id= @nEL_ID
AND OV.VAL_DESCRIPTION= @sResponse_String)
)
ELSE '00'
END Counts;
GO
SELECT * FROM [getMatchedFlags](1,'whole upper',1);
SELECT * FROM [getMatchedFlags](1,'WHOLE UPPER',1);
SELECT * FROM [getMatchedFlags](1,'WHOLE ',1);
GO
DROP FUNCTION [getMatchedFlags];
DROP TABLE SampleData;
September 21, 2016 at 2:06 pm
My code has no errors. I tested it before posting.
I still have no idea on what you want to return. You're asking to return the same values that you're entering as parameters.
September 21, 2016 at 2:34 pm
All it does is concatening 2 strings and it returns resulting string. For example 26, 32 result: 2632.
I have defered this function will try to implement the same directly in the procedure. Thanks for your time.
September 21, 2016 at 4:02 pm
Part of the problem is that you are reading the same table twice when you only need to read it once. I've rewritten this to use a CROSS APPLY instead of a function call, use CASE expressions instead of subqueries, and use XML concatenation instead of the CONCAT function. The main query holds the "parameters".
SELECT *
FROM (
VALUES
(1, 'whole upper', 1),
(1, 'WHOLE UPPER', 1),
(1, 'WHOLE ', 1)
) s(nFY, sRESPONSE_STRING, nEL_ID)
CROSS APPLY (
SELECT COUNT(match1), COUNT(match2)
FROM (
SELECT CASE WHEN UPPER(ov.VAL_DESCRIPTION) = UPPER(s.sRESPONSE_STRING) THEN s.nEL_ID END,
CASE WHEN ov.VAL_DESCRIPTION = s.sRESPONSE_STRING THEN s.nEL_ID END
FROM SampleData ov
WHERE ov.FY = s.nFY AND ov.EL_ID = s.nEL_ID
) m(match1, match2)
FOR XML PATH('')
) ov(conct)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 22, 2016 at 8:35 am
@drew Allen,
Thanks for identifying the problem. I did not understand the XML concatenation part here. CAn you please provide some examples. I have never used it before. Also, what are the benefits of doing xml concatenation.
What does FOR XML PATH('') mean?
Thanks
September 22, 2016 at 10:27 am
SQL Server add XML functionality to SQL 2005. This allows you to read/create XML fragments. The FOR XML tells SQL Server that you want to create an XML fragment; the PATH tells it which of four methods you want to use to create that fragment, and the '' says that you do not want to create an element to hold that fragment. The reasons to use it are that it's very fast, well-documented (although not necessarily for this particular application), and automatically handles NULL values.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply