我的逻辑一定有点古怪,因为当我测试以确保这些CASE
语句为真时,我不会得到预期的结果。。。
例如,如果Sample1 = 'FI'
、Sample2 = 'GOVT'
和Sample3 = 'GOVT'
,我不会得到'OKFI'
。。。我知道我在某个地方一定有一些古怪的逻辑。。。
关于这些东西的最佳实践,有什么想法吗?
CREATE FUNCTION SampleFunction
(
@Sample1 varchar(50),
@Sample2 varchar(50) = null,
@Sample3 varchar(50) = null
)
RETURNS VARCHAR(255)
AS
BEGIN
declare @output varchar(255)
SET @output =
CASE WHEN @Sample1 = 'CASH' THEN 'CASH'
WHEN @Sample1 = 'ST' THEN
CASE WHEN @Sample2 ='TBILL' THEN
CASE WHEN @Sample3 = 'TBILL' THEN 'OKFI'
WHEN @Sample1 = 'FI' THEN
CASE WHEN @Sample2 = 'GOVT' THEN
CASE WHEN @Sample3 = 'GOVT' THEN 'OKFI'
WHEN @Sample1 = 'DERV' THEN
CASE WHEN @Sample2 = 'CURRCONT' THEN
CASE WHEN @Sample3 = 'FORWARD' THEN 'CASH'
WHEN @Sample3 = 'SPOT' THEN 'CASH'
WHEN @Sample2 = 'SWAP' THEN
CASE WHEN @Sample3 = 'CURRSWAP' THEN 'CASH'
WHEN @Sample3 = 'INDXSWAP' THEN 'OTHER'
WHEN @Sample2 = 'FUTURE' THEN
CASE WHEN @Sample3 = 'COMFUT' THEN 'COM'
WHEN @Sample3 = 'BFUT' THEN 'OKFI'
WHEN @Sample3 = 'NDXFUT' THEN 'OKSHARES'
WHEN @Sample3 = 'EQFUT' THEN 'OKSHARES'
WHEN @Sample3 = 'CURRFUT' THEN 'CASH'
WHEN @Sample2 = 'OPTION' THEN
CASE WHEN @Sample3 = 'CUROPTION' THEN 'CASH' END
ELSE 'OTHER'
END END END
END END END
END END END RETURN @Output
END -- FUNCTION
您应该创建@table变量,甚至是一个具有列Sample1, Sample2, Sample3, Output
的物理永久表-在其中插入所有组合和所需输出,然后根据您的@Sample1
、@Sample2
和@Sample3
变量从中选择Output
。
这样,在CASE中有几十个CASE,你只会迷失方向。
类似这样的东西:
CREATE FUNCTION SampleFunction2
(
@Sample1 VARCHAR(50) ,
@Sample2 VARCHAR(50) = NULL ,
@Sample3 VARCHAR(50) = NULL
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @output VARCHAR(255)
DECLARE @tempResults TABLE
(
Sample1 VARCHAR(50) ,
Sample2 VARCHAR(50) ,
Sample3 VARCHAR(50) ,
[Output] VARCHAR(255)
)
INSERT INTO @tempResults
VALUES ( 'CASH', NULL, NULL, 'CASH' )
,( 'ST', 'TBILL', 'TBILL', 'OKFI' )
,( 'FI', 'GOVT', 'GOVT', 'OKFI' )
,( 'DERV', 'CURRCONT', 'FORWARD', 'CASH' )
,( 'DERV', 'CURRCONT', 'SPOT', 'CASH' )
,( NULL, 'SWAP', 'CURRSWAP', 'CASH' )
,( NULL, 'SWAP', 'INDXSWAP', 'OTHER' )
,( NULL, 'FUTURE', 'COMFUT', 'COM' )
,( NULL, 'FUTURE', 'BFUT', 'OKFI' )
,( NULL, 'FUTURE', 'NDXFUT', 'OKSHARES' )
,( NULL, 'FUTURE', 'EQFUT', 'OKSHARES' )
,( NULL, 'FUTURE', 'CURRFUT', 'CASH' )
,( NULL, 'OPTION', 'CUROPTION', 'CASH' )
SELECT @output = [Output]
FROM @tempResults
WHERE (@Sample1 IS NULL OR Sample1 = @Sample1)
AND (@Sample2 IS NULL OR Sample2 = @Sample2)
AND (@Sample3 IS NULL OR Sample3 = @Sample3)
IF @output IS NULL SET @output = 'OTHER'
RETURN @output
END
SQLFiddle演示
如果您想坚持使用CASE,那么原始查询的问题是将END
s:放错地方
CREATE FUNCTION SampleFunction
(
@Sample1 varchar(50),
@Sample2 varchar(50) = null,
@Sample3 varchar(50) = null
)
RETURNS VARCHAR(255)
AS
BEGIN
declare @output varchar(255)
SET @output =
CASE WHEN @Sample1 = 'CASH' THEN 'CASH'
WHEN @Sample1 = 'ST' THEN
CASE WHEN @Sample2 ='TBILL' THEN
CASE WHEN @Sample3 = 'TBILL' THEN 'OKFI'
END
END
WHEN @Sample1 = 'FI' THEN
CASE WHEN @Sample2 = 'GOVT' THEN
CASE WHEN @Sample3 = 'GOVT' THEN 'OKFI'
END
END
WHEN @Sample1 = 'DERV' THEN
CASE WHEN @Sample2 = 'CURRCONT' THEN
CASE WHEN @Sample3 = 'FORWARD' THEN 'CASH'
WHEN @Sample3 = 'SPOT' THEN 'CASH'
END
END
WHEN @Sample2 = 'SWAP' THEN
CASE WHEN @Sample3 = 'CURRSWAP' THEN 'CASH'
WHEN @Sample3 = 'INDXSWAP' THEN 'OTHER'
END
WHEN @Sample2 = 'FUTURE' THEN
CASE WHEN @Sample3 = 'COMFUT' THEN 'COM'
WHEN @Sample3 = 'BFUT' THEN 'OKFI'
WHEN @Sample3 = 'NDXFUT' THEN 'OKSHARES'
WHEN @Sample3 = 'EQFUT' THEN 'OKSHARES'
WHEN @Sample3 = 'CURRFUT' THEN 'CASH'
END
WHEN @Sample2 = 'OPTION' THEN
CASE WHEN @Sample3 = 'CUROPTION' THEN 'CASH'
END
ELSE 'OTHER'
END RETURN @Output
END -- FUNCTION
SQLFiddle DEMO