First of all, I DON'T want to convert the following query into the SQL92
equivalent, I just want to learn how to do it.
Most of the left and right join examples I've found on the Net usually deal
with 2 or three tables at the maximum.  What happens if my query uses 7
tables and only two of those require a left join? The 1st Where clause
requires the left join; everything else stays the same. That's one of the
reasons I still haven't switched to SQL92. This is the query that I want to
convert:

select addr.city, addr.secondary_addr, count(*)
from
addr, acct_offer_prod, acct, cust, active_addr_range, vw_bdry_levels,
addr_range_grouping
where
addr.addr_id = acct_offer_prod.addr_id and
acct_offer_prod.acct_id = acct.acct_id and
acct.acct_id = cust.cust_id and
acct_offer_prod.addr_id = active_addr_range.addr_id and  
active_addr_range.range_id = addr_range_grouping.range_id and   
addr_range_grouping.bdry_id = vw_bdry_levels.bdry_id and   
vw_bdry_levels.metro_or_isla like 'HD ISLA' and  
vw_bdry_levels.region_short like 'REG-R' and  
vw_bdry_levels.district_short like '037' and  
acct_offer_prod.addr_id = addr.addr_id and 
active_addr_range.eff_dt <= getdate() and 
(active_addr_range.exp_dt >= getdate() or active_addr_range.exp_dt is null)
and 
addr_range_grouping.eff_dt <= getdate() and 
(addr_range_grouping.exp_dt >= getdate() or addr_range_grouping.exp_dt is
null) and  vw_bdry_levels.eff_dt <= getdate() and 
(vw_bdry_levels.exp_dt >= getdate() or vw_bdry_levels.exp_dt is null) and
acct_offer_prod.del_status_cd = 'NORMAL'
group by addr.city, addr.secondary_addr

First of all, let's write it so it's readable. Looking past the naming
convention and the nonsense of putting keywords in all caps, this should be
SQL92 equivalent of your original query (and will work in SQL Server).

Select A.City, A.Secondary_Addr, Count(*)
From Addr As A
    Join Acct_Offer_Prod As AOP
        On A.Addr_Id = AOP.Addr_Id
            And AOP.Del_Status_Cd = 'NORMAL'
    Join Cust As C
        On A.Acct_Id = C.Cust_Id
    Join Active_Addr_Range As AAR
        On AOP.Addr_Id = AAR.Addr_Id
            And AAR.Eff_Dt <= Current_Timestamp
            And (AAR.Exp_Dt >= Current_Timestamp
                Or AAR.Exp_Dt Is Null)
    Join Addr_Range_Grouping As ARG
        On ARG.Range_Id = AAR.Range_Id
            And ARG.Eff_Dt <= Current_Timestamp
            And (ARG.Exp_Dt >= Current_Timestamp
                Or ARG.Exp_Dt Is Null)
    Join vw_Bdry_Levels As VBL
        On ARG.Bdry_Id = BDry_Id
            And VBL.Metro_Or_Isla Like 'HD ISLA'
            And VBL.Region_Short Like 'REG-R'
            And VBL.District_Short Like '037'
            And VBL.Eff_Dt <= Current_Timestamp
            And (VBL.Exp_Dt >= Current_Timestamp
                Or VBL.Exp_Dt Is Null)
Group By A.City, A.Secondary_Addr


Are you asking, "What will happen *if* the first where clause needs to be
converted into a left join?" If that is the case there are a couple of
solutions, I'd be inclined to write like so (depending on the actual spec of
what is desired) using paren'ed joins (Not sure if this is officially ANSI
compliant)

Select A.City, A.Secondary_Addr, Count(*)
From Addr As A
    Join Cust As C
        On A.Acct_Id = C.Cust_Id
    Left Join (Acct_Offer_Prod As AOP
        Join Active_Addr_Range As AAR
            On AOP.Addr_Id = AAR.Addr_Id
                And AAR.Eff_Dt <= Current_Timestamp
                And (AAR.Exp_Dt >= Current_Timestamp
                    Or AAR.Exp_Dt Is Null)
        Join Addr_Range_Grouping As ARG
            On ARG.Range_Id = AAR.Range_Id
                And ARG.Eff_Dt <= Current_Timestamp
                And (ARG.Exp_Dt >= Current_Timestamp
                    Or ARG.Exp_Dt Is Null)
        Join vw_Bdry_Levels As VBL
            On ARG.Bdry_Id = BDry_Id
                And VBL.Metro_Or_Isla Like 'HD ISLA'
                And VBL.Region_Short Like 'REG-R'
                And VBL.District_Short Like '037'
                And VBL.Eff_Dt <= Current_Timestamp
                And (VBL.Exp_Dt >= Current_Timestamp
                    Or VBL.Exp_Dt Is Null))
        On A.Addr_Id = AOP.Addr_Id
            And AOP.Del_Status_Cd = 'NORMAL'
Group By A.City, A.Secondary_Addr


Another solution is to use a series of Left Joins

Select A.City, A.Secondary_Addr, Count(*)
From Addr As A
    Join Cust As C
        On A.Acct_Id = C.Cust_Id
    Left Join Acct_Offer_Prod As AOP
        On A.Addr_Id = AOP.Addr_Id
            And AOP.Del_Status_Cd = 'NORMAL'
    Left Join Active_Addr_Range As AAR
        On AOP.Addr_Id = AAR.Addr_Id
            And AAR.Eff_Dt <= Current_Timestamp
            And (AAR.Exp_Dt >= Current_Timestamp
                Or AAR.Exp_Dt Is Null)
    Left Join Addr_Range_Grouping As ARG
        On ARG.Range_Id = AAR.Range_Id
            And ARG.Eff_Dt <= Current_Timestamp
            And (ARG.Exp_Dt >= Current_Timestamp
                Or ARG.Exp_Dt Is Null)
    Left Join vw_Bdry_Levels As VBL
        On ARG.Bdry_Id = BDry_Id
            And VBL.Metro_Or_Isla Like 'HD ISLA'
            And VBL.Region_Short Like 'REG-R'
            And VBL.District_Short Like '037'
            And VBL.Eff_Dt <= Current_Timestamp
            And (VBL.Exp_Dt >= Current_Timestamp
                Or VBL.Exp_Dt Is Null)
Group By A.City, A.Secondary_Addr


The results of these two solutions can vary dramatically depending on what is
actually desired. Thus, I'd need more details about the actual results desired
before I could provide an exact solution.



Thomas

naming convention and the nonsense of putting keywords in all caps, ..
<<

Upon how amny years of  research do you base this statement, if any?

I just published a book (SQL PROGRAMMING STYLE) based on 20+ years of
"readability of code" research, some of which I myself did at AIRMICS.
Reserved words in uppercase were shown in *every* study to be easier to
read  than code in all-lowercase or all-uppercase.  I think in the late
1970's we had about 150+ of them -- it was a popular topic for grad
students (see Ben Schneiderman at U. of Maryland, et al).

Quick lecture.

1) The eye of a Latin alphabet reader is drawn to  an uppercase letter;
it is the start of a sentence which models a unit of thought.
Camelcase screws up eye movement. Lowercase does not attract it.

2) All uppercase words are seen as bouma.  Bet that you had no idea
what a "bouma" is because you never did research on code readability.
It is "word shape" -- google it.for details.  We do not read code the
way we read text.  Code is 3D and text is linear.

You are smarter than this!  Send me a "snail mail" and I will get you a
copy of SQL PROGRAMMING STYLE.

You're telling me ... I could have saved $23.95 by just asking you ?

;-)

-- Alex Papadimoulis

Joe,

Again, pretending to be an expert on something when you are are really just
a zealot... As usual, your "facts", (or your memory of them), is totally
distored towards  just one side of the issue.   There are many many studies
that indicate exactly the opposite, but to read your post, they don't exist. 
As a famous epistomologist once said,  When the Experts disagree, the layman
would be wise to gold no opinion..  On this topic, as on others, you are not
an expert, you just have an opinion. And there are many experts, (and
studies) that disagree completely. 

Examples.


http://www.freescale.com/files/microcontrollers/doc/app_note/AN2111.pdf
"Uppercase and Lowercase Characters
The consistent use of uppercase and lowercase characters in programs
improves readability and makes
them easier to understand."


http://www.pacific.edu/standards/guidelines-coding.asp
..." Uppercase Do not use all UPPERCASE. Readability studies show this
slows down readers' cognition, leads to errors, and decreases readability.
All uppercase characters create a subconscious discomfort and alienate
readers from your pages. It also may appear like you are shouting."


http://www.psych.utoronto.ca/~muter/pmuter1.htm
Case
Searching for words is faster with uppercase characters, but reading of
continuous text is slower (Vartabedian, 1971), perhaps because interline
masking is greater with uppercase (Nes, 1986). In addition, lowercase
enhances reading efficiency because word shape is helpful in word recognition
(Rudnicky & Kolers, 1984).
http://human-factors.arc.nasa.gov/ihi/papers/publications/lozito/OSUMontalvo.html
Uppercase vs. lowercase letters. The benefits of lowercase v. uppercase are
well known. The literature (Poulton, 1967) concludes that lowercase is easier
to read than uppercase in some instances. The reasoning behind this claim is
that the envelope surrounding the lowercase letters is more distinctive than
uppercase letters. However this advantage emerges only with printed
sentences. In recognizing isolated words, it has been determined that
uppercase words are more easily processed than lowercase (Vartabedian, 1972).
This finding would prescribe the use of uppercase when formatting heading or
display labeling. Presently, these principles cannot be implemented on the
ACARS system because the cockpit devices available for the PDC typically do
not have the capability to display lowercase letters.
http://scholar.lib.vt.edu/theses/available/etd-102999-110544/unrestricted/WeisenmillerDissertation.pdf
Configuration refers to the distinctive shape patterns that written words
create. Lowercase letters have a more distinctive shape than capital letters,
therefore they can be perceived more quickly than uppercase letters. Because
readers are frequently exposed to a word, they no longer have to "read" the
word, but instantly recognize the meaning by the familiar shape of the group
of
letters.

http://www.ryerson.ca/library/info/style/print.html
Typography
For ease of readability:

Avoid using all uppercase letters

http://www.grammatech.com/style_guide/sec_3a.html
Use lowercase for all reserved words (when used as reserved words).

Use mixed case for all other identifiers, a capital letter beginning every
word separated by underscores.

http://www.mail-archive.com/disc***@ppig.org/msg00598.html
The general finding IIRC is that ALL-UPPER-CASE has low legibility in terms
of
recognising individual words, almost certainly because the outline shapes of
the words
don't differ as much as lower-case. I don't recall what happened to with
Title case or
with camel case (where joinedUpWords are internally capitalised). They might
not have
invented camel case at that time.
http://ecology.lifescience.ntu.edu.tw/seminar/931/Suggestions%20for%20slides%20at%20scientific%20meetings.pdf
.... not all uppercase, because a mixture of the two is more readable.


Charly





Show quoteHide quote

Comments inline...

Show quoteHide quoteThis article, while being one of the few that seems to actually pertain to
coding standards versus continuous-text standards (magazines, books, web
pages), does not seem to contradict Joe's assertion (specifically that
uppercase for reserved words is the best approach). The sentence you quote,
when taken out of the larger context, can be interpreted in more than one
fashion. However, after reading the document a couple of times, it appears
that the section quoted simply describes when it is best to use uppercase
characters, and when it is best to use lower case characters. It does not
state that it is best to use a combination of upper-and-lower case
characters, and I do not see any mention of which casing style to used with
reserved words.

This item has nothing to do with standards for how code should be written
(the page name is definitely misleading here). It is the web site style
guidelines for this university. They are saying that someone who is creating
a web page for the university web site should not use all UPPERCASE.

This is another item that is expressly not about coding standards. The title
is "Interface Design and Optimization of Reading of Continuous Text". Of
course, I am assuming that there is a qualitative difference between reading
code and reading a book or magazine (or other continuous text document).

http://human-factors.arc.nasa.gov/ihi/papers/publications/lozito/OSUMontalvo.html
Show quoteHide quoteAnother item that is not coding related. This appears to be related to
displaying information to end-users in an aviation reporting system.

http://scholar.lib.vt.edu/theses/available/etd-102999-110544/unrestricted/WeisenmillerDissertation.pdf
This appears to be another continuous text document, although it is related
to reading text on a computer screen. This quote, in the "Summary,
Conclusions, and Recommendations" section, appears to support this
hypothesis:

"The purpose of this study was to determine empirically whether the text
displayed using the typefaces Georgia and Verdana [which, according to
Microsoft, 1998; Will-Harris, 1998; Monotype, 1997, optimize on-screen
readability] significantly improve reading performance as measured by
reading
rate and/or reading comprehension as compared to the typefaces Times and
Arial-both of which are digital fonts designed specifically for
text-on-paper
output."

This is another web site style guideline, and has nothing to do with coding
standards. The heading on the page is "Ryerson Library Web Page Style
Guide".

This appears to be the only article/document that actually refutes Joe's
assertion in any meaningful way. This is a coding guideline from the "Ada 95
Style Guide" for the Ada product from GrammaTech.

Show quoteHide quoteThis item definitely pertains to coding guidelines, but the quote appears to
be missing a crucial sentence (which appears as the sentence just before the
text that you quoted):
"The place to start is probably with studies of print readability in
general."

I am not sure that the general case of print readability is a strong
refutation of Joe's assertion that reserved words in code should be
uppercase.

http://ecology.lifescience.ntu.edu.tw/seminar/931/Suggestions%20for%20slides%20at%20scientific%20meetings.pdf
Here is another strange item to use to refute Joe's specific claim. The
title of this document is "Suggestions for Slides at Scientific Meetings".
It is difficult to understand how this pertains at all.

Show quoteHide quote

Thank you; I just went thru the list can came to the same conclusions.


refutation of Joe's assertion that reserved words in code should be
uppercase. <<

Yes and no.  Colors in text and color are not helpful beyond a
highlight -- "fruit salad" displays are a nightmare.  But text is read
from left to right smoothly and the typographer's rule about a column
being no more than 2.5 alphabets wide is good.  I set type for a living
in the 1970's, just when it was being computerized and wrote a few
articles in that trade press before I did IT magazine columns.

Code has a strong vertical flow and "chuck" components to it.  The
extreme example was a study we got at AIRMICS on assembly language
programmers; They read straight down the column. There is not much else
they can do, actually, but it was a totally different set of eye
movements from COBOL, FORTRAN, Jovial and Burroughs Algol (I was at
Georgia Tech in the old days).

product from GrammaTech. <<

That one was really funny to me.  I had to learn ADA while I was at
AIRMICS, but there were no compilers at the time.  There is an old
Dilbert cartoon about outsourcing to Elbonia.  The Elbonians have a
cardboard box with one guy inside and the caption "Okay, it's your turn
to be the computer!"

GammaTech lost and the ADA Standard was to uppercase user words and
lowercase reserved words. It was awful and made the code 12-15% harder
to maintain when we did programmer tests.  But the Army would not
change and there was a rumor that this was the result of a typo in a
report that nobody would reverse -- the right way, the wrong way and
the Army way!

opinion. <<

I posted this earlier, but it did not seem to make it. From 1982 to
1984, I was a Department fo the Army researcher at AIRMICS (Army
Institute for Research in Management Information & Computer Sciences)
at Georgia Tech.  We were the guys that ran ahead of the pack to
improve software productivity, in particular tools for easing
maintenance, rapid prototyping and applications of software metrics.
About six months of this two years was spent on gathering and funding
research on formatting code and other human factors for
maintainability.  Most of the research was at colleges and consisted of
formattng buggy programming different ways and clocking how long it
took to de-bug.  I got the job because the boss knew I had been a
typographer and had the typography research materials.  Later we got
enough money to fund eye movement studies with fancy equipment.  I
tracked the research for a year after we moved onto software metrics
and automatic specifications research.  I think that this makes me a
bit better than just a person with opinions.

<snip>... I think that this makes me a bit better than just a person with
opinions.</snip>

    Joe,  I'm sorry to say, although you seem to have missed it... but
that's exactly my point... that you *do* think you are better than the
average person with an opinion...  There's nothing quite so arrogant as
someone who "thinks"  (read "knows") they are a bit better than the average
person with an opinion...  

   You missed the point of my post as widely as Jeremy did (read his
response to mine)  It's not about the readability issue per se at all.  It's
about the certitude you present your opinion with...    You might very well
be right about this issue, it's entirely possible...  But even if you are,
it's not because of anything you did in 1984 or 1985, or even 2004.  It's not
because of how much you've written, or spoken, or lectured.  It won't be
because of who you are, or who you know, or what you wear... And it won't be
because the ideas you espouse are consistent with your opinion,  It will be
because the ideas you espose are consistent with the real world...

   And no matter what you "think", the jury is still out on that issue...


Show quoteHide quote

Newbie (to Triggers) Trigger Help....
SLOW UDF
Resequence column
Linked Server Yields inconsistent results
Newbie: Transfering data from Access, Binary data type