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
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.
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
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
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
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
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?
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 ?
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
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
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
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?
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
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 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')
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?
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
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?
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
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
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.
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
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'.
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
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?
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.
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