March 7, 2010 at 11:52 pm
Comments posted to this topic are about the item Converting Access Queries with iff() and DLookup() to SQL Server
March 8, 2010 at 4:14 am
Sorry to be the first pedant, but the syntax for the Immediate If function is IIf() NOT iff()
March 8, 2010 at 4:45 am
I'll be the second pedant then.
Maybe it's my browser or maybe it's the article, but things like
Replace IIF with CASE, the becomes the WHEN clause, and the is the ELSE clause.
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"?
Tom
March 8, 2010 at 5:12 am
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' = expression
The Microsoft depreciating list, http://msdn.microsoft.com/en-us/library/ms143729.aspx
Mitigation
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 AS Sound
FROM Pets;
March 8, 2010 at 5:40 am
Am 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.
Kev
March 8, 2010 at 6:36 am
Should 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.
March 8, 2010 at 6:41 am
-- For the demonstration
USE tempdb;
GO
-- Drop any objects left over from previous runs
IF 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 tables
CREATE 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 keys
CREATE 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 sounds
INSERT 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 colours
INSERT 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 animals
INSERT 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 pets
INSERT 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 table
INSERT 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 colours
CREATE FUNCTION dbo.GetPetColourString (@PetID INTEGER)
RETURNS TABLE
WITH SCHEMABINDING
AS 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);
GO
CREATE VIEW dbo.PetColours
AS
SELECT P.pet_id,
P.name,
PCS.pet_colours
FROM dbo.Pet P
CROSS
APPLY dbo.GetPetColourString (P.pet_id) PCS;
GO
-- Pet names and sounds made
SELECT pet_name = P.name,
sound_name = S.name
FROM dbo.Pet P
JOIN dbo.Animal A
ON A.animal_id = P.animal_id
JOIN dbo.Sound S
ON S.sound_id = A.sound_id;
-- Pets and colours
SELECT PC.pet_id,
pet_name = PC.name,
PC.pet_colours
FROM dbo.PetColours PC
WHERE pet_colours = N'black/white';
GO
-- Tidy up
DROP 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
Ok, so I was bored :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 8, 2010 at 6:58 am
Most 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.
Aigle de Guerre!
March 8, 2010 at 7:25 am
No need to create a form and button to call the CreateDatabase sub. Just run it from the immediate window in the VBA editor.
March 8, 2010 at 7:36 am
I 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.
March 8, 2010 at 7:52 am
In 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.
Aigle de Guerre!
March 8, 2010 at 9:23 am
I 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 <AliasName = Expression> and <Expression AS Alias> 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 <Alias Name = Expression> 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;
March 8, 2010 at 1:45 pm
Paul White (3/8/2010)
Ok, so I was bored :laugh:
err...Understatement??:-P
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 8, 2010 at 4:45 pm
Robert Livermore (3/8/2010)
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' = expression
The Microsoft depreciating list, http://msdn.microsoft.com/en-us/library/ms143729.aspx
I deprecate your lack of distinction between "deprecate" and "depreciate"!
March 9, 2010 at 2:19 am
ezytime,
I appreciate your deprecation of the the misappropriation of 'depreciation'!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply