Function with CASE Statements

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

Can you try this
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|||The case statement cannot stand alone even within a fuction. Create a variable called @.result and modify your fuction to Select @.result = <your case statement> then return the result.|||

Replace the CASE statment with a series of

IF condition

BEGIN

...

END

That should work

|||CASE is an expression not a control of flow statement. So you need to either assign the variable @.op using the value of the CASE expression or use IF...ELSE statements. Also, you should avoid writing scalar UDFs like this which perform lookup operations and use it in SELECT statements. You will get sub-optimal performance. It is based to model the lookup in a table and then perform a simple SELECT operation. This also gives added flexibility and performs better. Or you can inline the expression in the UDF in a view or TVF and use that instead.|||

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,

function to format Time (24 hours- 5 digits)

Hi

I know this is not a complicated matter.

I'm using this code in my SP to return the time formated in 24 hours

DECLARE @.Hora VARCHAR(5)
SET @.Hora = CONVERT(char(2), DatePart (hh,GetDate())) + ':' + CONVERT(char(2), DatePart (mi,GetDate()))
print @.Hora

te problem is that when the time is any before 10 am for instance 7:23 am the sp returns 7 :23 and I need it ro return 07:23.

What function allows me to insert that '0' to complete the 5 digits format in this case?

thanks

Try this instead:

SELECT My24HrTime = convert( char(5), getdate(), 114 )

For future reference, you may wish to refer to the 'style' chart in Books Online, Topic: 'Cast and Convert'.

(The number 114, above is the 'style' used in the convert() function.)

function selects

I have some code for a duntion that looks like the following
alter FUNCTION fnLevelTable_1 (@.sourceID SMALLINT,
@.sectorID VARCHAR(4),
@.manufacturerID VARCHAR(4),
@.rangeID VARCHAR(4),
@.bodyID VARCHAR(4),
@.transmissionID VARCHAR(4),
@.fuelID VARCHAR(4),
@.doors VARCHAR(4),
@.derivativeID VARCHAR(10),
@.vehicleTypeId VARCHAR(4),
@.levelName varchar(20))
returns @.levelTable table(surveyID VARCHAR(10),
code varchar(100),
description varchar(100)) AS
begin
if @.levelName = 'Sector'
begin
insert @.levelTable
SELECT DISTINCT tblSurveyDerivative.surveyID,
tblMarketSector.sectorID as code,
tblMarketSector.sector_desc as description
FROM tblSurveyDerivative
INNER JOIN basedata.dbo.tblDerivative tblDerivative
ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code = tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = @.sourceID
INNER JOIN basedata.dbo.tblMarketSector tblMarketSector
ON tblMarketSector.sectorID = tblDerivative.sectorID
WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
@.manufacturerID)
AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
@.transmissionID)
AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
AND (@.doors = '0' OR tblDerivative.doors = @.doors)
AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
AND tblSurveyDerivative.sourceID = @.sourceID
end
else if @.levelName = 'Manu'
begin
insert @.levelTable
SELECT DISTINCT tblSurveyDerivative.surveyID,
tblManufacturer.manufacturerID as code,
tblManufacturer.manufacturer_desc as description
FROM tblSurveyDerivative
INNER JOIN basedata.dbo.tblDerivative tblDerivative
ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code =
tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = @.sourceID
INNER JOIN basedata.dbo.tblManufacturer tblManufacturer
ON tblDerivative.manufacturerID = tblManufacturer.manufacturerID
WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
@.manufacturerID)
AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
@.transmissionID)
AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
AND (@.doors = '0' OR tblDerivative.doors = @.doors)
AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
AND tblSurveyDerivative.sourceID = @.sourceID
end
What it does is based upon what the @.levelname variable is, it picks the
corresponding select statement and puts the result into a table to be
returned later to a stored procedure, the problem is I have only shown 2 of
these select statements but there are quite a few, I wanted to know if there
is a better way of writting this, perhaps using something like a case
statement.
Hope this is enough information.
ThanksIt looks like the only thing different between all the select statements is
which table you are getting the 2nd and 3rd column data from.. If that's the
case, (I don;t know how much simpler this will help or not, but)
you can include the joins from all the tables into one query, (They will
have to be Left [Outer] Joins), and then use a case statement to determine
which table to extract the data value from, based on the @.levelname
variable...
Insert @.levelTable(SurveyID, Code, Description)
Select Distinct tblSurveyDerivative.surveyID,
Case @.levelName
When 'Sector' Then tblMarketSector.sectorID
When 'Manu' Then tblManufacturer.sectorID
End,
Case @.levelName
When 'Sector' Then tblMarketSector.sector_desc
When 'Manu' Then tblManufacturer.sector_desc
End
From tblSurveyDerivative
Join basedata.dbo.tblDerivative tblDerivative
On tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
AND tblDerivative.derivative_code =
tblSurveyDerivative.derivative_code
AND tblDerivative.sourceID = @.sourceID
Left Join basedata.dbo.tblMarketSector tblMarketSector
On tblMarketSector.sectorID = tblDerivative.sectorID
Left Join basedata.dbo.tblManufacturer tblManufacturer
On tblMarketSector.sectorID = tblDerivative.sectorID
Where (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
@.manufacturerID)
AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
@.transmissionID)
AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
AND (@.doors = '0' OR tblDerivative.doors = @.doors)
AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
AND tblSurveyDerivative.sourceID = @.sourceID
"Phil" wrote:

> I have some code for a duntion that looks like the following
> alter FUNCTION fnLevelTable_1 (@.sourceID SMALLINT,
> @.sectorID VARCHAR(4),
> @.manufacturerID VARCHAR(4),
> @.rangeID VARCHAR(4),
> @.bodyID VARCHAR(4),
> @.transmissionID VARCHAR(4),
> @.fuelID VARCHAR(4),
> @.doors VARCHAR(4),
> @.derivativeID VARCHAR(10),
> @.vehicleTypeId VARCHAR(4),
> @.levelName varchar(20))
> returns @.levelTable table(surveyID VARCHAR(10),
> code varchar(100),
> description varchar(100)) AS
>
> begin
> if @.levelName = 'Sector'
> begin
> insert @.levelTable
> SELECT DISTINCT tblSurveyDerivative.surveyID,
> tblMarketSector.sectorID as code,
> tblMarketSector.sector_desc as description
> FROM tblSurveyDerivative
> INNER JOIN basedata.dbo.tblDerivative tblDerivative
> ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
> AND tblDerivative.derivative_code = tblSurveyDerivative.derivative_
code
> AND tblDerivative.sourceID = @.sourceID
> INNER JOIN basedata.dbo.tblMarketSector tblMarketSector
> ON tblMarketSector.sectorID = tblDerivative.sectorID
> WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
> @.manufacturerID)
> AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
> AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
> AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
> AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
> @.transmissionID)
> AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
> AND (@.doors = '0' OR tblDerivative.doors = @.doors)
> AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
> AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
> AND tblSurveyDerivative.sourceID = @.sourceID
> end
> else if @.levelName = 'Manu'
> begin
> insert @.levelTable
> SELECT DISTINCT tblSurveyDerivative.surveyID,
> tblManufacturer.manufacturerID as code,
> tblManufacturer.manufacturer_desc as description
> FROM tblSurveyDerivative
> INNER JOIN basedata.dbo.tblDerivative tblDerivative
> ON tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
> AND tblDerivative.derivative_code =
> tblSurveyDerivative.derivative_code
> AND tblDerivative.sourceID = @.sourceID
> INNER JOIN basedata.dbo.tblManufacturer tblManufacturer
> ON tblDerivative.manufacturerID = tblManufacturer.manufacturerID
> WHERE (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
> @.manufacturerID)
> AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
> AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
> AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
> AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
> @.transmissionID)
> AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
> AND (@.doors = '0' OR tblDerivative.doors = @.doors)
> AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
> AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
> AND tblSurveyDerivative.sourceID = @.sourceID
> end
>
> What it does is based upon what the @.levelname variable is, it picks the
> corresponding select statement and puts the result into a table to be
> returned later to a stored procedure, the problem is I have only shown 2 o
f
> these select statements but there are quite a few, I wanted to know if the
re
> is a better way of writting this, perhaps using something like a case
> statement.
> Hope this is enough information.
> Thanks|||Hi CBretana,
Thanks very much for that, I had the principal right just couldn't seem to
write it down, thanks again for you help!!
Phil
"CBretana" wrote:
> It looks like the only thing different between all the select statements i
s
> which table you are getting the 2nd and 3rd column data from.. If that's t
he
> case, (I don;t know how much simpler this will help or not, but)
> you can include the joins from all the tables into one query, (They will
> have to be Left [Outer] Joins), and then use a case statement to determine
> which table to extract the data value from, based on the @.levelname
> variable...
> Insert @.levelTable(SurveyID, Code, Description)
> Select Distinct tblSurveyDerivative.surveyID,
> Case @.levelName
> When 'Sector' Then tblMarketSector.sectorID
> When 'Manu' Then tblManufacturer.sectorID
> End,
> Case @.levelName
> When 'Sector' Then tblMarketSector.sector_desc
> When 'Manu' Then tblManufacturer.sector_desc
> End
> From tblSurveyDerivative
> Join basedata.dbo.tblDerivative tblDerivative
> On tblDerivative.derivativeID = tblSurveyDerivative.derivativeID
> AND tblDerivative.derivative_code =
> tblSurveyDerivative.derivative_code
> AND tblDerivative.sourceID = @.sourceID
> Left Join basedata.dbo.tblMarketSector tblMarketSector
> On tblMarketSector.sectorID = tblDerivative.sectorID
> Left Join basedata.dbo.tblManufacturer tblManufacturer
> On tblMarketSector.sectorID = tblDerivative.sectorID
> Where (@.manufacturerID = 'null' OR tblDerivative.manufacturerID =
> @.manufacturerID)
> AND (@.sectorID = 'null' OR tblDerivative.sectorID = @.sectorID)
> AND (@.rangeID = 'null' OR tblDerivative.rangeID = @.rangeID)
> AND (@.bodyID = 'null' OR tblDerivative.bodyID = @.bodyID)
> AND (@.transmissionID = 'null' OR tblDerivative.transmissionID =
> @.transmissionID)
> AND (@.fuelID = 'null' OR tblDerivative.fuelID = @.fuelID)
> AND (@.doors = '0' OR tblDerivative.doors = @.doors)
> AND (@.vehicleTypeId = 'null' OR tblDerivative.typeID = @.vehicleTypeId)
> AND (@.derivativeID = '0' OR tblDerivative.derivativeID = @.derivativeID)
> AND tblSurveyDerivative.sourceID = @.sourceID
>
> "Phil" wrote:
>

Full-Text Search Query Question - Performance

I have a table with 3M rows that contains a varchar(2000) field with
various keywords. Here is the table structure:

PKColumn
ImageID
FullTextColumn

There is an association table:
ImageID
ContractID

Now, I want to do a query where the ContractID = x and Contains some
word in the FullTextColumn. There is an association table that maps
Images to Contracts - so I can't use the trick of putting the Contract
code in the FullTextColumn.

I'm finding that first the FTS service is performing a search on the
Keyword (which can take a long time if 100K rows are returned) then
joining to the association table for the particular contract.

Is there anyway to make this faster by telling the FTS service, only
search this subset of rows for the keyword based on the contract.

Sorry if this sounds convoluted. Appreciate any help you can suggest.

Thanks!jimdandy@.shaw.ca (Jim Dandy) wrote in message news:<705c8539.0405271024.5ce1d19b@.posting.google.com>...
> I have a table with 3M rows that contains a varchar(2000) field with
> various keywords. Here is the table structure:
> PKColumn
> ImageID
> FullTextColumn
> There is an association table:
> ImageID
> ContractID
> Now, I want to do a query where the ContractID = x and Contains some
> word in the FullTextColumn. There is an association table that maps
> Images to Contracts - so I can't use the trick of putting the Contract
> code in the FullTextColumn.
> I'm finding that first the FTS service is performing a search on the
> Keyword (which can take a long time if 100K rows are returned) then
> joining to the association table for the particular contract.
> Is there anyway to make this faster by telling the FTS service, only
> search this subset of rows for the keyword based on the contract.
> Sorry if this sounds convoluted. Appreciate any help you can suggest.
> Thanks!

You might want to post this in microsoft.public.sqlserver.fulltext to
see if you get a better reply.

Simon

FullText Search data model for speed

Which method is faster for full-text search
One row big varchar field
comment VARCHAR(1000)
on lots of small varchar fieds
like 10 rows comment VARCHAR(100)
Message posted via http://www.sqlmonster.com
Kuido,
Could you post the full output of the below SQL code as this is very helpful
to understanding your environment as well as troubleshooting SQL FTS issues
as both SQL Server version and the OS platform play a part in FTS
performance tuning:
use <your_database_name>
SELECT @.@.version
SELECT @.@.language
SELECT count(*) from <your_true_FT-enabled_table_name>
The biggest factor in both FT Indexing and FT Searching is the number of
rows in your FT-enable table. Specifically, for a table with one row of
large text will be just as fast as 10 rows with smaller text. Furthermore,
in this situation (1 row table vs. 10 row table), T-SQL LIKE will be faster
as with very small tables all the rows will fit in one or a couple of data
pages, while the CONTAINS FTS queries will have to use the external MSSearch
service.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:35824a78f71441eda1460869a906ea07@.SQLMonster.c om...
> Which method is faster for full-text search
> One row big varchar field
> comment VARCHAR(1000)
> on lots of small varchar fieds
> like 10 rows comment VARCHAR(100)
> --
> Message posted via http://www.sqlmonster.com
|||SQL FTS query performance is most sensitive to the number of rows returned
in a query. So if you can limit the number of rows returned you will get
better performance. So 1 big varchar field would probably offer better
performance.
However if you can partition your table into sub tables, you will get even
better performance this way as long as you are only doing a single hit on
MSSearch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:35824a78f71441eda1460869a906ea07@.SQLMonster.c om...
> Which method is faster for full-text search
> One row big varchar field
> comment VARCHAR(1000)
> on lots of small varchar fieds
> like 10 rows comment VARCHAR(100)
> --
> Message posted via http://www.sqlmonster.com
|||I'm just planning database application
SQL-server will be Microsoft SQL Server 2000
Language - eesti (Estonian)
and there will be 15 000 000 rows in database
Message posted via http://www.sqlmonster.com
|||Kuido,
Then you should review all the SQL FTS links and resources at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
You should also review SQL Server 2000 Books Online (BOL) and using the
search tab, search on "full text" (with the double quotes) and especially
the BOL title: "Full-text Search Recommendations". Additional, since your
language will be Estonian, you will need to use the "neutral wordbreaker" as
Estonian is not one of the subset of languages supported by SQL FTS.
Specifically, for each of your FT-enabled columns, set the "Language for
Word Breaker" to Neutral.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:677e90c6408e4927ad6a290c05d61745@.SQLMonster.c om...
> I'm just planning database application
> SQL-server will be Microsoft SQL Server 2000
> Language - eesti (Estonian)
> and there will be 15 000 000 rows in database
> --
> Message posted via http://www.sqlmonster.com

FullText Search

I have create a table called tblcatalog with colums id(identity,primary key) and contents(varchar(100))

I have then created a full text catalog on that table and populated it.

Then i wrote the following query
"select contents from tblcatalog where contains(contents,'sample data')"
It is fetching 0 records even though u have 5 records with entries "sample data"

Can anyone tell me the solution immediately

bye
shankyImmediately? Have you tried everything you could think of before asking for help? It'll take me 20 minutes or so to setup FullText Search, and will take you probably much less to experiment with it.|||when you say you populated it did you create an initial full population?|||Forgive me if this is basic to you. Just trying to be thorough. :)
In Enterprise Manager, right click on your table. Choose 'Full Text Index Table' (MS Search service must be started for this to appear.) Choose 'Start full population.' Rerun your query. If it works, you must not have really done a full population yet. In that case you may want to set up incremental or full populations using the 'Schedules..' option on the same context menu...
If not, I have no clue what the problem is! :)