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


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:23 PM
Points: 471, Visits: 512
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
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 451, Visits: 363
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 (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;
Post #878710
Posted Monday, March 8, 2010 1:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:55 PM
Points: 21,204, Visits: 14,889
Paul White (3/8/2010)


Ok, so I was bored


err...Understatement??




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #878903
Posted Monday, March 8, 2010 4:45 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 11:21 PM
Points: 138, Visits: 370
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"!
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
ezytime,

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

Add to briefcase ««12

Permissions Expand / Collapse