I am trying to run the query bellow, and I get a mismatched datatype error for the two "sum" computation columns. I thought that using "null" in the corresponding columns would eliminate the problem, but it does not. If I print the "sum" columns from both select statements in the same column it works, but I need them to be side by side...any ideas?
select o.market,
sum(
round(t.gross_c omm * e.ex_for_per_mi n * nvl(s.revsplit, 100)/100,
2))
+
sum(
round((t.tp - t.mkup) * e.ex_for_per_mi n * nvl(s.revsplit, 100)/100,
2)) "HK",null
from split s, exchange e, tinfoco t, client p, client c, orders o
where o.clientid = c.clientid
AND P.createloc like 'HK'
and p.clientid = nvl(c.profile_p arent_id,c.clie ntid)
and o.orderid = t.orderid
and o.tradedate = e.ex_date
and o.currid = e.currid
and exists (select null from client_profile prof
where prof.clientid = p.clientid)
and o.clientid = s.clientid(+)
and o.tradedate between to_date('14-JUN-2005','DD-MON-YYYY')
and to_date('14-JUN-2005','DD-MON-YYYY')
and nvl(o.errorflag ,'*') not in ('E','S')
group by o.market
union
select o.market,null,
sum(
round(t.gross_c omm * e.ex_for_per_mi n * nvl(s.revsplit, 100)/100,
2))
+
sum(
round((t.tp - t.mkup) * e.ex_for_per_mi n * nvl(s.revsplit, 100)/100,
2)) "NY"
from split s, exchange e, tinfoco t, client p, client c, orders o
where o.clientid = c.clientid
AND P.createloc like 'NY'
and p.clientid = nvl(c.profile_p arent_id,c.clie ntid)
and o.orderid = t.orderid
and o.tradedate = e.ex_date
and o.currid = e.currid
and exists (select null from client_profile prof
where prof.clientid = p.clientid)
and o.clientid = s.clientid(+)
and o.tradedate between to_date('14-JUN-2005','DD-MON-YYYY')
and to_date('14-JUN-2005','DD-MON-YYYY')
and nvl(o.errorflag ,'*') not in ('E','S')
group by o.market
select o.market,
sum(
round(t.gross_c omm * e.ex_for_per_mi n * nvl(s.revsplit, 100)/100,
2))
+
sum(
round((t.tp - t.mkup) * e.ex_for_per_mi n * nvl(s.revsplit, 100)/100,
2)) "HK",null
from split s, exchange e, tinfoco t, client p, client c, orders o
where o.clientid = c.clientid
AND P.createloc like 'HK'
and p.clientid = nvl(c.profile_p arent_id,c.clie ntid)
and o.orderid = t.orderid
and o.tradedate = e.ex_date
and o.currid = e.currid
and exists (select null from client_profile prof
where prof.clientid = p.clientid)
and o.clientid = s.clientid(+)
and o.tradedate between to_date('14-JUN-2005','DD-MON-YYYY')
and to_date('14-JUN-2005','DD-MON-YYYY')
and nvl(o.errorflag ,'*') not in ('E','S')
group by o.market
union
select o.market,null,
sum(
round(t.gross_c omm * e.ex_for_per_mi n * nvl(s.revsplit, 100)/100,
2))
+
sum(
round((t.tp - t.mkup) * e.ex_for_per_mi n * nvl(s.revsplit, 100)/100,
2)) "NY"
from split s, exchange e, tinfoco t, client p, client c, orders o
where o.clientid = c.clientid
AND P.createloc like 'NY'
and p.clientid = nvl(c.profile_p arent_id,c.clie ntid)
and o.orderid = t.orderid
and o.tradedate = e.ex_date
and o.currid = e.currid
and exists (select null from client_profile prof
where prof.clientid = p.clientid)
and o.clientid = s.clientid(+)
and o.tradedate between to_date('14-JUN-2005','DD-MON-YYYY')
and to_date('14-JUN-2005','DD-MON-YYYY')
and nvl(o.errorflag ,'*') not in ('E','S')
group by o.market