Derived Query

I'm trying to build a "derived" query from 2 seperate table a Build count
table and a defect table
I've done this before but never with the calculated columns as in
Year(TestDate) as 'Yr'. It fails and returns
Server: Msg 207, Level 16, State 1, Line 17
Invalid column name 'YR'.
Any help ?
Select Year(TestDate) as 'Yr',
DateName(mm,TestDate)as 'Mnth',
Category, SubCategory,
Sum(NumFailures) as 'Fails'
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as 'Yr',
DateName(mm,TestDate)as 'Mnth',
Sum(DropCount) as 'Dropped'
From FTC..FTC_DropCount_Results
Where DropCount > 0
And TestDate >= '09-01-2005'
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON T1.YR = CT.YR
Group By '"Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
news:OgAwBv2HGHA.1728@.TK2MSFTNGP09.phx.gbl...
> I'm trying to build a "derived" query from 2 seperate table a Build count
> table and a defect table
> I've done this before but never with the calculated columns as in
> Year(TestDate) as 'Yr'. It fails and returns
> Server: Msg 207, Level 16, State 1, Line 17
> Invalid column name 'YR'.
> Any help ?
>
> Select Year(TestDate) as 'Yr',
> DateName(mm,TestDate)as 'Mnth',
> Category, SubCategory,
> Sum(NumFailures) as 'Fails'
> From ftc_testresults T1
> INNER JOIN
> (
> Select Year(TestDate) as 'Yr',
> DateName(mm,TestDate)as 'Mnth',
> Sum(DropCount) as 'Dropped'
> From FTC..FTC_DropCount_Results
> Where DropCount > 0
> And TestDate >= '09-01-2005'
> Group By Year(TestDate),
> DateName(mm,TestDate)
> ) CT
> ON T1.YR = CT.YR
>
A column alias cannot be used in the same query in which it is assigned. So
in the outer query T1.YR is not legal. You must repeat the source
expression Year(T1.TestDate). And get rid of the single quotes around
column aliases, and add table aliases to all your column references.
Something like this:
Select Year(T1.TestDate) as Yr,
DateName(mm,T1.TestDate)as Mnth,
T1.Category,
T1.SubCategory,
Sum(T1.NumFailures) as Fails
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as Yr,
DateName(mm,TestDate)as Mnth,
Sum(DropCount) as Dropped
From FTC..FTC_DropCount_Results
Where DropCount > 0
And TestDate >= '09-01-2005'
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON Year(T1.TestDate) = CT.YR
David|||Dave,
Thanks for the quick and accurate reply.....
Curious... what's the taboo about single quotes around column aliases?
Final working product....
Select Year(T1.TestDate) as Yr,
DateName(mm,T1.TestDate) as Mnth,
T1.Category, T1.SubCategory,
IsNull(CT.Dropped , 0) as Dropped,
Sum(T1.NumFailures) as Failures
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as Yr,
DateName(mm,TestDate)as Mnth,
Sum(DropCount) as Dropped
From FTC..FTC_DropCount_Results
Where DropCount > 0
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON Year(T1.TestDate) = CT.YR
AND DateName(mm,TestDate) = CT.Mnth
Where T1.TestDate > = '09-01-2005'
Group By T1.TestDate,T1.Category,
T1.SubCategory, ct.dropped;
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uCN8P72HGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
> news:OgAwBv2HGHA.1728@.TK2MSFTNGP09.phx.gbl...
> A column alias cannot be used in the same query in which it is assigned.
> So in the outer query T1.YR is not legal. You must repeat the source
> expression Year(T1.TestDate). And get rid of the single quotes around
> column aliases, and add table aliases to all your column references.
> Something like this:
>
> Select Year(T1.TestDate) as Yr,
> DateName(mm,T1.TestDate)as Mnth,
> T1.Category,
> T1.SubCategory,
> Sum(T1.NumFailures) as Fails
> From ftc_testresults T1
> INNER JOIN
> (
> Select Year(TestDate) as Yr,
> DateName(mm,TestDate)as Mnth,
> Sum(DropCount) as Dropped
> From FTC..FTC_DropCount_Results
> Where DropCount > 0
> And TestDate >= '09-01-2005'
> Group By Year(TestDate),
> DateName(mm,TestDate)
> ) CT
> ON Year(T1.TestDate) = CT.YR
> David
>|||"Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
news:u9eSrD3HGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Dave,
> Thanks for the quick and accurate reply.....
> Curious... what's the taboo about single quotes around column aliases?
>
Not a taboo, just a style preference. A column alias is an identifier, a
name, and it should follow the same convention as other identifiers (tables,
columns, table aliases).
David|||>> Curious... what's the taboo about single quotes around column aliases? <<
In Standard SQL, strings are enclosed in single quotes and user created
names are in double quotes. Violating standards is never a good idea;
makes code harder to pport, harder to read and occassionally can scew
up things.
You might also want to look up some old postings of mine as to how a
SELECT statement works.