InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 14:52:44 GMT20http://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxezytime,I appreciate your deprecation of the the misappropriation of 'depreciation'!Tue, 09 Mar 2010 02:19:53 GMTPrometheus-867888http://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspx[quote][b]Robert Livermore (3/8/2010)[/b][hr]Becareful following the CASE statement syntax in the examples provided in the article. A string enclosed in quotation marks used as a column alias for an expression in a SELECT list is on the depreciation list for future versions of SQL Server:'string_alias' = expressionThe Microsoft depreciating list, [url=http://msdn.microsoft.com/en-us/library/ms143729.aspx]http://msdn.microsoft.com/en-us/library/ms143729.aspx[/url] [/quote]I deprecate your lack of distinction between "deprecate" and "depreciate"!Mon, 08 Mar 2010 16:45:49 GMTezytimehttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspx[quote][b]Paul White (3/8/2010)[/b][hr]Ok, so I was bored :laugh:[/quote]err...Understatement??:-PMon, 08 Mar 2010 13:45:30 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxI have to disagree with you Robert.:hehe:Sorry, I looked more closely at the posting and now see that Robert is referring specifically to the use of Single Quotes rather than the general syntax. He is correct. The use of Single Quotes is deprecated and I think it should be.;-)I did not find anything in the article you referenced (http://msdn.microsoft.com/en-us/library/ms143729.aspx) to indicate that column aliasing in this fashion is deprecated. I think what is deprecated is omitting the "AS" key word. The syntax &lt;AliasName = Expression&gt; and &lt;Expression AS Alias&gt; is explictly shown in the SQL 2008 syntax diagram for the Select clause.http://msdn.microsoft.com/en-us/library/ms176104.aspx. The use of square brackets or Double Quotes or Single Quotes to delimit the AliasName is required when the name does not follow TSQL naming rules, (e.g., [My Alias Name] or [Object_Id]). I personally prefer the &lt;Alias Name = Expression&gt; version and case statements are a good example of why. I think it simpler and more obvious to see something like this. I find that my application developers have a much easier time following the stored procedure and processing the proper columns this way.SELECT Name, [Sound] = CASE Barks WHEN 'True' THEN 'Ruff Ruff' ELSE CaseType WHEN 'Cat' THEN'Meow' WHEN 'Snake' THEN'Hiss' WHEN 'Pig' THEN'Oink' WHEN 'Monkey' THEN'Eek Eek' ELSE ' ' END END FROM Pets;Mon, 08 Mar 2010 09:23:14 GMTRay Herringhttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxIn my case, DLookup was being used to find unknown values in a OTLT :w00t: based on a primary key. For example: using a random address book number to find a customer's name, the sales agent, the co-sales agent, and the manager all from the same AddressBook table downloaded nightly from an ERP source. I have several Access queries that have as many as 15 DLookups from as many as 5 separate tables. I have never come across an example as simple as the one given in the article.Mon, 08 Mar 2010 07:52:01 GMTMeow Nowhttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxI suggest if you are suffering through a rewrite anyway that you move the data to tables where it belongs rather than hardcoding data into code. Join the existing table(s) to the metadata. Metadata in a table is reusable across multiple queries without the maintenance hassle of copy/paste. Data in a table is much easier to extend than finding all occurrences of a particular CASE. (suppose in this example you need to add the sound of a goat and there are a dozen CASE statements in as many SP using the animals table, and worse: ad-hoc command batches in application code)I don't know what principle of data stewardship this notion espouses; I expect many long-time DBA would agree that the extra 15 minutes work to properly store metadata in tables will save hours of hunt&fix in the future.Mon, 08 Mar 2010 07:36:21 GMTMike Doughertyhttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxNo need to create a form and button to call the CreateDatabase sub. Just run it from the immediate window in the VBA editor.Mon, 08 Mar 2010 07:25:15 GMTjwgworldhttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxMost of the Access queries that I have converted to TSQL use the DLookup function only when using multiple tables. It's quite a neat feature in Access and is commonly used to avoid having to write more complicated LEFT and RIGHT OUTER joins. I would suggest sticking to those LEFT and RIGHT OUTER joins if you ever think the code might need to be converted one day. It will save a ton of re-writing.Mon, 08 Mar 2010 06:58:05 GMTMeow Nowhttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspx[code="sql"]-- For the demonstrationUSE tempdb;GO-- Drop any objects left over from previous runsIF OBJECT_ID(N'dbo.PetColours', N'V') IS NOT NULL DROP VIEW dbo.PetColours;IF OBJECT_ID(N'dbo.GetPetColourString', N'IF') IS NOT NULL DROP FUNCTION dbo.GetPetColourString; IF OBJECT_ID(N'dbo.PetColourMap', N'U') IS NOT NULL DROP TABLE dbo.PetColourMap;IF OBJECT_ID(N'dbo.Pet', N'U') IS NOT NULL DROP TABLE dbo.Pet;IF OBJECT_ID(N'dbo.Colour', N'U') IS NOT NULL DROP TABLE dbo.Colour;IF OBJECT_ID(N'dbo.Animal', N'U') IS NOT NULL DROP TABLE dbo.Animal;IF OBJECT_ID(N'dbo.Sound', N'U') IS NOT NULL DROP TABLE dbo.Sound;GO-- Create tablesCREATE TABLE dbo.Sound ( sound_id INTEGER NOT NULL PRIMARY KEY, name NVARCHAR(50) NOT NULL, );CREATE TABLE dbo.Animal ( animal_id INTEGER NOT NULL PRIMARY KEY, name NVARCHAR(50) NOT NULL, leg_count SMALLINT NOT NULL, has_fur BIT NOT NULL, sound_id INTEGER NOT NULL REFERENCES dbo.Sound, );CREATE TABLE dbo.Colour ( colour_id INTEGER NOT NULL PRIMARY KEY, name NVARCHAR(50) NOT NULL, ) CREATE TABLE dbo.Pet ( pet_id INTEGER IDENTITY PRIMARY KEY, name NVARCHAR(50) NOT NULL, animal_id INTEGER NOT NULL REFERENCES dbo.Animal, ); CREATE TABLE dbo.PetColourMap ( map_id INTEGER IDENTITY PRIMARY KEY, pet_id INTEGER NOT NULL REFERENCES dbo.Pet, colour_id INTEGER NOT NULL REFERENCES dbo.Colour, )GO-- For foreign keysCREATE NONCLUSTERED INDEX [IX dbo.Animal sound_id] ON dbo.Animal (sound_id);CREATE NONCLUSTERED INDEX [IX dbo.Pet animal_id] ON dbo.Pet (animal_id);CREATE UNIQUE NONCLUSTERED INDEX [UQ dbo.PetColourMap pet_id, colour_id] ON dbo.PetColourMap (pet_id, colour_id);CREATE UNIQUE NONCLUSTERED INDEX [UQ dbo.PetColourMap colour_id, pet_id)] ON dbo.PetColourMap (colour_id, pet_id);GO-- Define soundsINSERT dbo.Sound (sound_id, name) VALUES (0, N'silent');INSERT dbo.Sound (sound_id, name) VALUES (1, N'ruff ruff');INSERT dbo.Sound (sound_id, name) VALUES (2, N'hiss');INSERT dbo.Sound (sound_id, name) VALUES (3, N'oink');INSERT dbo.Sound (sound_id, name) VALUES (4, N'meow');INSERT dbo.Sound (sound_id, name) VALUES (5, N'eek eek');-- Define coloursINSERT dbo.Colour (colour_id, name) VALUES (0, N'black');INSERT dbo.Colour (colour_id, name) VALUES (1, N'white');INSERT dbo.Colour (colour_id, name) VALUES (2, N'brown');INSERT dbo.Colour (colour_id, name) VALUES (3, N'green');INSERT dbo.Colour (colour_id, name) VALUES (4, N'pink');INSERT dbo.Colour (colour_id, name) VALUES (5, N'tabby');INSERT dbo.Colour (colour_id, name) VALUES (6, N'dark brown');INSERT dbo.Colour (colour_id, name) VALUES (7, N'lime green');-- Define animalsINSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)VALUES (1, N'dog', 4, 1, 1);INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)VALUES (2, N'snake', 0, 0, 2);INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)VALUES (3, N'pig', 4, 0, 3);INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)VALUES (4, N'cat', 4, 1, 4);INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)VALUES (5, N'monkey', 4, 1, 5);INSERT dbo.Animal (animal_id, name, leg_count, has_fur, sound_id)VALUES (6, N'iguana', 4, 0, 0);-- Create petsINSERT dbo.Pet (name, animal_id) VALUES (N'Spike', 1);INSERT dbo.Pet (name, animal_id) VALUES (N'Rex', 1);INSERT dbo.Pet (name, animal_id) VALUES (N'Slither', 2);INSERT dbo.Pet (name, animal_id) VALUES (N'Wilbur', 3);INSERT dbo.Pet (name, animal_id) VALUES (N'Fluffy', 4);INSERT dbo.Pet (name, animal_id) VALUES (N'Hunter', 4);INSERT dbo.Pet (name, animal_id) VALUES (N'Mr. Biggles', 5);INSERT dbo.Pet (name, animal_id) VALUES (N'Godzilla', 6);-- Add entries to the pet colour mapping tableINSERT dbo.PetColourMap (pet_id, colour_id) VALUES (1, 0);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (1, 2);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (2, 0);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (2, 1);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (3, 3);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (4, 4);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (5, 0);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (5, 1);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (6, 5);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (7, 6);INSERT dbo.PetColourMap (pet_id, colour_id) VALUES (8, 7);GO-- An in-line table-valued function to combine pet colours-- into a delimited string with guaranteed order of coloursCREATE FUNCTION dbo.GetPetColourString (@PetID INTEGER)RETURNS TABLEWITH SCHEMABINDINGAS RETURN SELECT pet_colours = STUFF( Colours.xml_string.value('./text()[1]', 'NVARCHAR(100)') , 1, 1, N'') FROM ( SELECT N'/' + C.name FROM dbo.PetColourMap CM JOIN dbo.Colour C ON C.colour_id = CM.colour_id WHERE CM.pet_id = @PetID ORDER BY C.colour_id ASC FOR XML PATH(''), TYPE ) Colours (xml_string);GOCREATE VIEW dbo.PetColoursASSELECT P.pet_id, P.name, PCS.pet_coloursFROM dbo.Pet PCROSSAPPLY dbo.GetPetColourString (P.pet_id) PCS;GO-- Pet names and sounds madeSELECT pet_name = P.name, sound_name = S.nameFROM dbo.Pet PJOIN dbo.Animal A ON A.animal_id = P.animal_idJOIN dbo.Sound S ON S.sound_id = A.sound_id;-- Pets and coloursSELECT PC.pet_id, pet_name = PC.name, PC.pet_coloursFROM dbo.PetColours PCWHERE pet_colours = N'black/white';GO-- Tidy upDROP VIEW dbo.PetColours;DROP FUNCTION dbo.GetPetColourString;DROP TABLE dbo.PetColourMap;DROP TABLE dbo.Pet;DROP TABLE dbo.Colour;DROP TABLE dbo.Animal;DROP TABLE dbo.Sound;-- End script[/code]Ok, so I was bored :laugh:Mon, 08 Mar 2010 06:41:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxShould be TOP 1 for T-SQL, instead of FIRST, I think. First is used in other SQL's, it think, like db2, at the end of the select clause. First does work in Access; I tried it.Mon, 08 Mar 2010 06:36:30 GMTdgreen-1126628http://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxAm I missing something here? I hadn't seen SELECT FIRST before so I tried it, but SQL 2008 won't recognise it. Can't find it in books online either.KevMon, 08 Mar 2010 05:40:40 GMTKevin O'Donovanhttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxBecareful following the CASE statement syntax in the examples provided in the article. A string enclosed in quotation marks used as a column alias for an expression in a SELECT list is on the depreciation list for future versions of SQL Server:'string_alias' = expressionThe Microsoft depreciating list, [url=http://msdn.microsoft.com/en-us/library/ms143729.aspx]http://msdn.microsoft.com/en-us/library/ms143729.aspx[/url] [b]Mitigation[/b]Rather use AS to alias the CASE expression.SELECT Name, CASE Barks WHEN 'True' THEN 'Ruff Ruff' ELSE CaseType WHEN 'Cat' THEN'Meow' WHEN 'Snake' THEN'Hiss' WHEN 'Pig' THEN'Oink' WHEN 'Monkey' THEN'Eek Eek' ELSE ' ' END END [b]AS Sound[/b]FROM Pets;Mon, 08 Mar 2010 05:12:29 GMTRobert Livermorehttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxI'll be the second pedant then.Maybe it's my browser or maybe it's the article, but things like [quote]Replace IIF with CASE, the becomes the WHEN clause, and the is the ELSE clause.[/quote] are missing enough words to not make any sense; and even if the missing words were supplied what is the purpose of the contrast between "becomes" and "is"?Mon, 08 Mar 2010 04:45:33 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxSorry to be the first pedant, but the syntax for the Immediate If function is IIf() NOT iff()Mon, 08 Mar 2010 04:14:02 GMTPrometheus-867888http://www.sqlservercentral.com/Forums/Topic878432-2637-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Access/69416/">Converting Access Queries with iff() and DLookup() to SQL Server</A>[/B]Sun, 07 Mar 2010 23:52:07 GMTfredsid