April 13, 2010 at 2:52 pm
Hello comunity
i have this script :
select '2009' AS ANO, cl.no, sum(isnull(mybo.TOTBO,0)) as 'Encomendas', sum(isnull(myft.TOTFT,0)) as 'Total Pendente' ,
'Respons.Fin.Cliente' = sum(isnull(mybo.TOTBO,0))+sum(isnull(myft.TOTFT,0)),
sum(cl.eplafond)as 'Plafond Empresa',
'Plafond COSEC'=ISNULL((select top 1 evalor from u_histcyc (nolock) , cl (nolock) , cc (nolock)
where u_histcyc.clstamp=(cl.clstamp collate SQL_Latin1_General_Cp1250_CI_AS) and
cl.no=cc.no and cc.datalc >= dataplafond and cc.no = 25
and year(cc.datalc)=2009
order by dataplafond desc), 0),
avg(mycc.dias) as 'média dias pendentes',
avg(mypmr.dias) as 'P.M.R.',
avg(myatr.dias) as 'Atraso Recebimento'
from cl (nolock)
left join
(select bo.no, sum(bo.etotaldeb) as TOTBO from bo (nolock) where bo.fechada = 0 and bo.ndos = 2
and year(bo.dataobra)=2009 group by bo.no ) mybo
on mybo.no = cl.no
left join
(select cc.no,sum(cc.edeb - cc.ecred) as TOTFT from cc ( nolock)
where (cc.edebf< cc.edeb or cc.ecredf < cc.ecred)
and year(cc.datalc)=2009
group by cc.no) myft
on myft.no=cl.no
left join
(select cc.no,avg(datediff(day,cc.dataven,getdate())) as dias
from cc ( nolock)
where (cc.edebf< cc.edeb or cc.ecredf < cc.ecred)
and year(cc.datalc)=2009
group by cc.no) mycc
on mycc.no=cl.no
--Atraso recebimento
left join
(Select cc.no,dias=avg(datediff(dd,cc.dataven,re.procdata)), documentos=count(*) from rl (nolock)
inner join re (nolock) on re.restamp=rl.restamp inner join cc (nolock) on cc.ccstamp=rl.ccstamp
where cc.no=25 And cc.estab=0 And re.process=1
and year(procdata)=2009
group by cc.no) myatr
on myatr.no = cl.no
--PMR
left join
(select cc.no,dias = sum(datediff(dd,cc.datalc,re.procdata))/count(*)
from rl (nolock) inner join re (nolock) on re.restamp = rl.restamp
inner join cc (nolock) on cc.ccstamp = rl.ccstamp
where re.process = 1 and re.no = 25 and re.estab = 0
and year(cc.datalc)=2009
group by cc.no) mypmr
on mypmr.no = cl.no
where cl.no = 25
group by cl.no
UNION ALL
select '2010' AS ANO, cl.no, sum(isnull(mybo.TOTBO,0)) as 'Encomendas', sum(isnull(myft.TOTFT,0)) as 'Total Pendente' ,
'Respons.Fin.Cliente' = sum(isnull(mybo.TOTBO,0))+sum(isnull(myft.TOTFT,0)),
sum(cl.eplafond)as 'Plafond Empresa',
'Plafond COSEC'=ISNULL((select top 1 evalor from u_histcyc (nolock) , cl (nolock) , cc (nolock)
where u_histcyc.clstamp=(cl.clstamp collate SQL_Latin1_General_Cp1250_CI_AS) and
cl.no=cc.no and cc.datalc >= dataplafond and cc.no = 25
and year(cc.datalc)=2010
order by dataplafond desc), 0),
avg(mycc.dias) as 'média dias pendentes',
avg(mypmr.dias) as 'P.M.R.',
avg(myatr.dias) as 'Atraso Recebimento'
from cl (nolock)
left join
(select bo.no, sum(bo.etotaldeb) as TOTBO from bo (nolock) where bo.fechada = 0 and bo.ndos = 2
and year(bo.dataobra)=2010 group by bo.no ) mybo
on mybo.no = cl.no
left join
(select cc.no,sum(cc.edeb - cc.ecred) as TOTFT from cc ( nolock)
where (cc.edebf< cc.edeb or cc.ecredf < cc.ecred)
and year(cc.datalc)=2010
group by cc.no) myft
on myft.no=cl.no
left join
(select cc.no,avg(datediff(day,cc.dataven,getdate())) as dias
from cc ( nolock)
where (cc.edebf< cc.edeb or cc.ecredf < cc.ecred)
and year(cc.datalc)=2010
group by cc.no) mycc
on mycc.no=cl.no
--Atraso recebimento
left join
(Select cc.no,dias=avg(datediff(dd,cc.dataven,re.procdata)), documentos=count(*) from rl (nolock)
inner join re (nolock) on re.restamp=rl.restamp inner join cc (nolock) on cc.ccstamp=rl.ccstamp
where cc.no=25 And cc.estab=0 And re.process=1
and year(procdata)=2010
group by cc.no) myatr
on myatr.no = cl.no
--PMR
left join
(select cc.no,dias = sum(datediff(dd,cc.datalc,re.procdata))/count(*)
from rl (nolock) inner join re (nolock) on re.restamp = rl.restamp
inner join cc (nolock) on cc.ccstamp = rl.ccstamp
where re.process = 1 and re.no = 25 and re.estab = 0
and year(cc.datalc)=2010
group by cc.no) mypmr
on mypmr.no = cl.no
where cl.no = 25
group by cl.no
ORDER BY 1,2
but they return always the same error:
Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.
Could someone give me help
Many thanks
Luis Santos
April 13, 2010 at 3:13 pm
Hello again
I solve the problem, because on a this Select :
'Plafond COSEC'=ISNULL((select top 1 evalor from u_histcyc (nolock) , cl (nolock) , cc (nolock)
where u_histcyc.clstamp=(cl.clstamp collate SQL_Latin1_General_Cp1250_CI_AS) and
cl.no=cc.no and cc.datalc >= dataplafond and cc.no = 25
and year(cc.datalc)=2010
order by dataplafond desc), 0),
i can´t have the ORDER BY clause.
Even so I thank you for possibles replies.
Luis Santos
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply