I'm creating a view in SQL and am looking for something equivalent to the IIF ststement in Access.
Basically I want to create 2 new columns, which are based on the result of 1 of the other columns.
So if in my columns I have :
ID - NAME - FLAG - TIMEFROM - TIMETO
11 - Fred - 0 - 09:00 - 10:30
12 - John - 1 - 11:30 - 15:30
etc, etc, etc
I want to add the first new column saying something along the lines of "If Flag = 0 then DateTo - DateFrom, otherwise Null"
and then add a second column saying "If Flag = 1 then DateTo - DateFrom, otherwise Null"
Therefore the above would show :
ID - NAME - FLAG - TIMEFROM - TIMETO - NEWCOL1 - NEWCOL2
11 - Fred - 0 - 10:00 - 10:30 - 0:30 - Null
12 - John - 1 - 11:30 - 15:30 - Null - 4:00
etc, etc, etc
This in turn would give me 2 new columns which would show value 1 and value 2 - 1 being where the flag is not checked, and 2 being where the flag is checked.
Using ms sql 2000I have 2 tables.I have a table which has information regarding a computer scan. Eachrecord in this table has a column called MAC which is the unique ID foreach Scan. The table in question holds the various scan results ofevery scan from different computers. I have an insert statement thatworks however I am having troulbe getting and update statement out ofit, not sure if I'm using the correct method to insert and thats why orif I'm just missing something. Anyway the scan results is stored as anXML document(@iTree) so I have a temp table that holds the releventinfo from that. Here is my Insert statement for the temporary table.INSERT INTO #tempSELECT * FROM openxml(@iTree,'ComputerScan/scans/scan/scanattributes/scanattribute', 1)WITH(ID nvarchar(50) './@ID',ParentID nvarchar(50) './@ParentID',Name nvarchar(50) './@Name',scanattribute nvarchar(50) '.')Now here is the insert statement for the table I am having troublewith.INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,ScanID, AttributeValue, DateCreated, LastModified)SELECT @MAC, #temp.ID, #temp.ParentID,tblScanAttribute.ScanAttributeID, tblScan.ScanID,#temp.scanattribute, DateCreated = getdate(),LastModified =getdate()FROM tblScan, tblScanAttribute JOIN #temp ONtblScanAttribute.Name =#temp.NameIf there is a way to do this without the temporary table that would begreat, but I haven't figured a way around it yet, if anyone has anyideas that would be great, thanks.
Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?
I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.
Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.
Server Configuration:
SQL Server 2008 R2 SP1, Enterprise Edition 3 x Quad-Core Xeon Processor Max Degree of Parallelism = 8 148 GB RAM
SQL Code:
Target Table: USE TPS; IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL DROP TABLE dbo.ParticipantResponse;
I have looked around and found the equivalent for IIF (Access) to be aSELECT CASE in SQL. I have tried this with no success. I am also lookingfor the equivalent of MAX and have had no luck. The portion of thestring I am trying to SQL'ize is:SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS ADCN FROM ADCNINNER JOIN Sheet ON ADCN.RecordID = Sheet.RecordID WHERE (Sheet.Drawing= '" & x & "') AND (Sheet.SheetNumber = 0);This portion is the most important:SELECT Max(IIf([ADCN.ADCN] Is Null,0,[ADCN.ADCN])) AS ADCN*** Sent via Developersdex http://www.developersdex.com ***
Recently, my company's deveolpment machine crashed. We had to format the harddrive which meant we lost some of our databases. We managed to restore the databases, but we do not know in what state, i.e. which stored procedures were written between the restore point and the point the machine crashed. Tow questions related to this: 1. I was wondering if there is any product that is similar to soursesafe but appied to databases, so that this problem will not happen again. Does Enterprise manager do something like this? 2. I was also wondering if there was anyway to undo an sql command. e.g. my colleague recently ran a command to update a database, but forgot to write the where clause!! That data was recovered aswell, but it would be nice to have some sort of undo button. Thanks in advance. Jagdip
Is there anyway I can return only the last row of a query, like TOP does for the top most items?I would like to return something like this:SELECT BOTTOM 1 Column_CFROM Table1WHERE Column_A = somethingAny help would be greatly appreciated. Thank you!
Hi everyoneI have a table 'users' with column 'residence' New York, NY Houston, TXSan Antonio, TX In MySQL I can select the city by running the following command SELECT SUBSTRING_INDEX(residence, ',' , 1) FROM users. How would I accomplish the same thing using ms sql? Thanks in advance
What is the equivalent to autonumber in SQL 7? I ported over some data into SQL but when I bring it up in Access 97 as linked tables it shows that what used to be autonumber in Access is listed only as number in SQL. So what is the equivalent to Autonumber in SQL?
In this sql syntax Quote: "SELECT table1.ID, table1.Catagory, table1.PostCount, table1.LastPostDate, table1.Description, table2.Username, table2.ID AS UserID FROM Catagories AS table1 INNER JOIN Users AS table2 ON table1.LastPostBy = table2.ID"
Is this the equivalent?
Quote: “SELECT table1.ID, table1.Catagory, table1.PostCount, table1.LastPostDate, table1.Description, table2Username, table2ID AS UserID FROM Categories AS table, Users AS table 2 WHERE table1.LastPostby = table2.ID”
I have 2 SQL statements in Visual Basic(with sybase as backend) 1) "set option DBA.MAX_STATEMENT_COUNT = 1069999900" 2) "set option DBA.MAX_CURSOR_COUNT = 1069999900"
And when I migrated the DB from Sybase to SQL server and try to run the vb code it is giving me error in that SQL statement as MS SQL server might not be recognising the above two statements. Is there an equivalent of this in SQL server. Thanks
Hi everyone, I am new to SQL, and would really appreciate help with this.
I have a database with the following fields: IDNumber: sequential running from 1 to approx 50000 SURNAME: Surname FNAME: Forename.
I want to return the last 100 IDNUmbers and return the surname and fname associated with the IDNumbers. When I try TOP it gives me IDNumbers 1 to 100, is there an equilvant for the bottom 100 numbers. Please help if you can. Thanks Scott
IS there a component/way of retrieving a list of SQL Servers or databases on your local network for SQL 2005/Express. The same way sqldmo worked for a .net project.
Hi.I'm a casual sql user. I have found a situation where I need to convert anoracle statement to tsql, one I can just fire off in any sql tool against anms sql server database.I studied the exists statement and I think I understand it somewhat, however Iwas not sure how to get it quite right. If you have an idea and a minute ortwo I'd appreciate any insight or tutorial.insert into authorization (program, optiontitle, usergroup, authorizationid)select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextvalfrom allgroups where exists (select * from authorizationwhere authorization.USERGROUP = allgroups.USERGROUP andauthorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE')I believe that because in my data, three values of usergroup from allgroupsreturn true from the exists, that this is supposed to insert three rows intoauthorization.But I can't figure out what to do about the authorization.nextval.. I triedvarious max(authorization)+1etc but nothing seemed to compile/workthanksJeff Kish
Hi everyone,Please excuse me if this has been asked before or sounds a bit dim.This is a question asked on another forum but the solutions beingoffered are focussing on programming rather than letting the DB serverdo the work, which I'm not sure is the most efficient solution.However, my confession is I dont use SQL server so can't help themdirectly with the syntax. Hopefully you can help me help them and learna little about SQL Server in the process.Trying to right pad a first name field so the padded string is a totalof 30 chars. It will be output concatenated with the last name field,and each field separated with a "|". So that when output it readssomething like:fname | mylastnameSyntax given was:select id,substring((last_name+','+rtrim(' '+isnull(level,'))+''+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as student_namefrom studentIssue: It appears this is padding correctly but the spaces are notrendering in the browser. (I have no way to check this as I don't usesqlserver. However, I can understand that multiple spaces are not goingto render in the client browser, if indeed the query is padding withspaces.Question: Instead of using space(), can replicate() be used and aunicode space representation rather than an actual space be used? Or,is there a better way that will ensurethe padding shows in browser?I guess a fixed width font would also need to be used otherwise the30-char blocks could wind up being different widths, which would defeatthe purpose.If there is something I've missed, or you have any suggestions, I'mkeen to learn.TYhanks in advance,Lossed
Is there an equivalent in mssql to SQL Anywhere's GET_IDENTITY whichreserves the next autoinc value for a table? Yes I know about@@Identity and SCOPE_IDENTITY. I need to get the next autoinc value_before_ I insert the record due to the way the existing applicationworks.I've seen DBCC CHECKIDENT mentioned but that seems kludgy to me.TIA,Jim
Is there any effcent way of copy bulk records from SQL2000 to SDF ? The approach I am using at moment is manually concatenate insert CommandText based on the value datatable from SQL 2000. However, it's not very effcient and I am getting timeout on a table that has around 30 fields and 3,600 records.
Can any one think a better way of doing bulk insert on SDF? Both schema on SQL 2000 and SDF are exactly the same.
I have a query that returns a set of rows - sorted by part#. On the report I can hide the duplicates (part#). How can I test the part# so that whenever a new part# starts I can reverse image the whole l line. I have not defined any groups. Is this a must?
I have a table with a count of 30K rows that describe values over a period of time. I would like to be able to replicate the PERCENTILE_CONT function that is found in Oracle with MS SQL Server 2005 but have not had much success.
Would anybody know of a way to use current MS SQL syntax to retrieve the value of the 5th,10th,15th,20th etc... percentile of my table values? Assume that all of the values are associated with the same key. Oracle gives you the Percentile_Cont function for inverse percentile calculation but what does SQL2005 give you?
In Crystal reports there was a property "New Page after". When this property is checked, a page break is inserted after the section is printed. Is there an equivalent in Reporting services? I am using a Table in the body of the report and i want to insert a page break once 20 rows have been displayed on the report. Please let me know how this could be achieved in reporting services.
I have several areas where it will either be yes / no or enabled / disabled in the site I am working on. I remember in ACCESS there was a yes no field. Is their an equivalent to this in SQL2005 and if so, what data type should I use. I know this is a simple stupid question, but I am new to SQL and .NET. Thanks in advance for your help!!!
VWD 2005 Express. Visual Basic. SQL Server 2005. I have a gridview tied to a sqldatasource. If there have been changes made to the records of the sqldatasource, does a sqldatasource.databind have the effect of requerying the database? Is this the proper method to use if I want to requery/update the gridview form?
Hi what is the MSSQL eqivalent for this MSAccess Query 1 UPDATE tbl1, tbl2 SET 2 tbl1.ADJUSTED = tbl2.Code, 3 tbl1.CODE = tbl2.Code, 4 tbl1.OTHER_CODE = tbl2.Other_Code, 5 tbl1.STATUS = 'Check', tbl1.ORGK = Null, 6 tbl1.SOURCE = 'Manual' 7 WHERE (((tbl1.STATUS)='Invalid') AND 8 ((tbl2.Override)=0) AND 9 ((tbl1.Path) Like tbl2.webadmin_path))
