April 1, 2011 at 9:32 am
Hello comunity
I have the following script , but i don´t know how to join the 2 correlated query to show me the result on column:
column that i was pretend:
pais,nrcli,cliente cosec, nome,nif , SUM(x.VALORcomCOB) as 'Com Cobertura',SUM(y.VALORsemCOB) as 'Sem Cobertura'
i have always errors and i think is join problems.
select cl.area as 'Pais',cast(cc.[no] as char) as 'nrCli',
(select top 1 cyccli from u_histcyc inner join cl on u_histcyc.clstamp=(cl.clstamp collate SQL_Latin1_General_Cp1250_CI_AS) and cl.no=cc.no) as 'Cliente COSEC',
cc.nome,'NIF'=cl.ncont, SUM(x.VALORcomCOB) as 'Com Cobertura',SUM(y.VALORsemCOB) as 'Sem Cobertura'
FROM
(select sum(x.VALORcomCOB)
from
(SELECT cc.no ,SUM((edeb-edebf) -(ecred-ecredf)) as 'VALORcomCOB'
FROM cc WHERE (select top 1 evalor from u_histcyc (nolock) , cl (nolock)
where u_histcyc.clstamp=(cl.clstamp collate SQL_Latin1_General_Cp1250_CI_AS) and cl.no=cc.no and cc.datalc >= dataplafond)= 0 ) as x
inner join cc on x.no = cc.no ,
(select sum(y.valorsemcob)
from
(SELECT CC.NO, SUM((edeb-edebf) -(ecred-ecredf)) as 'VALORsemCOB'
FROM cc WHERE (select top 1 evalor from u_histcyc (nolock) , cl (nolock)
where u_histcyc.clstamp=(cl.clstamp collate SQL_Latin1_General_Cp1250_CI_AS) and cl.no=cc.no and cc.datalc >= dataplafond)> 0 GROUP BY CC.NO) as y
inner join cc on Y.no = cc.no
inner join cl (nolock) on cc.no=cl.no and cc.estab=cl.estab
where ((edebf<edeb or ecredf<ecred)
and (edebf<edeb or ecredf<ecred))
and cl.no <> 0
and (cc.cmdesc not like '%Créd%' and cc.cmdesc not like '%cred%' and cc.cmdesc not like '%N/N Lanç. Deb%' and cc.cmdesc
not like '%Pag. Excesso%' and cc.cmdesc not like '%Anulação de Letra%')
and cc.datalc between '20110101' and '20111231'
GROUP BY CL.AREA, CC.[NO],CC.NOME,CL.NCONT
Many thanks
Luis Santos
April 1, 2011 at 10:07 am
Well I'm certainly not surprised that you're always getting errors. It's a very confused mash up of sub queries within selects within subqueries and in several places is not close to having correct syntax.
Unfortunately, because it's in such a mess, I've no idea what you're trying to achieve with the query, so it's difficult to offer any help other than that it's probably best to go back to original requirements and re-write it from scratch, avoiding the mass of subqueries and probably re-designing the schema at the same time.
If this sounds like an impossible task, maybe it's time to look at getting some specialist help in the form of consultancy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply