A service from Red Gate

in
Home SQL .NET Exchange Opinion Blogs Forums About
Sign in | Join | Help
Home Blogs Forums

using EXEC sys_CrossTab

Last post 06-04-2008, 5:57 PM by Luis Santos. 0 replies.
Sort Posts: Previous Next

  •  06-04-2008, 5:57 PM Post number 58727

    using EXEC sys_CrossTab

    Reply Quote
    Hello comunity

    I have use the SP sys_Crosstab on my database, to obtain value of each customer on all products, with the following code:

    select cast(nome as char)
    , sum([0.121.01.0]) [0.121.01.0]
    , sum([0.129.04.0]) [0.129.04.0]
    , sum([0.129.05.0]) [0.129.05.0]
    , sum([0.781.04.01]) [0.781.04.01]
    , sum([101.01.04]) [101.01.04]
    , sum([101.05.03]) [101.05.03]
    , sum([101.05.04]) [101.05.04]
    , sum([101.05.05]) [101.05.05]
    , sum([101.05.10]) [101.05.10]
    , sum([101.90.01]) [101.90.01]
    , sum([102.00.06]) [102.00.06]
    , sum([102.00.08]) [102.00.08]
    , sum([102.03.05]) [102.03.05]
    , sum([102.03.09]) [102.03.09]
    , sum([102.03.11]) [102.03.11]
    , sum([102.12.07]) [102.12.07]
    , sum([102.12.10]) [102.12.10]
    , sum([102.13.11]) [102.13.11]
    , sum([109.01.01]) [109.01.01]
    , sum([110.00.14]) [110.00.14]
    , sum([110.00.15]) [110.00.15]
    , sum([110.01]) [110.01]
    , sum([110.02]) [110.02]
    , sum([110.10.02]) [110.10.02]
    , sum([110.10.03]) [110.10.03]
    , sum([110.10.04]) [110.10.04]
    , sum([110.10.05]) [110.10.05]
    , sum([110.10.06]) [110.10.06]
    , sum([114.23.00]) [114.23.00]
    , sum([114.40.00]) [114.40.00]
    , sum([114.40.01]) [114.40.01]
    , sum([114.40.02]) [114.40.02]
    , sum([115.00.01]) [115.00.01]
    , sum([115.00.02]) [115.00.02]
    , sum([115.00.03]) [115.00.03]
    , sum([115.00.04]) [115.00.04]
    , sum([115.01.01]) [115.01.01]
    , sum([115.01.02]) [115.01.02]
    , sum([115.01.03]) [115.01.03]
    , sum([115.01.04]) [115.01.04]
    , sum([115.05.01]) [115.05.01]
    , sum([115.05.02]) [115.05.02]
    , sum([115.05.03]) [115.05.03]
    , sum([115.10.01]) [115.10.01]
    , sum([115.10.02]) [115.10.02]
    , sum([115.10.03]) [115.10.03]
    , sum([115.10.04]) [115.10.04]
    , sum([115.10.05]) [115.10.05]
    , sum([115.11.01]) [115.11.01]
    , sum([115.11.02]) [115.11.02]
    , sum([115.11.03]) [115.11.03]
    , sum([115.11.04]) [115.11.04]
    , sum([115.12.05]) [115.12.05]
    , sum([115.14.14]) [115.14.14]
    , sum([115.16.01]) [115.16.01]
    , sum([115.16.02]) [115.16.02]
    , sum([115.16.03]) [115.16.03]
    , sum([115.16.04]) [115.16.04]
    , sum([115.21.01]) [115.21.01]
    , sum([115.21.04]) [115.21.04]
    , sum([115.30.01]) [115.30.01]
    , sum([115.30.02]) [115.30.02]
    , sum([115.30.03]) [115.30.03]
    , sum([115.30.04]) [115.30.04]
    , sum([115.30.05]) [115.30.05]
    , sum([115.30.07]) [115.30.07]
    , sum([115.30.15]) [115.30.15]
    , sum([120.02]) [120.02]
    , sum([120.03]) [120.03]
    , sum([120.05.09]) [120.05.09]
    , sum([121.00.03]) [121.00.03]
    , sum([121.00.04]) [121.00.04]
    , sum([121.00.05]) [121.00.05]
    , sum([121.00.06]) [121.00.06]
    , sum([121.00.08]) [121.00.08]
    , sum([191.01]) [191.01]
    , sum([192.01]) [192.01]
    , sum([192.02]) [192.02]
    , sum([193.01.01]) [193.01.01]
    , sum([193.01.06]) [193.01.06]
    , sum([193.01.07]) [193.01.07]
    , sum([193.01.08]) [193.01.08]
    , sum([193.01.09]) [193.01.09]
    , sum([193.01.10]) [193.01.10]
    , sum([193.02.99]) [193.02.99]
    , sum([201.13.06]) [201.13.06]
    , sum([202.14.07]) [202.14.07]
    , sum([681.08.3]) [681.08.3]
    , sum([700.02]) [700.02]
    , sum([700.10]) [700.10]
    , sum([700.12]) [700.12]
    , sum([700.14]) [700.14]
    , sum([700.16]) [700.16]
    , sum([700.18]) [700.18]
    , sum([701.01.01]) [701.01.01]
    , sum([701.01.02]) [701.01.02]
    , sum([701.01.04]) [701.01.04]
    , sum([701.05.01]) [701.05.01]
    , sum([701.05.02]) [701.05.02]
    , sum([701.05.04]) [701.05.04]
    , sum([701.05.05]) [701.05.05]
    , sum([701.05.06]) [701.05.06]
    , sum([701.05.10]) [701.05.10]
    , sum([701.09.00]) [701.09.00]
    , sum([701.09.02]) [701.09.02]
    , sum([701.09.03]) [701.09.03]
    , sum([701.09.04]) [701.09.04]
    , sum([701.09.05]) [701.09.05]
    , sum([701.09.09]) [701.09.09]
    , sum([701.10.03]) [701.10.03]
    , sum([701.10.04]) [701.10.04]
    , sum([701.10.05]) [701.10.05]
    , sum([701.10.06]) [701.10.06]
    , sum([701.10.09]) [701.10.09]
    , sum([701.11.00]) [701.11.00]
    , sum([701.11.01]) [701.11.01]
    , sum([701.11.02]) [701.11.02]
    , sum([701.11.03]) [701.11.03]
    , sum([701.11.04]) [701.11.04]
    , sum([701.11.09]) [701.11.09]
    , sum([701.12.00]) [701.12.00]
    , sum([701.12.03]) [701.12.03]
    , sum([701.12.04]) [701.12.04]
    , sum([701.12.06]) [701.12.06]
    , sum([701.12.09]) [701.12.09]
    , sum([701.12.11]) [701.12.11]
    , sum([701.13.03]) [701.13.03]
    , sum([701.13.04]) [701.13.04]
    , sum([701.13.05]) [701.13.05]
    , sum([701.13.06]) [701.13.06]
    , sum([701.13.09]) [701.13.09]
    , sum([701.14.00]) [701.14.00]
    , sum([701.14.03]) [701.14.03]
    , sum([701.14.04]) [701.14.04]
    , sum([701.15.03]) [701.15.03]
    , sum([701.16.04]) [701.16.04]
    , sum([701.16.09]) [701.16.09]
    , sum([702.00.04]) [702.00.04]
    , sum([702.00.06]) [702.00.06]
    , sum([702.00.08]) [702.00.08]
    , sum([702.00.10]) [702.00.10]
    , sum([702.08.00]) [702.08.00]
    , sum([702.08.03]) [702.08.03]
    , sum([702.08.06]) [702.08.06]
    , sum([702.08.07]) [702.08.07]
    , sum([702.08.10]) [702.08.10]
    , sum([702.11.12]) [702.11.12]
    , sum([702.12.07]) [702.12.07]
    , sum([702.13.07]) [702.13.07]
    , sum([702.13.11]) [702.13.11]
    , sum([702.14.07]) [702.14.07]
    , sum([702.20.01]) [702.20.01]
    , sum([710.00.00]) [710.00.00]
    , sum([710.00.14]) [710.00.14]
    , sum([710.06.00]) [710.06.00]
    , sum([710.07.00]) [710.07.00]
    , sum([710.08.00]) [710.08.00]
    , sum([710.09.00]) [710.09.00]
    , sum([710.10.05]) [710.10.05]
    , sum([710.10.08]) [710.10.08]
    , sum([710.10.10]) [710.10.10]
    , sum([710.11.00]) [710.11.00]
    , sum([710.11.08]) [710.11.08]
    , sum([710.12.00]) [710.12.00]
    , sum([710.12.03]) [710.12.03]
    , sum([710.12.04]) [710.12.04]
    , sum([710.12.05]) [710.12.05]
    , sum([710.12.08]) [710.12.08]
    , sum([710.13.00]) [710.13.00]
    , sum([710.14.00]) [710.14.00]
    , sum([710.18.00]) [710.18.00]
    , sum([710.19.00]) [710.19.00]
    , sum([710.20.00]) [710.20.00]
    , sum([710.20.02]) [710.20.02]
    , sum([710.20.03]) [710.20.03]
    , sum([720.00.00]) [720.00.00]
    , sum([720.00.05]) [720.00.05]
    , sum([720.00.06]) [720.00.06]
    , sum([731.01]) [731.01]
    , sum([732.01]) [732.01]
    , sum([733.01]) [733.01]
    , sum([788.01]) [788.01]
    , sum([788.02]) [788.02]
    , sum([788.04]) [788.04]
    , sum([788.90]) [788.90]
    , sum([788.99]) [788.99]
    , sum([791.01]) [791.01]
    , sum([791.02]) [791.02]
    , sum([800.01]) [800.01]
    , sum([800.03]) [800.03]
    , sum([800.04]) [800.04]
    , sum([801.01]) [801.01]
    , sum([801.02]) [801.02]
    , sum([801.03]) [801.03]
    , sum([801.04]) [801.04]
    , sum([801.05]) [801.05]
    , sum([801.06]) [801.06]
    , sum([810.01]) [810.01]
    , sum([810.02]) [810.02]
    , sum([820.01]) [820.01]
    , sum([820.02]) [820.02]
    , sum([820.03]) [820.03]
    , sum([820.04]) [820.04] ,
     sum([0.121.01.0])
     + sum([0.129.04.0])
     + sum([0.129.05.0])
     + sum([0.781.04.01])
     + sum([101.01.04])
     + sum([101.05.03])
     + sum([101.05.04])
     + sum([101.05.05])
     + sum([101.05.10])
     + sum([101.90.01])
     + sum([102.00.06])
     + sum([102.00.08])
     + sum([102.03.05])
     + sum([102.03.09])
     + sum([102.03.11])
     + sum([102.12.07])
     + sum([102.12.10])
     + sum([102.13.11])
     + sum([109.01.01])
     + sum([110.00.14])
     + sum([110.00.15])
     + sum([110.01])
     + sum([110.02])
     + sum([110.10.02])
     + sum([110.10.03])
     + sum([110.10.04])
     + sum([110.10.05])
     + sum([110.10.06])
     + sum([114.23.00])
     + sum([114.40.00])
     + sum([114.40.01])
     + sum([114.40.02])
     + sum([115.00.01])
     + sum([115.00.02])
     + sum([115.00.03])
     + sum([115.00.04])
     + sum([115.01.01])
     + sum([115.01.02])
     + sum([115.01.03])
     + sum([115.01.04])
     + sum([115.05.01])
     + sum([115.05.02])
     + sum([115.05.03])
     + sum([115.10.01])
     + sum([115.10.02])
     + sum([115.10.03])
     + sum([115.10.04])
     + sum([115.10.05])
     + sum([115.11.01])
     + sum([115.11.02])
     + sum([115.11.03])
     + sum([115.11.04])
     + sum([115.12.05])
     + sum([115.14.14])
     + sum([115.16.01])
     + sum([115.16.02])
     + sum([115.16.03])
     + sum([115.16.04])
     + sum([115.21.01])
     + sum([115.21.04])
     + sum([115.30.01])
     + sum([115.30.02])
     + sum([115.30.03])
     + sum([115.30.04])
     + sum([115.30.05])
     + sum([115.30.07])
     + sum([115.30.15])
     + sum([120.02])
     + sum([120.03])
     + sum([120.05.09])
     + sum([121.00.03])
     + sum([121.00.04])
     + sum([121.00.05])
     + sum([121.00.06])
     + sum([121.00.08])
     + sum([191.01])
     + sum([192.01])
     + sum([192.02])
     + sum([193.01.01])
     + sum([193.01.06])
     + sum([193.01.07])
     + sum([193.01.08])
     + sum([193.01.09])
     + sum([193.01.10])
     + sum([193.02.99])
     + sum([201.13.06])
     + sum([202.14.07])
     + sum([681.08.3])
     + sum([700.02])
     + sum([700.10])
     + sum([700.12])
     + sum([700.14])
     + sum([700.16])
     + sum([700.18])
     + sum([701.01.01])
     + sum([701.01.02])
     + sum([701.01.04])
     + sum([701.05.01])
     + sum([701.05.02])
     + sum([701.05.04])
     + sum([701.05.05])
     + sum([701.05.06])
     + sum([701.05.10])
     + sum([701.09.00])
     + sum([701.09.02])
     + sum([701.09.03])
     + sum([701.09.04])
     + sum([701.09.05])
     + sum([701.09.09])
     + sum([701.10.03])
     + sum([701.10.04])
     + sum([701.10.05])
     + sum([701.10.06])
     + sum([701.10.09])
     + sum([701.11.00])
     + sum([701.11.01])
     + sum([701.11.02])
     + sum([701.11.03])
     + sum([701.11.04])
     + sum([701.11.09])
     + sum([701.12.00])
     + sum([701.12.03])
     + sum([701.12.04])
     + sum([701.12.06])
     + sum([701.12.09])
     + sum([701.12.11])
     + sum([701.13.03])
     + sum([701.13.04])
     + sum([701.13.05])
     + sum([701.13.06])
     + sum([701.13.09])
     + sum([701.14.00])
     + sum([701.14.03])
     + sum([701.14.04])
     + sum([701.15.03])
     + sum([701.16.04])
     + sum([701.16.09])
     + sum([702.00.04])
     + sum([702.00.06])
     + sum([702.00.08])
     + sum([702.00.10])
     + sum([702.08.00])
     + sum([702.08.03])
     + sum([702.08.06])
     + sum([702.08.07])
     + sum([702.08.10])
     + sum([702.11.12])
     + sum([702.12.07])
     + sum([702.13.07])
     + sum([702.13.11])
     + sum([702.14.07])
     + sum([702.20.01])
     + sum([710.00.00])
     + sum([710.00.14])
     + sum([710.06.00])
     + sum([710.07.00])
     + sum([710.08.00])
     + sum([710.09.00])
     + sum([710.10.05])
     + sum([710.10.08])
     + sum([710.10.10])
     + sum([710.11.00])
     + sum([710.11.08])
     + sum([710.12.00])
     + sum([710.12.03])
     + sum([710.12.04])
     + sum([710.12.05])
     + sum([710.12.08])
     + sum([710.13.00])
     + sum([710.14.00])
     + sum([710.18.00])
     + sum([710.19.00])
     + sum([710.20.00])
     + sum([710.20.02])
     + sum([710.20.03])
     + sum([720.00.00])
     + sum([720.00.05])
     + sum([720.00.06])
     + sum([731.01])
     + sum([732.01])
     + sum([733.01])
     + sum([788.01])
     + sum([788.02])
     + sum([788.04])
     + sum([788.90])
     + sum([788.99])
     + sum([791.01])
     + sum([791.02])
     + sum([800.01])
     + sum([800.03])
     + sum([800.04])
     + sum([801.01])
     + sum([801.02])
     + sum([801.03])
     + sum([801.04])
     + sum([801.05])
     + sum([801.06])
     + sum([810.01])
     + sum([810.02])
     + sum([820.01])
     + sum([820.02])
     + sum([820.03])
     + sum([820.04])
     [Total]
     from (select cus.nome
    , cast(case when STT.STID = '59915' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [0.121.01.0]
    , cast(case when STT.STID = '59345' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [0.129.04.0]
    , cast(case when STT.STID = '59350' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [0.129.05.0]
    , cast(case when STT.STID = '59449' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [0.781.04.01]
    , cast(case when STT.STID = '59349' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [101.01.04]
    , cast(case when STT.STID = '58749' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [101.05.03]
    , cast(case when STT.STID = '58995' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [101.05.04]
    , cast(case when STT.STID = '58994' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [101.05.05]
    , cast(case when STT.STID = '59360' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [101.05.10]
    , cast(case when STT.STID = '59317' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [101.90.01]
    , cast(case when STT.STID = '59682' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [102.00.06]
    , cast(case when STT.STID = '59661' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [102.00.08]
    , cast(case when STT.STID = '59684' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [102.03.05]
    , cast(case when STT.STID = '59683' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [102.03.09]
    , cast(case when STT.STID = '59685' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [102.03.11]
    , cast(case when STT.STID = '59686' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [102.12.07]
    , cast(case when STT.STID = '59687' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [102.12.10]
    , cast(case when STT.STID = '59688' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [102.13.11]
    , cast(case when STT.STID = '59329' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [109.01.01]
    , cast(case when STT.STID = '59320' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [110.00.14]
    , cast(case when STT.STID = '59733' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [110.00.15]
    , cast(case when STT.STID = '59310' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [110.01]
    , cast(case when STT.STID = '59975' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [110.02]
    , cast(case when STT.STID = '59779' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [110.10.02]
    , cast(case when STT.STID = '59780' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [110.10.03]
    , cast(case when STT.STID = '59781' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [110.10.04]
    , cast(case when STT.STID = '59782' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [110.10.05]
    , cast(case when STT.STID = '59783' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [110.10.06]
    , cast(case when STT.STID = '59973' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [114.23.00]
    , cast(case when STT.STID = '59976' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [114.40.00]
    , cast(case when STT.STID = '59977' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [114.40.01]
    , cast(case when STT.STID = '59978' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [114.40.02]
    , cast(case when STT.STID = '59635' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.00.01]
    , cast(case when STT.STID = '59636' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.00.02]
    , cast(case when STT.STID = '59637' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.00.03]
    , cast(case when STT.STID = '59638' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.00.04]
    , cast(case when STT.STID = '60059' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.01.01]
    , cast(case when STT.STID = '60060' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.01.02]
    , cast(case when STT.STID = '60061' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.01.03]
    , cast(case when STT.STID = '60062' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.01.04]
    , cast(case when STT.STID = '59642' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.05.01]
    , cast(case when STT.STID = '59643' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.05.02]
    , cast(case when STT.STID = '59644' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.05.03]
    , cast(case when STT.STID = '59796' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.10.01]
    , cast(case when STT.STID = '59797' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.10.02]
    , cast(case when STT.STID = '59798' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.10.03]
    , cast(case when STT.STID = '59799' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.10.04]
    , cast(case when STT.STID = '60048' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.10.05]
    , cast(case when STT.STID = '60080' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.11.01]
    , cast(case when STT.STID = '60081' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.11.02]
    , cast(case when STT.STID = '60082' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.11.03]
    , cast(case when STT.STID = '60083' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.11.04]
    , cast(case when STT.STID = '60092' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.12.05]
    , cast(case when STT.STID = '59649' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.14.14]
    , cast(case when STT.STID = '59651' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.16.01]
    , cast(case when STT.STID = '59652' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.16.02]
    , cast(case when STT.STID = '59653' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.16.03]
    , cast(case when STT.STID = '59654' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.16.04]
    , cast(case when STT.STID = '60072' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.21.01]
    , cast(case when STT.STID = '60073' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.21.04]
    , cast(case when STT.STID = '59656' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.30.01]
    , cast(case when STT.STID = '59657' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.30.02]
    , cast(case when STT.STID = '58984' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.30.03]
    , cast(case when STT.STID = '58985' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.30.04]
    , cast(case when STT.STID = '58986' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.30.05]
    , cast(case when STT.STID = '59280' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.30.07]
    , cast(case when STT.STID = '59281' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [115.30.15]
    , cast(case when STT.STID = '59312' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [120.02]
    --*** Line 1
    , cast(case when STT.STID = '59313' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [120.03]
    , cast(case when STT.STID = '60100' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [120.05.09]
    , cast(case when STT.STID = '59750' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [121.00.03]
    , cast(case when STT.STID = '59755' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [121.00.04]
    , cast(case when STT.STID = '59751' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [121.00.05]
    , cast(case when STT.STID = '59752' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [121.00.06]
    , cast(case when STT.STID = '59754' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [121.00.08]
    , cast(case when STT.STID = '59846' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [191.01]
    , cast(case when STT.STID = '59848' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [192.01]
    , cast(case when STT.STID = '60047' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [192.02]
    , cast(case when STT.STID = '59905' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [193.01.01]
    , cast(case when STT.STID = '59921' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [193.01.06]
    , cast(case when STT.STID = '59922' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [193.01.07]
    , cast(case when STT.STID = '59943' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [193.01.08]
    , cast(case when STT.STID = '59946' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [193.01.09]
    , cast(case when STT.STID = '60033' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [193.01.10]
    , cast(case when STT.STID = '60012' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [193.02.99]
    , cast(case when STT.STID = '58116' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [201.13.06]
    , cast(case when STT.STID = '59662' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [202.14.07]
    , cast(case when STT.STID = '59343' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [681.08.3]
    , cast(case when STT.STID = '59566' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [700.02]
    , cast(case when STT.STID = '57209' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [700.10]
    , cast(case when STT.STID = '57210' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [700.12]
    , cast(case when STT.STID = '57212' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [700.14]
    , cast(case when STT.STID = '59059' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [700.16]
    , cast(case when STT.STID = '59060' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [700.18]
    , cast(case when STT.STID = '57218' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.01.01]
    , cast(case when STT.STID = '57219' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.01.02]
    , cast(case when STT.STID = '59012' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.01.04]
    , cast(case when STT.STID = '53257' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.05.01]
    , cast(case when STT.STID = '57232' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.05.02]
    , cast(case when STT.STID = '57234' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.05.04]
    , cast(case when STT.STID = '58746' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.05.05]
    , cast(case when STT.STID = '59027' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.05.06]
    , cast(case when STT.STID = '59026' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.05.10]
    , cast(case when STT.STID = '58982' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.09.00]
    , cast(case when STT.STID = '57252' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.09.02]
    , cast(case when STT.STID = '57253' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.09.03]
    , cast(case when STT.STID = '57254' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.09.04]
    , cast(case when STT.STID = '59039' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.09.05]
    , cast(case when STT.STID = '60006' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.09.09]
    , cast(case when STT.STID = '57258' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.10.03]
    , cast(case when STT.STID = '57259' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.10.04]
    , cast(case when STT.STID = '59043' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.10.05]
    , cast(case when STT.STID = '58755' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.10.06]
    , cast(case when STT.STID = '59632' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.10.09]
    , cast(case when STT.STID = '58748' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.11.00]
    , cast(case when STT.STID = '57261' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.11.01]
    , cast(case when STT.STID = '57262' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.11.02]
    , cast(case when STT.STID = '57263' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.11.03]
    , cast(case when STT.STID = '57264' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.11.04]
    , cast(case when STT.STID = '59611' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.11.09]
    , cast(case when STT.STID = '59048' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.12.00]
    , cast(case when STT.STID = '57268' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.12.03]
    , cast(case when STT.STID = '57269' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.12.04]
    , cast(case when STT.STID = '59050' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.12.06]
    , cast(case when STT.STID = '60013' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.12.09]
    , cast(case when STT.STID = '60051' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.12.11]
    , cast(case when STT.STID = '59055' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.13.03]
    , cast(case when STT.STID = '58750' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.13.04]
    , cast(case when STT.STID = '59056' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.13.05]
    , cast(case when STT.STID = '59057' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.13.06]
    , cast(case when STT.STID = '59971' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.13.09]
    , cast(case when STT.STID = '59061' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.14.00]
    , cast(case when STT.STID = '59000' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.14.03]
    , cast(case when STT.STID = '59064' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.14.04]
    , cast(case when STT.STID = '59303' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.15.03]
    , cast(case when STT.STID = '59999' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.16.04]
    --*** Line 2
    , cast(case when STT.STID = '60000' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [701.16.09]
    , cast(case when STT.STID = '57278' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.00.04]
    , cast(case when STT.STID = '59665' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.00.06]
    , cast(case when STT.STID = '59663' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.00.08]
    , cast(case when STT.STID = '59664' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.00.10]
    , cast(case when STT.STID = '59336' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.08.00]
    , cast(case when STT.STID = '59666' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.08.03]
    , cast(case when STT.STID = '59667' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.08.06]
    , cast(case when STT.STID = '59669' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.08.07]
    , cast(case when STT.STID = '59668' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.08.10]
    , cast(case when STT.STID = '59670' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.11.12]
    , cast(case when STT.STID = '59671' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.12.07]
    , cast(case when STT.STID = '59672' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.13.07]
    , cast(case when STT.STID = '59673' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.13.11]
    , cast(case when STT.STID = '59674' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.14.07]
    , cast(case when STT.STID = '57369' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [702.20.01]
    , cast(case when STT.STID = '57375' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.00.00]
    , cast(case when STT.STID = '58983' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.00.14]
    , cast(case when STT.STID = '59873' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.06.00]
    , cast(case when STT.STID = '59878' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.07.00]
    , cast(case when STT.STID = '59882' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.08.00]
    , cast(case when STT.STID = '59901' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.09.00]
    , cast(case when STT.STID = '59826' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.10.05]
    , cast(case when STT.STID = '57533' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.10.08]
    , cast(case when STT.STID = '57535' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.10.10]
    , cast(case when STT.STID = '57540' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.11.00]
    , cast(case when STT.STID = '57548' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.11.08]
    , cast(case when STT.STID = '57555' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.12.00]
    , cast(case when STT.STID = '57558' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.12.03]
    , cast(case when STT.STID = '57559' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.12.04]
    , cast(case when STT.STID = '57560' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.12.05]
    , cast(case when STT.STID = '57563' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.12.08]
    , cast(case when STT.STID = '57570' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.13.00]
    , cast(case when STT.STID = '57585' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.14.00]
    , cast(case when STT.STID = '59827' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.18.00]
    , cast(case when STT.STID = '59855' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.19.00]
    , cast(case when STT.STID = '59139' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.20.00]
    , cast(case when STT.STID = '59141' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.20.02]
    , cast(case when STT.STID = '59142' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [710.20.03]
    , cast(case when STT.STID = '59116' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [720.00.00]
    , cast(case when STT.STID = '59120' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [720.00.05]
    , cast(case when STT.STID = '59121' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [720.00.06]
    , cast(case when STT.STID = '59301' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [731.01]
    , cast(case when STT.STID = '59451' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [732.01]
    , cast(case when STT.STID = '59548' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [733.01]
    , cast(case when STT.STID = '59298' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [788.01]
    , cast(case when STT.STID = '59300' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [788.02]
    , cast(case when STT.STID = '59359' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [788.04]
    , cast(case when STT.STID = '59319' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [788.90]
    , cast(case when STT.STID = '59314' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [788.99]
    , cast(case when STT.STID = '58760' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [791.01]
    , cast(case when STT.STID = '58758' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [791.02]
    , cast(case when STT.STID = '59071' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [800.01]
    , cast(case when STT.STID = '59073' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [800.03]
    , cast(case when STT.STID = '59344' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [800.04]
    , cast(case when STT.STID = '58759' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [801.01]
    , cast(case when STT.STID = '58751' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [801.02]
    , cast(case when STT.STID = '58753' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [801.03]
    , cast(case when STT.STID = '60057' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [801.04]
    , cast(case when STT.STID = '58979' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [801.05]
    , cast(case when STT.STID = '58993' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [801.06]
    , cast(case when STT.STID = '58752' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [810.01]
    , cast(case when STT.STID = '58981' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [810.02]
    , cast(case when STT.STID = '58978' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [820.01]
    , cast(case when STT.STID = '58754' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [820.02]
    , cast(case when STT.STID = '58980' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [820.03]
    , cast(case when STT.STID = '59969' then isnull(sum(round(FII.epv * FII.qtt,2)), 0) else 0 end as int)  [820.04]
    from ft FTT
        inner join      [fi] FII
        on              FII.ftstamp        = FTT.ftstamp
        inner join      st STT
        on              STT.ref      = FII.ref
        inner join      CL cus
        on              cus.no      = FTT.no
    where round(FII.epv * FII.qtt,2) is not null

    group by cus.nome, STT.STID) derivedtable
    group by nome
     order by nome
    But SQL server 2005, return the following error:

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type int.

    Can someone explain me why this error occur

    Many thanks
    Luis Santos
View as RSS news feed in XML