I have a derived table, which selects 6 values when I run the SQL from the SQLPlus prompt. But when I view the values in a list in desktop intelligence, there are only 4. I have tried to remove 2 values from the 6, but then only 2 values are displayed!
Following is the SQL for my derived table:
select max(TRDATE) AS THE_DATE,
'Last_COB_'||b.compcd AS THE_FILTER,
b.compcd AS THE_COMPCD,
max(b.repno) AS THE_REPNO
from ECLIPSE.BATCH_DATES b, ECLIPSE.COMPANY c
where b.compcd = c.compcd
group by b.compcd
UNION
select max(b.trdate), p_filter, p_compcd, max(b.repno)
from batch_dates b,
( select last_day(add_months(trdate,-1)) AS P_DATE,
'MTD_'||b.compcd AS P_FILTER,
b.compcd P_COMPCD
from ECLIPSE.BATCH_DATES b, ECLIPSE.COMPANY c
where trdate = (select max(c.trdate) from ECLIPSE.BATCH_DATES c where compcd = b.compcd )
and b.compcd = c.compcd
group by b.compcd, trdate ) c
where b.trdate <= c.p_date
and b.compcd = c.p_compcd
group by p_filter, p_compcd
UNION
select max(b.trdate), p_filter, p_compcd, max(b.repno)
from batch_dates b,
( select to_date('31-DEC'||substr('13-FEB-08',7,3)) AS P_DATE, --to_date('31-DEC'||substr(add_months(trdate,-12),7,3))
'YTD_'||b.compcd AS P_FILTER,
b.compcd P_COMPCD
from ECLIPSE.BATCH_DATES b, ECLIPSE.COMPANY c
where trdate = (select max(c.trdate) from ECLIPSE.BATCH_DATES c where compcd = b.compcd )
and b.compcd = c.compcd
group by b.compcd, b.trdate ) c
where b.trdate <= c.p_date
and b.compcd = c.p_compcd
group by p_filter, p_compcd
The YTD values are the ones which are never displayed. Does anyone know anything about this?