Hi,
Uses: SQL Server 2000 + Winxp PRO;
I Created a Function as below:
<CODE>
CREATE FUNCTION [GetDestinationOperator] (@Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @Op VARCHAR(15)
SET @Dest = RTRIM(@Dest)
CASE LEN(@Dest)
WHEN 13 THEN SET @Op = 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@Dest , 1 , 3 )
WHEN '071' THEN SET @Op = 'MOBITEL'
WHEN '072' THEN SET @Op = 'CELTEL'
WHEN '077' THEN SET @Op = 'DIALOG'
WHEN '078' THEN SET @Op = 'HUTCH'
ELSE SET @Op = 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@Dest , 1 , 1 )
WHEN '2' THEN SET @Op = 'SLT'
WHEN '4' THEN SET @Op = 'SUNTEL'
WHEN '5' THEN SET @Op = 'LANKA BELL'
ELSE SET @Op = 'LOCAL'
END
WHEN 3 THEN SET @Op = 'INTERNAL'
ELSE SET @Op = 'UNKNOWN'
END
RETRUN @Op
END
<\CODE>
However when I checked the Code I written I get error Message as saying:
Error = 156:
Incorrect Syntax near the Keyword CASE
Incorrect Syntax near the Keyword WHEN
.
.
.
However when I executed the Query which I used with my database mentioned below, I get no errors:
<CODE>
SELECT CASE LEN(CalledNo)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (CalledNo , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (CalledNo , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END, CalledNo
FROM PABX
WHERE ExtNo = 204
<\CODE>
What might be the problem here
Regards,
Hifni
Function with CASE Statements
Andrea Y
CREATE FUNCTION GetDestinationOperator (@Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @Op VARCHAR(15)
SET @Dest = RTRIM(@Dest)
select @op=
CASE LEN(@Dest)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@Dest , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@Dest , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END
RETURN @Op
END
Bhonda
Hi Everyone,
Thanks for all of your responses. For Eisa, I tried your sql and it did work wery well. Thanks for it and Umarchandra who gave a technical detail of it, thanks as well and for the rest.
Regards,
jay K B
Replace the CASE statment with a series of
IF condition
BEGIN
...
END
That should work
RBA
jeff_______