Dúvida no sql de um relatório do Núcleus

[NTI_Reitoria]

Estou alterando um relatório e o código funcina, porém ele n~~ao apresenta as informações ao lado umas das outras e sim coloca uma consulta de baixo da outra.

obs. o código:

SELECT (Select codnat from dnatureza where codcoligada=tmov.codcoligada and idnat=tmov.idnat) as CFOP,

(sum(c.quantidade*c.precounitario)/sum(tmov.valorbruto))*sum(tmov.valorliquido) as Valor_TOTAL_IPI,

sum(c.quantidade*c.precounitario) as Base_IPI, sum(a.valor) as IPI, '.0000' as BASE_ICMS, null as ICMS,

null as Valor_TOTAL_ICMS

FROM tmov (nolock) inner join titmmov c

on (tmov.codcoligada=c.codcoligada and tmov.idmov=c.idmov)

inner join (select distinct codcoligada,idmov,codtrb,valor,NSEQITMMOV

from ttrbmov (nolock) where codtrb = 'Ipi'

and idmov in (select idmov from tmov where status <> 'c')) a

on (c.codcoligada = a.codcoligada and c.idmov = a.idmov and a.NSEQITMMOV=c.NSEQITMMOV)

WHERE (tmov.codcoligada= '1') and

(tmov.status <> 'C') and

(tmov.dataemissao>= '2007-12-01') and

(tmov.dataemissao<= '2007-12-31') and

( (tmov.CODTMV like '2.2.__' or tmov.CODTMV ='2.6.01')) and

(tmov.idnat in (select idnat from dnatureza

where codcoligada= '1' and codnat like '_.___.___'))

GROUP BY (tmov.idnat), (tmov.codcoligada)

union all

SELECT max(dnatureza.codnat) as CFOP, null as Valor_TOTAL_IPI, null as Base_IPI, '.0000' as ipi, sum(tmov.valorbruto) as Base_ICMS ,

sum(b.valor) as ICMS , sum(tmov.valorliquido) as Valor_TOTAL_ICMS

FROM tmov (nolock) inner join dnatureza (nolock)

on (tmov.codcoligada=dnatureza.codcoligada and tmov.idnat=dnatureza.idnat)

inner join (select distinct codcoligada,idmov,codtrb,valor

from ttrbmov (nolock) where codtrb = 'ICMS'

and idmov in (select idmov from tmov where status <> 'c')) b

on (tmov.codcoligada = b.codcoligada and tmov.idmov = b.idmov)

WHERE (tmov.codcoligada= '1') and

(tmov.status <> 'C') and

(tmov.dataemissao>= '2007-12-01') and

(tmov.dataemissao<= '2007-12-31') and

( (tmov.CODTMV like '2.2.__' or tmov.CODTMV ='2.6.01')) and

(dnatureza.codnat like '_.___.___')

group BY (dnatureza.codnat)

observe agora a apresentação desse relatório:

5.101.001 97283.170000 82923.48000000 14359.6900 .0000 NULL NULL

5.102.001 15426.280981 14100.32000000 1376.2000 .0000 NULL NULL

6.101.001 84844.715155 70703.99200000 14140.8000 .0000 NULL NULL

6.101.002 50481.850000 42068.21000000 8413.6400 .0000 NULL NULL

6.102.001 73821.502357 64605.94550000 9215.7000 .0000 NULL NULL

5.949.001 925.000000 925.00000000 .0000 .0000 NULL NULL

6.102.003 490.000000 490.00000000 .0000 .0000 NULL NULL

5.403.001 5889.600000 5889.60000000 .0000 .0000 NULL NULL

5.108.001 48054.980000 43409.67410000 4645.3100 .0000 NULL NULL

6.922.001 81000.000000 73636.36200000 7363.6400 .0000 NULL NULL

5.101.001 NULL NULL .0000 82923.4800 14926.2300 97283.1700

5.102.001 NULL NULL .0000 14100.3200 2509.2600 15476.5200

5.108.001 NULL NULL .0000 43409.6700 7813.7400 48054.9800

5.403.001 NULL NULL .0000 5889.6000 .0000 5889.6000

5.949.001 NULL NULL .0000 925.0000 166.5000 925.0000

6.101.001 NULL NULL .0000 70704.0000 8051.0100 84844.8000

6.101.002 NULL NULL .0000 42068.2100 2944.7700 50481.8500

6.102.001 NULL NULL .0000 64605.9500 7752.7200 73821.6500

6.102.003 NULL NULL .0000 490.0000 34.3000 490.0000

6.922.001 NULL NULL .0000 73636.3600 .0000 81000.0000

eu quero que as informações que estã na parte de baixo (ICMS) fique na coluna que está porém nas linhas

do próprio cfop.

se vocês puderem me ajudar eu agradeço!

tatidia

[Emerson_Egs]

tenta utilizar ao inves de null sum(.0000)...

SELECT

(Select codnat from dnatureza where codcoligada=tmov.codcoligada and idnat=tmov.idnat) as CFOP,

(sum(c.quantidade*c.precounitario)/sum(tmov.valorbruto))*sum(tmov.valorliquido) as Valor_TOTAL_IPI,

sum(c.quantidade*c.precounitario) as Base_IPI, sum(a.valor) as IPI, sum(.0000) as BASE_ICMS, sum(.0000) as ICMS,

sum(.0000) as Valor_TOTAL_ICMS

FROM tmov (nolock) inner join titmmov c

on (tmov.codcoligada=c.codcoligada and tmov.idmov=c.idmov)

inner join (select distinct codcoligada,idmov,codtrb,valor,NSEQITMMOV

from ttrbmov (nolock) where codtrb = 'Ipi'

and idmov in (select idmov from tmov where status <> 'c')) a

on (c.codcoligada = a.codcoligada and c.idmov = a.idmov and a.NSEQITMMOV=c.NSEQITMMOV)

WHERE (tmov.codcoligada= '1') and

(tmov.status <> 'C') and

(tmov.dataemissao>= '2007-12-01') and

(tmov.dataemissao<= '2007-12-31') and

( (tmov.CODTMV like '2.2.__' or tmov.CODTMV ='2.6.01')) and

(tmov.idnat in (select idnat from dnatureza

where codcoligada= '1' and codnat like '_.___.___'))

GROUP BY (tmov.idnat), tmov.codcoligada

union all

SELECT max(dnatureza.codnat) as CFOP, sum(.0000) as Valor_TOTAL_IPI, null as Base_IPI, sum(.0000) as ipi, sum(tmov.valorbruto) as Base_ICMS ,

sum(b.valor) as ICMS , sum(tmov.valorliquido) as Valor_TOTAL_ICMS

FROM tmov (nolock) inner join dnatureza (nolock)

on (tmov.codcoligada=dnatureza.codcoligada and tmov.idnat=dnatureza.idnat)

inner join (select distinct codcoligada,idmov,codtrb,valor

from ttrbmov (nolock) where codtrb = 'ICMS'

and idmov in (select idmov from tmov where status <> 'c')) b

on (tmov.codcoligada = b.codcoligada and tmov.idmov = b.idmov)

WHERE (tmov.codcoligada= '1') and

(tmov.status <> 'C') and

(tmov.dataemissao>= '2007-12-01') and

(tmov.dataemissao<= '2007-12-31') and

( (tmov.CODTMV like '2.2.__' or tmov.CODTMV ='2.6.01')) and

(dnatureza.codnat like '_.___.___')

group BY (dnatureza.codnat), dnatureza.codcoligada

[Jair - Fórmula]

Vc chega la... basta querer