Iif To Case For Sql Server 2000
Jul 20, 2005
I am trying to convert this query to slq server 2000 and I cant figure
out how to get rid of the IIF statements and make them case statements.
If anyone could help I would greatly appreciate it!
Thanks!
spafa
SELECT 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 Blue
Cross","YES",IIf([COMP_HLTH]="Out of State Blue
Cross","YES",IIf([COMP_HLTH]="CT Blue
Cross","YES",IIf([COMP_HLTH]="Empire Blue Cross","YES","NO"))))
AS OTHER_BC_PLAN, IIf([other_bc_plan]="yes",[COMP_HLTH],"") AS
BC_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_NAME
AS 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 Not
Null,"YES","NO") AS OC, IIf([DISPOSITION_month] Is Not
Null,[DISPOSITION_month],Null) AS OC_MONTH
FROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ON
AccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN Jeopardy
ON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES ON
AccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BY
Jeopardy.DATE_UPDATED DESC; " );
--
Posted via http://dbforums.com
View 2 Replies
ADVERTISEMENT
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
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
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
Sep 12, 2006
Is Windows Login Name Case Sensitive in SQL Server 2000?
View 1 Replies
View Related
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
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
Nov 22, 2006
set MS SQL 2000 case-insensitive
View 1 Replies
View Related
Jul 20, 2005
Hi,I'm trying to do calculations in a SQL statement, but depending on onevariable (a.type in example) I'll need to pull another variable fromseperate tables.Here is my code thus far:select a.DeptCode DeptCode,a.Type Type,(a.ExpenseUnit / (select volume from TargetData b where b.type =a.type)) ExpenseFromcalc1 aThe problem... a.Type can be FYTD, Budget, or Target... and dependingon which one it is, I need to make b either FYTDData, TargetData, orBudgetData. I'm thinking a case statement might do the trick, but Ican't find any syntax on how to use Case in an MS SQL statement. EvenIf statements will work (if that's possible), though case would beless messy.Any suggestions would be much appriciative. Thanks...Alex.
View 4 Replies
View Related
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
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
Feb 24, 2005
Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.
View 5 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
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
View Related
Feb 28, 2008
Hi All,
I would like to know, how the datetime will be stored in the sqlserver datetime column.
Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy.
while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in
yyyy/mm/dd format.
So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database?
Thanks in Advance.
Regards,
Dhanasekaran. G
View 2 Replies
View Related
Sep 14, 2004
I am currently running SQL Server 2000 Standard on my production system, and I am looking to upgrade the system to Windows 2000 Adv. Server. I would also like to upgrade SQL Server 2000 Standard to SQL Server 2000 Enterprise to utilize more than 2GB of memory. Can anyone tell me what is the best way to upgrade the system, and please provide some feedback on your experiences with the upgrade. Thanks in advance.
View 2 Replies
View Related
Jul 20, 2005
We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.
View 1 Replies
View Related
Jul 20, 2005
What is the limitation of memory that SQL Server 2000 Standard can usewhen running on a Windows 2000 Advanced Server platform?
View 1 Replies
View Related
Sep 15, 2006
Hi, I need to know if somebody knows like making the update of Standard SQL 2000 to Enterprise. Greetings.
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
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
Mar 15, 2006
Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
View 1 Replies
View Related
