Skip to Main Content
Forums

APEX

Announcement

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!

IG: how i can add text/header after Add Row button in same area

SmithJohn45Oct 26 2021

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
This post has been answered by msammour on Oct 26 2021
Jump to Answer

Comments

Anurag Tibrewal
Hi,

One way
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
Regards
Anurag
1 person found this helpful
fsitja 
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
Can you have more than one record on table_b for each month? If so it won't work as this. I'm simply looking up the record from the previous month here.
1 person found this helpful
Solomon Yakobson
If you understood your reqs correctly, it should be:
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>
Two months prior 1/12/2008 is 11/12/2007 which falls before the earliest record for acct 123. One month prior 1/12/2008 is 12/12/2007 which falls between 11/30/2007 and 12/31/2007 and therefore score should be 620, not 650.

SY.
Frank Kulash
Hi,
Anurag Tibrewal wrote:
Hi,

One way
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
Regards
Anurag
Right idea. I'm sure if OP had posted some sample data, then you would have tested it and found the mistakes.

We don't want to use TO_CHAR here. If this is run in month X, then we usually want months X-2 and X-1, but not early in the year. Right now, the month i s '01', but the months wanted would be '11' and '12', ,not '-1' and '00'.

Also, table alias b has no meaning in the main query.

I think you meant something like: 
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
;
Edited by: Frank Kulash on Jan 22, 2010 3:00 PM
Changed SYSDATE to b.date_upload

Edited by: Frank Kulash on Jan 22, 2010 3:57 PM
Fixed several typos
Anurag Tibrewal
Agreed Frank,
You are really a guru.

Regards
Anurag
684479
I got a ORA-00904 error indicating b.score invalid.

I also changed the second score1priormonth to score2priormonth, don't we need to look at year as well? Thanks,
684479
No, one rcord for each month but sometimes the month end dates got screwed up (not month and year they are fine).
684479
Hi Frank, I keep getting a missing right parenthesis message but I think all of them are accounted for. We need to name month_between calculation as priormonth correct?
Frank Kulash
Answer
Hi,

Whenever you have a question, post a little sample data in a form people can actually use. CREATE TABLE and INSERT statements are best. A WITH clause, like Solomon posted, is better than nothing. Without that, people will help you as much as they can, but don't expect them to test anything.

Using Solomon's data, I was able to test (and correct) my query. 
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
;
ORA-00907 really means "absolutely clue-free regarding what you meant".
Perhaps a missing right parenthesis causes the error as much as anything, but when you get that error, look for any kind of syntax error at (or slightly before) the point indicated.

You're right, I did forget the column alias. Sorry; if I could have tested it before posting, I would have.
Marked as Answer by 684479 · Sep 27 2020
684479
I moved the join condition inside of the where clause and it worked (not sure why "on" would not work and give that Alicia Silverstone Clueless error).

Few issues though:

I get scores from months after change date not prior, two months prior is two months after score and one month prior is one month after. I looked at the syntax can't see why.

I missed some records in table A, is there anyway all the records from base table are returned?

Thanks,
Frank Kulash
Hi,

Sorry, I had the dates backwards.
Swap all references to a.date_changed and b.date_upload in sub-query got_priormonth: 
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
;
Output, using Solomon's data: 
.     ACCT SCORE2MONTHPRIOR SCORE1MONTHPRIOR
---------- ---------------- ----------------
       123              620              650
       456              630              680
684479
Perfect!

Thanks, Frank.
Aketi Jyuuzou
Hehe I like Pivot B-)
But recently,I think that group by and max and decode is sometimes readable than Pivot.
Because Pivot do implicit group by and we must image it implicit group by
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
Aketi Jyuuzou
I like this query more than my previous :8} 
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
1 person found this helpful
1 - 14

Post Details

Added on Oct 26 2021
#apex-discussions
4 comments
478 views
1 mention