July 22, 2010 at 11:53 am
I have the following query:
SELECT
fc.no, fc.nome,
'FANO1' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End),
'Dif' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End)-SUM(Case When year(datalc)=2009 Then fc.ecred Else 0 End)
FROM
fc(nolock) left join fo on fc.fostamp=fo.fostamp
WHERE
fc.fano1 <> 0
Group By
fc.no, fc.nome
Order By
fc.nome
This query returns an error that fano1 isn't found. It works flawlessly without the WHERE clause. But i need it to ensure that i don't get lines with suppliers that didn't sell...
Is there any other way i can rule out the rows where fano1 is equal to 0?
Other info:
fc.no => int
fc.nome => varchar
fc.datalc => datetime
fc.ecred => float
July 22, 2010 at 12:03 pm
Solved like this:
SELECT
fc.no, fc.nome,
'FANO1' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End),
'Dif' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End)-SUM(Case When year(datalc)=2009 Then fc.ecred Else 0 End)
FROM
fc(nolock) left join fo on fc.fostamp=fo.fostamp
Group By
fc.no, fc.nome
HAVING
SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End) <> 0
Order By
fc.nome
July 22, 2010 at 12:07 pm
try this. The problem comes from the fact that fan01 does not physically exist it is a named output of the query.
select t.* from
(
SELECT
fc.no, fc.nome,
'FANO1' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End),
'Dif' = SUM(Case When year(datalc)=2010 Then fc.ecred Else 0 End)-SUM(Case When year(datalc)=2009 Then fc.ecred Else 0 End)
FROM fc(nolock)
left join fo on fc.fostamp=fo.fostamp
) t
WHERE
t.fano1 <> 0
Group By
t.no, t.nome
Order By
t.nome
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply