Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting Access Queries with iff() and DLookup() to SQL Server


Converting Access Queries with iff() and DLookup() to SQL Server

Author
Message
fredsid
fredsid
Forum Newbie
Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)

Group: General Forum Members
Points: 0 Visits: 21
Comments posted to this topic are about the item Converting Access Queries with iff() and DLookup() to SQL Server
Prometheus-867888
Prometheus-867888
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 37
Sorry to be the first pedant, but the syntax for the Immediate If function is IIf() NOT iff()
TomThomson
TomThomson
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11429 Visits: 12091
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

Robert Livermore
Robert Livermore
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 81
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;



Kevin O'Donovan
Kevin O'Donovan
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 84
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



dgreen-1126628
dgreen-1126628
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 82
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.
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11330 Visits: 11354

-- 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
SQLblog.com
@SQL_Kiwi
Kick6Tiger
Kick6Tiger
SSChasing Mays
SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)

Group: General Forum Members
Points: 645 Visits: 766
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!
jwgworld
jwgworld
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 25
No need to create a form and button to call the CreateDatabase sub. Just run it from the immediate window in the VBA editor.
Mike Dougherty
Mike Dougherty
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 952
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search