Skip to Content
0
Former Member
Feb 16, 2009 at 10:42 AM

Derived table - not all values being displayed in list in report

30 Views

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?