Please review this short SQL and tell me how I screwed it up. 1

[Tomadams]

Please take a look at this and tell me why this line
year(a.Date_Return) = b.Rpt_Year
gets this error:
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near '.'.


select
a.Model_No,
year(a.Date_Return) as Rpt_Year,
a.Defect_Code,
count(*) as DefectCount,
max(b.TotalDefectCountForModel)
from
Returns_Analyzed a
inner join
(
select
Model_No,
year(Date_Return) as Rpt_Year,
count(*) as TotalDefectCountForModel
from
Returns_Analyzed
where
Defect_Code is not null and
Model_No is not null and
(isnull(Date_Return,'18990101') >= '1/1/2006' and isnull(Date_Return,'18990101') <= '1/31/2008')
group by
Model_No,
year(Date_Return)
) b
on
a.Model_No = b.Model_No,
year(a.Date_Return) = b.Rpt_Year
where
a.Defect_Code is not null and
a.Model_No is not null and
(isnull(Date_Return,'18990101') >= '1/1/2006' and isnull(Date_Return,'18990101') <= '1/31/2008')
group by
a.Model_No,
a.Defect_Code,
year(a.Date_Return)
order by
a.Model_No,
year(a.Date_Return)

 

[gmmastros]

When you have 2 join conditions, you do NOT use a comma. Instead, you use AND. Like this...

    on
        a.Model_No = b.Model_No  [green]-- No Comma[/green]
        [!]AND [/!]year(a.Date_Return) = b.Rpt_Year

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tomadams]

Simple things! thanks for taking the look George. I appreciate it very much

 

[SQLBill]

A way to remember that is that the ON replaces the old WHERE way of joining. WHERE uses AND and OR, so does ON.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875