throughout coding my project I've experienced a hurdle and can't undergo with this particular problem...
here's what I must achieve, I've got a simple table which stores data associated with football gamers like: Number (ID), Title, Goals (are additional ones, but at this time are irrelevant) and that i have produced a UDF Multistatement table LotOfGoals which looks the following:
CREATE FUNCTION LotOfGoals() RETURNS @Players TABLE ( Number INT, Name VARCHAR(20), Goals INT FuzzyLevel FLOAT(3) --extra column which I would like to add to result ) AS BEGIN INSERT @Players SELECT Number, Name, Goals FROM FuzzyFootballTeam WHERE Goals > 2 ORDER BY Number -- here FuzzyLevel column should include data counted by MembershipLevel -- scalar UDF. -- I want to pass each number of goals into MembershipLevel function and -- insert return value into a new column FuzzyLevel. RETURN END GO
now MembershipLevel function:
CREATE FUNCTION MembershipLevel(@Goals INT) RETURNS float(3) AS BEGIN DECLARE @Level float(3) SET @Level = 0.25*@Goals - 0.5; RETURN @Level END
When I wrote, after WHERE clause I must pass each quantity of goals to some MembershipLevel after which its return value place into new column FuzzyLevel.
I'd be really really grateful for just about any hint, idea etc. Thanks ahead of time !
True, I'll switch to in-line one. Yet another real question is there in whatever way to make use of FuzzyLevel column in where clause ? (I receive Invalid column title 'FuzzinessLevel') things i want would be to limit permitted fuzzylevel. I've broadened both functions with yet another additional argument @AcceptedFuzzyLevel float and also the scalar function appears like this:
DECLARE @Level float(3) DECLARE @TempLevel float(3) IF (@Goals <= 2) SET @TempLevel = 0; IF (@TempLevel >= @FuzzyLevelAccepted) SET @Level = @TempLevel; ELSE IF (@Goals > 2 AND @Goals < 6) SET @TempLevel = 0.25*@Goals - 0.5; IF (@TempLevel >= @FuzzyLevelAccepted) SET @Level = @TempLevel; ELSE IF (@Goals >= 6) SET @TempLevel = 1; IF (@TempLevel >= @FuzzyLevelAccepted) SET @Level = @TempLevel; RETURN @Level
But after execution I additionally receive records with NULL values.
OK, I've fixed it. Just resolved following inequality: x > 4y + 2. Works but I`m curious why it's not easy to use new column in Where clause.
Thanks millions of !
Just add it as being a column because
MembershipLevel is really a scalar udf. It does not matter concerning the outer code (saved proc or tablek valued udf or Choose)
INSERT @Players (Number, Name, Goals, FuzzyLevel) SELECT Number, Name, Goals, dbo.MembershipLevel(Goals) FROM FuzzyFootballTeam ft WHERE Goals > 2 ORDER BY Number
Findings: I'd clearly specify the column list for @Gamers. I'd also request myself why this is not an in-line table valued function: a multi-statement udf is frequently a performance killer...