Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Converting Access Queries with iff() and DLookup() to SQL Server Expand / Collapse
Author
Message
Posted Sunday, March 7, 2010 11:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 11:39 AM
Points: 0, Visits: 21
Comments posted to this topic are about the item Converting Access Queries with iff() and DLookup() to SQL Server
Post #878432
Posted Monday, March 8, 2010 4:14 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 1, 2010 5:11 AM
Points: 66, Visits: 37
Sorry to be the first pedant, but the syntax for the Immediate If function is IIf() NOT iff()
Post #878504
Posted Monday, March 8, 2010 4:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:12 AM
Points: 7,791, Visits: 9,545
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
Post #878516
Posted Monday, March 8, 2010 5:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:08 AM
Points: 197, Visits: 71
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;



Post #878525
Posted Monday, March 8, 2010 5:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 4:35 AM
Points: 31, Visits: 79
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



Post #878534
Posted Monday, March 8, 2010 6:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 11:59 AM
Points: 10, Visits: 72
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.
Post #878556
Posted Monday, March 8, 2010 6:41 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
-- 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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #878559
Posted Monday, March 8, 2010 6:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 575, Visits: 642
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!
Post #878578
Posted Monday, March 8, 2010 7:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 29, 2013 3:42 PM
Points: 2, 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.
Post #878597
Posted Monday, March 8, 2010 7:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110, 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.
Post #878605
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse