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

  • Comments posted to this topic are about the item Converting Access Queries with iff() and DLookup() to SQL Server

  • Sorry to be the first pedant, but the syntax for the Immediate If function is IIf() NOT iff()

  • 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

  • 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;

  • 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

  • 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.

  • -- 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:

  • 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!

  • No need to create a form and button to call the CreateDatabase sub. Just run it from the immediate window in the VBA editor.

  • 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.

  • 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!

  • 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;

  • 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

  • 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"!

  • 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