For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
hi all, working at apex.oracle.com how i can add a Text as Header on IG after Add Row button? i want to save screen area. regards
with t1 as ( select a.acct , b.score , to_char(a.date_changed,'MM') - to_char(b.dateupload,'MM') priormonth from tableA a, tableB b where a.acct=b.acct and (to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM') +1 or to_char(a.date_changed,'MM') = to_char(b.dateupload,'MM')+2) ) select t1.acct , max(decode(priormonth,1, b.score,0)) score1priormonth , max(decode(priormonth,2,b.score,0)) score1priormonth from t1 group by acct
SELECT acct, score2monthprior, score1monthprior FROM (SELECT a.*, b.dateupload, b.score, lag(b.dateupload) over(PARTITION BY a.acct, a.datechanged ORDER BY b.dateupload) dt_prv_upload, lag(b.score) over(PARTITION BY a.acct, a.datechanged ORDER BY b.dateupload) score1monthprior, lag(b.score, 2) over(PARTITION BY a.acct, a.datechanged ORDER BY b.dateupload) score2monthprior FROM table_a a JOIN table_b b ON a.acct = b.acct ORDER BY b.dateupload) WHERE datechanged BETWEEN dt_prv_upload AND dateupload
with a as ( select 123 acct,to_date('1/12/2008','mm/dd/yyyy') date_changed from dual union all select 456,to_date('8/25/2008','mm/dd/yyyy') from dual ), b as ( select 123 acct,to_date('11/30/2007','mm/dd/yyyy') date_upload,620 score from dual union all select 123,to_date('12/31/2007','mm/dd/yyyy'),650 from dual union all select 123,to_date('01/31/2008','mm/dd/yyyy'),712 from dual union all select 123,to_date('02/29/2008','mm/dd/yyyy'),800 from dual union all select 456,to_date('05/31/2008','mm/dd/yyyy'),520 from dual union all select 456,to_date('06/30/2008','mm/dd/yyyy'),630 from dual union all select 456,to_date('07/31/2008','mm/dd/yyyy'),680 from dual union all select 456,to_date('08/31/2008','mm/dd/yyyy'),710 from dual union all select 456,to_date('09/30/2008','mm/dd/yyyy'),780 from dual union all select 456,to_date('10/31/2008','mm/dd/yyyy'),786 from dual union all select 456,to_date('11/30/2008','mm/dd/yyyy'),823 from dual ), c as ( select acct, date_upload, score, lead(date_upload,1,sysdate) over(partition by acct order by date_upload) next_date_upload from b ) select acct, (select score from c where a.acct = c.acct and add_months(date_changed,-2) >= date_upload and add_months(date_changed,-2) < next_date_upload) Score2MonthPrior, (select score from c where a.acct = c.acct and add_months(date_changed,-1) >= date_upload and add_months(date_changed,-1) < next_date_upload) Score1MonthPrior from a / ACCT SCORE2MONTHPRIOR SCORE1MONTHPRIOR ---------- ---------------- ---------------- 123 620 456 520 630 SQL>
WITH got_priormonth AS ( SELECT a.acct , b.score , MONTHS_BETWEEN ( TRUNC (b.date_upload, 'MONTH') , TRUNC (a.date_changed, 'MONTH') ) AS priormonth -- Alias added FROM tableA a JOIN tableB b ON a.acct = b.acct WHERE a.date_changed >= ADD_MONTHS ( TRUNC (b.date_upload, 'MONTH') , -2 ) AND a.date_changed < TRUNC (b.date_upload, 'MONTH') ) SELECT acct , MAX (CASE WHEN priormonth = 2 THEN score ELSE 0 END) AS score2monthprior , MAX (CASE WHEN priormonth = 1 THEN score ELSE 0 END) AS score1monthprior FROM got_priormonth GROUP BY acct ;
WITH got_priormonth AS ( SELECT a.acct , b.score , MONTHS_BETWEEN ( TRUNC (a.date_changed, 'MONTH') , TRUNC (b.date_upload, 'MONTH') ) AS priormonth FROM tableA a JOIN tableB b ON a.acct = b.acct WHERE b.date_upload >= ADD_MONTHS ( TRUNC (a.date_changed, 'MONTH') , -2 ) AND b.date_upload < TRUNC (a.date_changed, 'MONTH') ) SELECT acct , MAX (CASE WHEN priormonth = 2 THEN score ELSE 0 END) AS score2monthprior , MAX (CASE WHEN priormonth = 1 THEN score ELSE 0 END) AS score1monthprior FROM got_priormonth GROUP BY acct ;
. ACCT SCORE2MONTHPRIOR SCORE1MONTHPRIOR ---------- ---------------- ---------------- 123 620 650 456 630 680
with a as ( select 123 acct,to_date('1/12/2008','mm/dd/yyyy') date_changed from dual union all select 456,to_date('8/25/2008','mm/dd/yyyy') from dual), b as ( select 123 acct,to_date('11/30/2007','mm/dd/yyyy') date_upload,620 score from dual union all select 123,to_date('12/31/2007','mm/dd/yyyy'),650 from dual union all select 123,to_date('01/31/2008','mm/dd/yyyy'),712 from dual union all select 123,to_date('02/29/2008','mm/dd/yyyy'),800 from dual union all select 456,to_date('05/31/2008','mm/dd/yyyy'),520 from dual union all select 456,to_date('06/30/2008','mm/dd/yyyy'),630 from dual union all select 456,to_date('07/31/2008','mm/dd/yyyy'),680 from dual union all select 456,to_date('08/31/2008','mm/dd/yyyy'),710 from dual union all select 456,to_date('09/30/2008','mm/dd/yyyy'),780 from dual union all select 456,to_date('10/31/2008','mm/dd/yyyy'),786 from dual union all select 456,to_date('11/30/2008','mm/dd/yyyy'),823 from dual) select Acct,Score2MonthPrior,Score1MonthPrior from (select a.acct,b.score, extract( year from a.date_changed)*12 +extract(month from a.date_changed) -extract( year from b.date_upload)*12 -extract(month from b.date_upload) as diff from a join b on a.acct = b.acct) Pivot (max(score) for diff in(2 as Score2MonthPrior, 1 as Score1MonthPrior)); ACCT Score2MonthPrior Score1MonthPrior ---- ---------------- ---------------- 123 620 650 456 630 680
select acct, max(decode(diff,2,score)) as Score2MonthPrior max(decode(diff,1,score)) as Score1MonthPrior, from (select a.acct,b.score, months_between(trunc(a.date_changed,'mm'), trunc(b.date_upload,'mm')) as diff from a join b on a.acct = b.acct) where diff in(1,2) group by acct; acct Score2MonthPrior Score1MonthPrior ---- ---------------- ---------------- 123 620 650 456 630 680