Case, If/else Sql Server
Mar 12, 2008
Create PROC ReportRequiredBatch
@Plan varchar(5)=null,
@ID varchar(5)=null,
@UserID int
AS
if PlanID= '0' or @Plan IS NULL or @Plan='-1' SET @Plan= ''
if @ID='0' or @ID IS NULL or @ID='-1' SET @ID= ' '
SELECT DISTINCT ICCode, HistoryCode, PlanID, UserID from PlanTable
Could I have done the upper given approach the following way:
CREATE PRoc ReportRequiredBatch
Select Distinct ICCode, HictoryCode, PlanIDCode Case PlanID When '0' or 'null or '-1' Then ' ',
case ID when '0' or 'null or '-1' Then ' ' '
My query might be not exact , free or error. but u get the idea.
What I am trying to see if we can complety take out the If/else and just work on the case.
So is it doable??
For example, in C# or visual basic
we can use if/else and also the select or case. so is it the samething.
or is it better to work with one rather than other.
View 4 Replies
ADVERTISEMENT
Aug 31, 2015
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies
View Related
Jan 6, 2005
Hello:
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Any suggestions?
View 4 Replies
View Related
May 4, 2007
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
View 3 Replies
View Related
Aug 17, 2005
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies
View Related
Jul 20, 2005
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
View 2 Replies
View Related
May 29, 2008
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
Jacob
View 5 Replies
View Related
May 4, 2015
I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies
View Related
Aug 19, 2007
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
View 2 Replies
View Related
Nov 5, 2007
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
Thanks,
Jason
View 1 Replies
View Related
Aug 1, 2007
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
View 1 Replies
View Related
Apr 18, 2008
what is the use of case in sql server
View 3 Replies
View Related
Mar 3, 2006
Anyone have much experience with SQL Server T-SQL CASE statement?
I have some code that someone else wrote that looks like this:
SELECT m.messageID, m.isTop, CASE @Mode WHEN 10 THEN m.subject WHEN 12 THEN p.userID ENDFROM Messages mINNER JOIN People p ON m.userID=p.userIDWHERE [blah][blah][blah].
What I need to do is return something that is not the userID, but still have that name. This did not work:
SELECT m.messageID, m.isTop, CASE @Mode WHEN 10 THEN m.subject WHEN 12 THEN pr.userName As userID ENDFROM Messages mINNER JOIN People p ON m.userID=p.userIDINNER JOIN Personalize pr ON m.userID=pr1.userIDWHERE [blah][blah][blah].
SQL dies on the "As"... (or, if I leave out the "As", it dies on "userID")
Any idea how I can do this?
Thanks,
View 3 Replies
View Related
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
Nov 30, 2004
I dont know the what is the exact replication case i am talking about but here is the scenerio. "I have a SQL Server database on my local machine. I want to update same database to a remote database (hosted SQL SERVER database on web) which i can access from anywhere from my laptop then I want to make these changes update back itself on my local machine connected with internet) I want both updation plan run at the end of day or when ever I want.
I thought first that it is merge replication. but it is that much complicated. it is quite simple as compared to merge replication ..... where we have Publisher ,subscriber & distributor etc
can any one tell me the solution for it
View 2 Replies
View Related
Aug 7, 2001
Ok, this is in reference to the previous post about replicated server with difference.
I have a Case statement that checks for NULL values and works on one server and not the other. For example:
Select Case LineItems.Item When 'BILL' then Activities.InvoiceState When Null Then Activities.InvoiceState Else States.State End As Sate.
The second server is not recognizing the NULL in this statement. Any ideas??
Thanks alot for any help.
View 1 Replies
View Related
Oct 13, 2006
in sql server 2000 or 2003 how can i tell if a database is case sensitive or not??
View 2 Replies
View Related
Aug 31, 2006
hi friends, is it possible to make sqlserver case sensitive?
i mean is there any options to set while installation?
thank you very much
View 3 Replies
View Related
Aug 25, 2005
Environment1)We are using VBA,MS Access 2000/2003 and SQL Server 2000 in ourproject.2)We need to use Rational clear case as the SCM tool.Our WorkOur work primarily falls in developing SQL Server stored procedures,functions and few VisualBasicForAccess(VBA) code.Technical Help sought1) Does SQL Server 2000 provide any inbuilt capabilities of SoftwareConfiguration Management(SCM)?Not that I am aware of other thanextending it with other DM management tools like BMC,A&G,VERITAS etc.2)If not, Is there any plug-in available in SQL Server environment thatcan be installed on SQL Server so that any code developed [Storedprocedures/functions etc] can be managed in Clearcase.[I am looking forEclipse plugin's kind of plugins which would enable you to check in andcheck out from Eclipse after the initial configuration is done onEclipse]?3)If plugins are not available then the option we have is, manual andexternal check in and check out the code bits in to Clearcase.Can someone point me to concise and relevant user manual on this.
View 2 Replies
View Related
Jul 20, 2005
I am trying to convert this query to slq server 2000 and I cant figureout how to get rid of the IIF statements and make them case statements.If anyone could help I would greatly appreciate it!Thanks!spafaSELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2,Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL, Jeopardy.COMP_DNTL_DISC,Jeopardy.OUT_TO_BID, IIf([COMP_HLTH]="Mass BlueCross","YES",IIf([COMP_HLTH]="Out of State BlueCross","YES",IIf([COMP_HLTH]="CT BlueCross","YES",IIf([COMP_HLTH]="Empire Blue Cross","YES","NO"))))AS OTHER_BC_PLAN, IIf([other_bc_plan]="yes",[COMP_HLTH],"") ASBC_PLAN, Jeopardy.LG_RANKING, Jeopardy.LG_SCORE, Jeopardy.DATE_NOTIFIED,Jeopardy.DATE_UPDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,IIf([DateDiff]<8,"*",Null) AS CHANGE, IIf([DateDiff2]<8,"+",Null) AS[ADD], Jeopardy.Rep_Id, Jeopardy.Rep_Name, tblIRIP_QA_NAMES.ADMIN_NAMEAS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,AccountOwnership.SIZE, AccountOwnership.SIZE2, Jeopardy.CYCLE,Jeopardy.DENTAL_CYCLE, IIf([Jeopardy]![cycle] Is Null,[Jeopardy]![DENTA-L_CYCLE],IIf([Jeopardy]![cycle]="N/A",[Jeopardy]![DENTAL_CYCLE],[Jeop-ardy]![cycle])) AS CYCLE2, AccountOwnership.Canc_Date,AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,AccountOwnership.CLASSIC, AccountOwnership.BROKER,AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is NotNull,"YES","NO") AS OC, IIf([DISPOSITION_month] Is NotNull,[DISPOSITION_month],Null) AS OC_MONTHFROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ONAccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN JeopardyON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES ONAccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BYJeopardy.DATE_UPDATED DESC; " );--Posted via http://dbforums.com
View 2 Replies
View Related
Feb 14, 2008
Dear SQL Experts,
Can I change an existing 2000 sql server to case-sensative?
Books on-line tell you how to create one during setup, but requirements around here dictate that I chage my existing setup.
Any help would be greatly appreciated.
Thanks
View 6 Replies
View Related
Aug 25, 2006
I have to use the Case expression in my query, so I search arround the web and got the following: SELECT title, price, Budget = CASE price WHEN price > 20.00 THEN 'Expensive' WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate' WHEN price < 10.00 THEN 'Inexpensive' ELSE 'Unknown' END,FROM titles It should run OK base on my research in the internet. But my SQL Server gave me error: syntax error arround '>'.I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.Thanks!
View 5 Replies
View Related
Mar 5, 2007
I have a Users table with UserName and Password columns, in SQL Server 2005.
Currently the SQL Server is not set up to use case-sensitive criteria in the sprocs. How can I write my sproc to be case sensitive when searching on username and password?
View 1 Replies
View Related
Mar 4, 2002
I require to change the case sensitivity of a server. If I run rebuildm (changing the sensitivity to the desired value) will that not mean that I won't be able to reattach the databases?
Can anyone advise please.
thanks
Derek
View 1 Replies
View Related
May 8, 2015
USE ODS_ST
/*
I am having trouble with the CASE statement when ">50". It comes out as zero (0) when according to the data in the example should be ".9".
*/
CREATE TABLE #CLASS_TIMES
(
START_TIME DATETIME,
END_TIME DATETIME
)
INSERT INTO #CLASS_TIMES (START_TIME, END_TIME)
SELECT '2015-05-08 10:00:00.000','2015-05-08 10:45:00.000' UNION ALL --The record in question
[Code] ...
View 0 Replies
View Related
Jul 20, 2005
I have already install my Microsoft SQL server 2000 as not casesensitive, how to I change it to be case sensitive?
View 2 Replies
View Related
Nov 3, 2004
Hi,
Can we set the Uper/Lower case in server wide in SQL server 2000 as like case sensitive.
Thanks,
Ravi
View 6 Replies
View Related
Aug 5, 2014
My table structure like below.
id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes
Want to calculate yes count on any day for same id
View 5 Replies
View Related
Sep 21, 2015
I'm trying to get a result set without the NULLs. Here is the code I'm using. I'd like the results to look like:
17285204 90471 090471
17285204 90715 090715
17285204 99396 099396
17285204 99420 099420
17285204 90471 090471
17285204 NULL G0444
create table #Test
(
AppNum varchar(10),
CPT varchar(10),
src char(1)
)
insert into #Test(AppNum, CPT, src) values('17285204','090471','b')
[Code] ...
View 5 Replies
View Related
Feb 25, 2008
Hii
I transferred data from Oracle to sql server 2005. Now what i want is to make data in the tables case-sensitive .(it has to be data inside the tables only and not table and column names).
what i tried is :
alter database test collate Latin1_General_CS_AS
But to my horror it made the tables name case-sensitive .
Plz help me out asap.
Thanx in advance
Supriya
View 18 Replies
View Related
Sep 12, 2006
Is Windows Login Name Case Sensitive in SQL Server 2000?
View 1 Replies
View Related
Jun 24, 2004
I have just been given a new SQL Server 2000 box to look after in production. I just tried to run a standard t-sql script I use for setting up backup jobs and so on. However, it failed with a long list of errors - quite a surprise at first since I have run the same script on many other servers wihtout a hitch. On close examination, the problem appears to be that the new serer is setup with a server default collation... Latin1_General_BIN (I think a binary based collation makes this a case sensitive server).
This is quite an urgenet one since I have to get this wrapped up today. I don't think I can change the server's default collation without a lot of red-tape. Is there a quick way to run my scripts in a 'case insenstive' context within Query Analyzer? If so, how?
Thanks in advance,
Clive
View 4 Replies
View Related
Dec 20, 2013
I have a set of data where a column contains titles which have been formatted as follows:
"FirstWordSecondWordThirdWord...." etc.
That is, all the words have been concatenated but can be visually separated by their capital first letters.
For reporting purposes, I need to break this column into the separate words so that it looks like:
"First Word Second Word Third Word...." etc.
how this can be achieved?
View 9 Replies
View Related