Skip to Main Content
  • Questions
  • insert only one row on IG report depends on value of page item


Question and Answer

Connor McDonald

Thanks for the question, Huy.

Asked: October 09, 2020 - 4:35 am UTC

Answered by: Connor McDonald - Last updated: October 15, 2020 - 4:32 am UTC

Category: Application Express - Version:

Viewed 100+ times

You Asked

Hi everyone,
I have a challenge case as: I have a select list page item and one IG report. I want to when I choose a page item valued by 'W', it allows IG report only one row. While I choose another value, it still allows many rows for IG report.

I try after submit with code :
v_count number;

select count(*)
into v_count
from E_EDUFIT_MARKS_DETAIL md, e_edufit_marks m
where md.marks_id = m.marks_id
and m.type_marks = 'W'
AND md.marks_id = :P2645_MARKS_ID;

if v_count > 1  then return 'FALSE';
end if;

I think it may be successfully when one row is created before. I wonder that when create new records, user click add row (over 2 rows) it wil be not run. I have not found a way to solve that problem. I hope you help me have a good idea. Thank in advance.

and we said...

I think you would be better off with a unique constraint on the database here, because otherwise even if the code works, multiple users will be able to do the operation at the same time.

For example, to have a single row of type W and multiple rows of anything else for (say) each employee, you would do:

create unique index IX on EMP  ( case when type = 'W' then empno end );

assuming here that empno is the primary key.

and you rated our response

  (2 ratings)


Not done for my question

October 14, 2020 - 7:59 am UTC

Reviewer: A reader

Thank for your code. I try it which follows your sample, I run it on DB:
create unique index IX on e_edufit_marks m  ( case when m.type_marks = 'W' then m.marks_id end );

where marks_id is a primary key

But when I test on oracle apex with page_item selected 'W', it still allow add over 1 row. Can you make more clear for me?
Connor McDonald


October 15, 2020 - 4:32 am UTC

What happens when you press Save in the IG ?


October 16, 2020 - 6:18 am UTC

Reviewer: A reader

When I press Save on IG button, it will allow one row for any value of page_item. And when editing the old record, Save button process nothing.

But I do not use Save button to create a new record.

More to Explore


New to good database design? Check out Chris Saxon's full fundamentals class.