Create User Function W/Case

Jul 23, 2005

I keep getting an error message "incorrect syntax near keyword case"
when trying to run this:

USE DEDUPE
GO

CREATE FUNCTION fnCleanString
(
@mString varchar (255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE
@mChar char(1),
@msTemp varchar(255),
@miLen int,
@i int,
@iAsc int
BEGIN
set @mChar = ''
set @msTemp = ''
set @miLen = Len(@mString)
set @i = 1
while @i <= @miLen
begin
set @mChar = substring(@mString,@i,1)
set @iAsc = Ascii(@mChar)
case
when @iAsc >= 87 And iAsc <= 122 Then set @mChar = @mChar
when iAsc >= 65 And iAsc <= 90 Then set @mChar = @mChar
when iAsc >= 49 And iAsc <= 57 Then set @mChar = @mChar
else @mChar = ""
end
set @msTemp = @msTemp & @mChar
set @i = @i + 1
end
END
RETURN @msTemp
END

Can anybody point out what I'm doing wrong?
Thanks.
Randy

View 3 Replies


ADVERTISEMENT

Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.

Sep 6, 2007

I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem  here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.

View 2 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View 3 Replies View Related

Using RAND Function In User Defined Function?

Mar 22, 2006

Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View 1 Replies View Related

Case Function In T-SQL

Sep 13, 2007

Wondering if possible to use case function in where clause of T-SQL statement. I have this application that needs to get the recordsets based on the day of the date selected that is @dDay in one of these set of values {0,7,14,21, more). Though, I know that if I use either of this:
datediff(dd,@startdate,@today) = @dDay
or convert(varchar(12), @startDate, 101) between convert(varchar(12), @today, 101) and convert(varchar(12), dateadd(dd,@dDay, @today), 101)
It will work for only the values that are specific as in number but what of if "more" is selected which means that from that day upward, can i use this T-SQL to accomplish this. PLease help
Any suggestion is welcome. thanks
 
 

View 6 Replies View Related

IIF Function Or Case

Nov 27, 2006

Hi I am wanting to use some thing like the IIF function in Access in a SQL view I did some looking n your Forum and found the case function its awsome does what I need but i can not use it in a view. Does any one have an alternate solution .. Thanks Jakes

My "Case"


USE SysproCompanyB
GO
SELECT dbo.ZZCuCostValue.Supplier, dbo.ZZCuCostValue.StockCode, dbo.ZZCuCostValue.[Year], dbo.ZZCuCostValue.[Month],'RandCost' =
CASE
WHEN BuyMulDiv IS NULL THEN '0'
WHEN BuyMulDiv = 'M' THEN round(dbo.ZZCuCostValue.UnitCost * dbo.ZZCuCostValue.ExchangeRate,4)
WHEN BuyMulDiv = 'D' THEN round(dbo.ZZCuCostValue.UnitCost / dbo.ZZCuCostValue.ExchangeRate,4)
ELSE 0
END
FROM dbo.ApSupplier INNER JOIN
dbo.TblCurrency ON dbo.ApSupplier.Currency = dbo.TblCurrency.Currency INNER JOIN
dbo.ZZCuCostValue ON dbo.ApSupplier.Supplier = dbo.ZZCuCostValue.Supplier

GO

View 6 Replies View Related

Case Function Again:(

May 9, 2008


Hi,

I'd like to ask my question with the data sample this time. Below is my table with 3 phone numbers, null, zero, less than 10 digits, I'd to get as an out put phone with not null, zero, or less than 10 digits .I couldn't run that CASE statement. Please HELP ......SOS.. Thanks






Home_phone
Work_phone
cell_phone_1

0
0
0

12344567890
0
0

0
NULL
32547821

12344567891
NULL
0

12344567892
NULL
0

0
0
12344567893

0
0
12344567894

0
0
NULL

12344567895
0
0

0
12345
0

12344567896
0
0


12344567897
0

Null
0
0

12344567898
0
0









OUTPUT



12344567890



12344567891



12344567892



12344567893



12344567894



12344567895



12344567896



12344567897



12344567898


View 7 Replies View Related

Case / Switch Function Help

Jun 9, 2004

Starting to play around with SQL server at work and this is my question:

In the query design mode in access I can make one of the fields an expression that is driven by a built-in switch function.
i.e. Switch([CategoryName] Like "Beverage","Drink",[CategoryName] Like "Cheese","Dairy")
This results in the additional column field I created to display "Drink" for each record that has the CategoryName = "Beverage", and "Dairy" for "Cheese".

Can I do something like this in SQL server in the view designer itself, or do I need to make a user defined function and call it?

Thanks in advance for any help.

View 3 Replies View Related

Function Or CASE Statement?

Feb 25, 2008



Hi,

I have a Query some thing like this,

Select Table1.Code, 'field2' = CASE




WHEN 1 THEN (Select name From Table2 where Table2.Code=Table1.Code)
WHEN 2 THEN (Select name From Table3 where Table3.Code=Table1.Code)
WHEN 3 THEN (Select name From Table4 where Table4.Code=Table1.Code)
END

FROM Table1

Do I need to use Function instead of CASE for better performance ?


Regards

Mujeeb






View 5 Replies View Related

How To Recall Column Name Defined In CASE Function

Aug 17, 2007

Hello all,

I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @Term, SQL Server returned an error: Invalid column name 'SLA'

If anyone knows a solution, please kindly let me know.

Thank you!

Here is the sample code:

ALTER PROCEDURE [dbo].[sp_premium_register]
@PolicyType AS VARCHAR(10),
@ReportFrom AS DATETIME,
@ReportTo AS DATETIME,
@Business AS VARCHAR(1),
@Term AS VARCHAR(1)

SELECT
ColumnA,
ColumnB,

SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L'
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S'
END

FROM DNIssue D
LEFT OUTER JOIN KILRIShare S
ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo)
LEFT OUTER JOIN KILPolicy P
ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo)
LEFT OUTER JOIN v_report_KILDNFund F
ON (D.DebitNote = F.DebitNote)
LEFT OUTER JOIN PolicyProfile R
ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType


WHERE
SLA = @Term

Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDate

View 3 Replies View Related

T-SQL (SS2K8) :: Case Query - Unpivot Function

May 6, 2015

I've this result from my 'case' query;

Jan Feb Mar April
1 2 3 4

I want ;

Month Value
JAN 1
Feb 2
Mar 3
April 4

View 3 Replies View Related

Can Insert Statement Works In Case When Function

Nov 5, 2007

Hi

Im beginer in sql,Please guide

can insert statement works fine in case when function

for example

case when condition1=true then (first insert statement based on some condition) when condition2=true then (second insert statement based on some other condition)
end

View 5 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007



Hi,



I am trying to create a inline function which is listed below.



USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO



while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.



Pleae help me to fix this error



thanks

Purnima

View 3 Replies View Related

SQL 2012 :: Using Count Function And Case In One Select Statement

Jul 9, 2014

I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.

Right now I am achieving this by the following case statement

SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'
WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'
WHEN Stcount>50 THEN 'Big Class'
END
FROM(
select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl

But can I do this with just one select statement?

View 2 Replies View Related

Problem With PATINDEX Function For Case-sensitive Information

Aug 21, 2007

Hi,

My database is not case-sensitive, but I want output like...

SELECT patindex('%[A-Z]%','gaurang Ahmedabad')

The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.

Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).

Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?

Thanks,

Gaurang Majithiya

View 7 Replies View Related

How To Create Index (Case When)

Oct 20, 2004

How to create index when there is a SQL statement like
Select count(1) as [Total],
sum(Case when Field1 < Field2 then 1 else 0 End) as [Selected]
from Table1


Thx in Adv
XLDB

View 3 Replies View Related

Joining On And Grouping By CASE Function Column Alias (URGENT)

Apr 14, 2004

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
CASE
WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
END,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC Ross

View 5 Replies View Related

Create New Login Non Case Sensitive

Jan 12, 2007

I am trying to create a new login on a database, and I want that username and password NOT to be case sensitive. Do I have to set this at the server level or can I set this in the SQL script?

Here is the script I am currently using...

IF EXISTS (SELECT * FROM dbo.sysusers WHERE [name] = 'UName')

BEGIN

EXEC sp_dropuser 'UserName'

EXEC sp_droplogin 'UserName'

END

EXEC sp_addlogin 'UserName', 'Password', 'Database'

GO

EXEC sp_adduser 'UserName', 'Password', 'db_datareader'

GO

Any help would be appreciated...



David

View 3 Replies View Related

Transact SQL :: CASE With Subselect And DATEADD Function Returning NULL Values

Jun 15, 2015

I'm running the following test query on a single table:

SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp),
CASE DATENAME(dw, sph.datestamp)    
WHEN 'Monday' then 'Monday'  
ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA') 
END AS TestCase,

[Code] ....

And here's an example of the output I'm getting:

Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?

View 7 Replies View Related

Need To Create Case Sensitive Uniqiue Indexes

Jul 20, 2005

Hi,How Can I create a case sensitive unique index so that A1 and a1 are treatedas different ?I dont mind if I have to make a global DB change.Steve

View 1 Replies View Related

First Time SELECT CASE Statement User

Aug 31, 2006

Hi, I have created a login page (webform1) that enables me to enter my user number and password and if correct it re-directs to webform 2 where all data related to the usernumber that was entered in the login page, appears.  Now I want to be able to set different re-direct pages dependant upon different users as some may require more privileges then others. As for my query I have 2 types of users: staff and managers. I want to set the login page so that if staff logins in it goes webform 3 and if manager logins in it goes webform 4. My table in the Sql database is called Users & the fields are: unumber(pk), pwd, userRole, forename, surname.   I have been advised to use CASE statements, yet I do not know how to use them. Below  have left my code of a simple login without the userRole validation. Please help! Sub cmdLogin_ServerClick          If ValidateUser(txtUserNumber.Value, txtUserPass.Value) Then            Dim tkt As FormsAuthenticationTicket            Dim cookiestr As String            Dim ck As HttpCookie             tkt = New FormsAuthenticationTicket(1, txtUserNumber.Value, DateTime.Now(), _      DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data")            cookiestr = FormsAuthentication.Encrypt(tkt)            ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr)            If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration            ck.Path = FormsAuthentication.FormsCookiePath()            Response.Cookies.Add(ck)             Dim strRedirect As String            strRedirect = Request("ReturnURL")            If strRedirect <> "" Then                Response.Redirect(strRedirect, True)            Else                strRedirect = "webform1.aspx"                Response.Redirect(strRedirect, True)            End If        Else            Response.Redirect("webform3.aspx", True)        End If     End Sub 

View 1 Replies View Related

Why Are CLR User-defined-types Case-sensitive?

Aug 24, 2006

Hi,

I'm wondering, why CLR user-defined-types are case-sensitive in T-SQL?

When I create an udt with vb.net, e.g. udtPoint with properties X and Y and a method ToString(), I have to write udtPoint.X ..., udtPoint.x would raise an error. udtPoint.tostring() isn't working, too.

I like vb, because you don't have to remember whether syntax is upper or lower case. Same is with T-SQL, there's no difference between select * from table and Select * FROM Table. Intellisense would by great, but is not yet implemented .

I'd like to put this topic under suggestions (in my opinon it's a bug), what do you think about it?

Many thanks for your answers!

View 1 Replies View Related

How To (the Steps) To Create DTS Package In 2005 As In The Case Of SQL 2000

May 30, 2007

Hi,



Warm Wishes for all !!



I used to create DTS Package in SQL Server 2000 and some times schedule accordingly , but in SQL 2005 although there is a provision to buid the same through integration service but couldnt able to succeed.

( the goal is to transfer the data from one table to another in different database)



Is there any document or any one can provide the steps involved in developing DTS Package in SQL 2005



Thanks

Aravind

View 1 Replies View Related

Raising User Friendly Message In Case Of DB Error

Oct 20, 2007

Hi,I am using sql server 2005 and  i am having table with a column which has unique key constraint.So if the user tries to enter the duplicate value for that particular column and try to save it ,it errors out in runtime..how do i raise the user friendly error message in this DB error, when user tries to save it.Thanks

View 9 Replies View Related

Case Statement - Calculate No Of Days Based On User Input Start And End Date

Jan 5, 2015

I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.

say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:

PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days

For this :

I calculated datediff + 1 and got sub jobs days BUT

say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec

so on paper I calulated as :

1-31 Dec 2014
PhaniMarketing NULL (Do not fall in Req Dt)
PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec)
PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days)
Total days 31

HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.

View 4 Replies View Related

CREATE FUNCTION??

Mar 21, 2006

Hello!How can I make this function in MS SQL:CREATE FUNCTION id_name() RETURNS INTEGER AS 'SELECT MAX(ID)+1 FROM Test;'Thanks!

View 2 Replies View Related

Create Function Help

Mar 15, 2008

I have the following function that I was able to put together with the help of the following article http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx but I'm having some problems with it any help would be greatly appreciated.


USE database1
GO
CREATE FUNCTION dbo.Concatdwg_Seq (@prt_Mark CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output =CASE @Output
WHEN '' THEN dwg_Seq
ELSE @Output + ', ' + dwg_Seq
END
FROM dbo.Un_Combined
WHERE prt_Mark = @prt_Mark
ORDER BY dwg_Seq
RETURN @Output
END
GO



prt_Mark | dwg_Seq
12 | 12a,23b
25c | 1b,5e,8d,100as

I get the following errors

Msg 325, Level 15, State 1, Line 2
Incorrect syntax near 'FUNCTION'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@prt_Mark".
Msg 178, Level 15, State 1, Line 17
A RETURN statement with a return value cannot be used in this context.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'To'.

View 5 Replies View Related

User ID Function

Jul 17, 2007

Greetings all

I have a question on using this function to limit the information to an individual. I've used this before on a report so that when an individual logs in he sees only his information. Now for this report I want a supervisor to only see his subordinates. Am I right in my train of thought here? I should be able to establish a relationship between the supervisor_id and the employee to limit the report to only his group?


thanks in advance -- if someone has done something similar to this and can share some specifics that would be great

regards
km

View 2 Replies View Related

Nested CASE WHEN - Create A Field To Show If Measure Hits Threshold Or Not To Be Later Picked Up In SSRS

Oct 28, 2015

I am trying to script a case when to achieve the following.

I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?

CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'Is this at all possible?

View 2 Replies View Related

Help Create Function Day_week_month

Jul 26, 2007

I want to divide day of month as

example: month is July

week1: 02-07

week2: 09-14
week3: 16-21
week4: 23-28

week5: 30-31

--========Sunday is not including

example: month is August:

week1: 01-04

week2: 06-11

week3: 13-18

week4: 20-25

week5: 27-31
but if December:week1: 01

week2: 03-08

week3: 10-15

week4: 17-22

week5: 24-29week6:31  please help me!thanks so much!  

View 1 Replies View Related

Create Function In Ms-sql Server 7.0

Aug 5, 2002

how do create function in sql server 7.0

View 1 Replies View Related

Unable To Create Function

Mar 28, 2002

Hi All,

I'm running SQL Server 2002 and trying to create a User Defined Function. However, everytime I try to save the script I get Error 170 Incorrect Syntax near 'FUNCTION'.

This happens if I create the Function from Code or use the Enterprise Manager. I'm logged in with 'sa' privs, so I don't think it's a privilege issue. I'm well confused.

Anyone help?

TIA

William.

View 1 Replies View Related

Is There Anyway To Create A View Within A Function

Dec 5, 2007

Hi, Is there anyway to create a view within a Function? The code is as below. I execute the code between "BEGIN" and "END". SQL Analyzer report error that said

'CREATE VIEW' must be the first statement in a query batch.

I could make the variable constant in SELECT statement, but I'm wondering if there is a way to make CREATE VIEW as part of code piece.

CREATE Function GetCommonFailurs()
AS
BEGIN
IF OBJECT_ID(N'CommonFailures') IS NOT NULL
DROP VIEW CommonFailures
DECLARE @Run1Result as char(4), @Run2Result as char(4);
SET @Run1Result='Fail';
SET @Run2Result='Fail';
CREATE VIEW CommonFailures
AS
SELECT Run1Failures.RunID as Run1ID,
Run2Failures.RunID as Run2ID,
@Run1Result as 'Run1Result',
@Run2Result as 'Run2Result',
Run1Failures.SmartyDOTXMLFilePath as Run1SmartyFilePath,
Run2Failures.SmartyDOTXMLFilePath as Run2SmartyFilePath,
Run1Failures.SDET as SDET,
Run1Failures.CommandLine as CommandLine,
Run1Failures.OutputFilePath as OutputFilePath
FROM Run1Failures
INNER JOIN Run2Failures
ON Run1Failures.TestID = Run2Failures.TestID
END

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved