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
Kick6Tiger
Kick6Tiger
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 Visits: 766
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!
Ray Herring
Ray Herring
SSC Eights!
SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)

Group: General Forum Members
Points: 858 Visits: 571
I have to disagree with you Robert.
HeheSorry, 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;
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23337 Visits: 18271
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

ezytime
ezytime
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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, http://msdn.microsoft.com/en-us/library/ms143729.aspx

I deprecate your lack of distinction between "deprecate" and "depreciate"!
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
ezytime,

I appreciate your deprecation of the the misappropriation of 'depreciation'!
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