The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN ‘WECS 9500’ ELSE ‘WECS 9520’ END as wecs_system 1Best Answer 11 That format requires you to use either: CASE ebv.db_no WHEN 22978 THEN ‘WECS 9500’ WHEN 23218 … Read more
table1 (id, name) table2 (id, name) Query: SELECT name FROM table2 — that are not in table1 already 15 s 15 SELECT t1.name FROM table1 t1 LEFT JOIN table2 t2 ON t2.name = t1.name WHERE t2.name IS NULL Q: What is happening here? A: Conceptually, we select all rows from table1 and for each row … Read more
I have a table which I want to get the latest entry for each group. Here’s the table: DocumentStatusLogs Table |ID| DocumentID | Status | DateCreated | | 2| 1 | S1 | 7/29/2011 | | 3| 1 | S2 | 7/30/2011 | | 6| 1 | S1 | 8/02/2011 | | 1| 2 | … Read more
I want to have a unique constraint on a column which I am going to populate with GUIDs. However, my data contains null values for this columns. How do I create the constraint that allows multiple null values? Here’s an example scenario. Consider this schema: CREATE TABLE People ( Id INT CONSTRAINT PK_MyTable PRIMARY KEY … Read more
I have to update a field with a value which is returned by a join of 3 tables. Example: select im.itemid ,im.sku as iSku ,gm.SKU as GSKU ,mm.ManufacturerId as ManuId ,mm.ManufacturerName ,im.mf_item_number ,mm.ManufacturerID from item_master im, group_master gm, Manufacturer_Master mm where im.mf_item_number like ‘STA%’ and im.sku=gm.sku and gm.ManufacturerID = mm.ManufacturerID and gm.manufacturerID=34 I want to … Read more
The table name is Scores. Is it correct to do the following? IF EXISTS(SELECT * FROM dbo.Scores) DROP TABLE dbo.Scores 16 s 16 Is it correct to do the following? IF EXISTS(SELECT * FROM dbo.Scores) DROP TABLE dbo.Scores No. That will drop the table only if it contains any rows (and will raise an error … Read more
Which one: datetime datetime2 is the recommended way to store date and time in SQL Server 2008+? I’m aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use what, or maybe we should just use datetime2 only? 16 s 16 The … Read more
I have seen SQL that uses both != and <> for not equal. What is the preferred syntax and why? I like !=, because <> reminds me of Visual Basic. 14 s 14 Most databases support != (popular programming languages) and <> (ANSI). Databases that support both != and <>: MySQL 5.1: != and <> … Read more
I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table? (SQL answers preferable over clicking about in the GUI of the management studio.) 29 s 29 Not sure why … Read more
I’ll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2…} STL-7442-1 {3EC6…} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3…} 1 7 prefabulated amulite {1609B09…} 2 32 spurving bearing But occasionally there will be an order with two line items: … Read more