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
Posted Monday, March 8, 2010 7:52 AM

SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, September 25, 2015 6:33 AM
Points: 619, Visits: 710
In my case, DLookup was being used to find unknown values in a OTLT 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!
Post #878625
Posted Monday, March 8, 2010 9:23 AM


Group: General Forum Members
Last Login: Monday, September 14, 2015 10:41 AM
Points: 493, Visits: 436
I have to disagree with you Robert.
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 ( 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. 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.
[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 ' '
FROM Pets;
Post #878710
Posted Monday, March 8, 2010 1:45 PM



Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 19,279, Visits: 17,632
Paul White (3/8/2010)

Ok, so I was bored


Jason AKA CirqueDeSQLeil
I have given a name to my pain...


Posting Performance Based Questions - Gail Shaw
Post #878903
Posted Monday, March 8, 2010 4:45 PM


Group: General Forum Members
Last Login: Monday, January 19, 2015 12:13 AM
Points: 138, Visits: 377
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,

I deprecate your lack of distinction between "deprecate" and "depreciate"!
Post #879007
Posted Tuesday, March 9, 2010 2:19 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

I appreciate your deprecation of the the misappropriation of 'depreciation'!
Post #879193
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse