December 15, 2015 at 4:20 pm
I have a scalar function where I am trying to check which category the product falls under based on a number.
The output should be as below.
prod a - cat 1
prod b - cat 2
prod c - cat 3
prod d - cat 4
The cat ranges are
1 - < 0.5
2 - < 1
3 - < 2
4 - > 2
When I debug, the cat 1 is assigned for the product but then it does not exit out of the function. I am new to scalar functions. Any help would be great.
create table tblProd
(
prod nvarchar(25),
reg decimal,
cat int
)
insert into tblProd (prod, reg, cat)
values('a', 0.25, 0)
insert into tblProd (prod, reg, cat)
values('b', 0.75, 0)
insert into tblProd (prod, reg, cat)
values('c', 1.25, 0)
insert into tblProd (prod, reg, cat)
values('d', 3.25, 0)
IF OBJECT_ID (N'dbo.fn_getprodCat', N'FN') IS NOT NULL
DROP FUNCTION fn_getprodCat;
GO
CREATE FUNCTION [dbo].[fn_getprodCat]
(
@prod decimal,
@reg nvarchar(255)
)
RETURNS int --@prodCat
AS
BEGIN
declare @prodCat int
set @prodCat = 0
if @prod = 0 or @reg is null
begin
set @prodCat = 0
return @prodCat
end
declare @minprodThreshold int
set @minprodThreshold = 0
declare @maxprodThreshold int
set @maxprodThreshold = 0
declare @thresholdRange int
set @thresholdRange = 0
declare @catRange int
set @catRange = 0
set @minprodThreshold = (select min(prodThreshold) from Sales_Data_Results_Mgmt_prodCategory
where regName = @reg)
set @maxprodThreshold = (select max(prodThreshold) from Sales_Data_Results_Mgmt_prodCategory
where regName = @reg)
set @catRange = (select max(Category) from Sales_Data_Results_Mgmt_prodCategory
where regName = @reg)
select @prodCat = Category from Sales_Data_Results_Mgmt_prodCategory
where regName = @reg and
@prod >= @maxprodThreshold
if @prodCat > 0
begin
return @prodCat
end
select @prodCat = Category from Sales_Data_Results_Mgmt_prodCategory
where regName = @reg and
@prod <= @minprodThreshold
if @prodCat > 0
begin
return @prodCat
end
declare @idx int
set @idx = 1
while (@catRange between 1 and @catRange)
begin
set @idx = @idx + 1
if @idx = @catRange
begin
break
end
select @prodCat = Category from Sales_Data_Results_Mgmt_prodCategory
where regName = @reg and
(@prod > @minprodThreshold and @prod < @maxprodThreshold) and
Category = @idx
if @prodCat > 0
begin
return @prodCat
end
else
begin
continue
end
end
return @prodCat
END
December 15, 2015 at 4:49 pm
First, I'd write it as an inline table valued function, not a scalar function. Also, from what I can see it is rather simple. Does the below, even though it doesn't update the table, return what you are expecting?
Also, I dropped the tbl from your table name.
create function dbo.itvCats (
@reg decimal(10,2)
)
returns table
with schemabinding
return (
select case when @reg >= 2 then 4
when @Reg >= 1 then 3
when @reg >= 0.5 then 2
else 1
end Cat
);
go
select
p.prod,
p.reg,
ca.Cats
from
dbo.Prod p
cross apply (select Cat from dbo.itvCats(p.reg))ca(Cats);
go
December 15, 2015 at 7:06 pm
I would not suggest hardcoding category ranges.
They should be put in a table.
This approach dramatically reduces the need in agile development, continuous deployment and questionable technics which became very fashionable because of low coding standards dominating in the industry.
In the following code I use table variables, but it must be a static table in the actual database.
DECLARE @CatRanges TABLE (
CatNo INT,
RangeFrom DECIMAL(10,2),
RangeTo DECIMAL (10,2)
)
INSERT INTO @CatRanges
( CatNo ,
RangeFrom ,
RangeTo
)
SELECT 1, 0, 0.5
UNION
SELECT 2, 0.5, 1
UNION
SELECT 3, 1, 2
UNION
SELECT 4, 2, 9.999E7
declare @tblProd table
(
prod nvarchar(25),
reg decimal(10,2),
cat int
)
insert into @tblProd (prod, reg, cat)
values('a', 0.25, 0)
insert into @tblProd (prod, reg, cat)
values('b', 0.75, 0)
insert into @tblProd (prod, reg, cat)
values('c', 1.25, 0)
insert into @tblProd (prod, reg, cat)
values('d', 3.25, 0)
SELECT p.prod ,
p.reg ,
R.CatNo
FROM @tblProd p
INNER JOIN @CatRanges R ON p.reg >= R.RangeFrom AND p.reg < R.RangeTo
_____________
Code for TallyGenerator
December 15, 2015 at 8:05 pm
Thanks, the categories are in a table, but not aranged as from and to values.
Also, number of categories may change from one product to another. Some have 3 and others have 4.
December 15, 2015 at 9:50 pm
tinausa (12/15/2015)
Also, number of categories may change from one product to another. Some have 3 and others have 4.
Then there must be additional column in the CatRange table:
DECLARE @CatRanges TABLE (
ProductID int,
CatNo INT,
RangeFrom DECIMAL(10,2),
RangeTo DECIMAL (10,2)
)
And you need to match by ProductID as well.
_____________
Code for TallyGenerator
December 16, 2015 at 9:05 am
tinausa (12/15/2015)
Thanks, the categories are in a table, but not aranged as from and to values.Also, number of categories may change from one product to another. Some have 3 and others have 4.
If you want real help, help us. You need to provide the DDL (CREATE TABLE statement) for the table(s) involved, sample data (as INSERT statements) for the table(s) that is representative of your problem domain, and expected results based on the sample data.
Test all the code you are going to post in an empty database to be sure all the code runs properly. The sample should be just that, sample data not production data.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply