When we run a Microstrategy report that is pointing to Denodo, we are not getting the correct results and SQL. The report needs the top 3 records.
When the report is run against DB2, the SQL contains a Rank function.
When the same report is run against Denodo, we are seeing SQL statements as shown below. We are not able to explain where the number 6865898.22999999 came from.
Is Denodo doing the Ranking on the Denodo server and substituing the number 6865898.22999999 in the SQL statement ? Even if it is, the number 6865898.22999999 is incorrect because we are not getting the correct top 3 records.
Denodo SQLin Microstrategy report
insert into TTQHUS61WMR000
select ctry_cd ,
sum(a11.amt_xb) WJXBFS1
from dpd.iv_issr_qtr a11
where ( a11.bus_id in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 10000000, 10000001, 10000002)
and a11.prod_srce_cd_vcis in ('C', 'D', ' ', '-', 'H', 'M', 'N', 'P', 'R')
)
group by a11.ctry_cd
select pa11.WJXBFS1 WJXBFS1
from TTQHUS61WMR000 pa11
order by 1 desc
insert into TMQ5EDHT8MQ001
select pa11.ctry_cd ctry_cd
from TTQHUS61WMR000 pa11
where (pa11.WJXBFS1 >= 6865898.22999999)
