Interactive Grid: Validation - Check for duplicated column entries over all rows
Von Tobias Arnhold →
4.01.2020
I had this situation now a few times and was always to lazy to write it down. :/
During my last task within the fabe project I hat to create a validation to check for duplicated entries inside an Interactive Grid.
This blog post from Lino Schilde was a good start for my final solution:
-- select only if the current row is set to Y
Tip of the day
All those meeting clients (Skype, Zoom, Hangouts ...) can also be used for APEX friends and beer meetups. #beer #orclapex
During my last task within the fabe project I hat to create a validation to check for duplicated entries inside an Interactive Grid.
Whenever I add "None of the above" twice, an error should occur:
Interactive Grid Validation
Validation of Type: PL/SQL Function (returning Error Text)
Code:
declare
v_cnt number;
begin
v_cnt number;
begin
-- check only if insert or update (not delete "D")
if :APEX$ROW_STATUS in ('C','U') then
if :APEX$ROW_STATUS in ('C','U') then
-- select only if the current row is set to Y
-- positive result if one answer was set to Y
select max(case when none_yn = 'Y' then 1 else 0 end)
into v_cnt
from answer
where question_id = :P301_QUESTION_ID
and answer_id != nvl(:ANSWER_ID,0)
and :NONE_YN = 'Y';
if v_cnt = 1 then
return 'Another answer was already set up with "None of the above". You need to change and save it first.';
end if;
end if;
end;
into v_cnt
from answer
where question_id = :P301_QUESTION_ID
and answer_id != nvl(:ANSWER_ID,0)
and :NONE_YN = 'Y';
if v_cnt = 1 then
return 'Another answer was already set up with "None of the above". You need to change and save it first.';
end if;
end if;
end;
My solution used a "max" aggregation within a "case when" trick to get the right result.
Maxime Tremblay gave me a really important tip:
If I add more then one row through the IG and press save. The validation is not gone be triggered.
To fix that you to add a unique index:
create unique index ANSWER_UK1 on ANSWER (
case when NONE_YN = 'Y' then QUESTION_ID end
);
Maxime Tremblay gave me a really important tip:
If I add more then one row through the IG and press save. The validation is not gone be triggered.
To fix that you to add a unique index:
create unique index ANSWER_UK1 on ANSWER (
case when NONE_YN = 'Y' then QUESTION_ID end
);
had to had a look at it :P
— askMax - Maxime Tremblay (@askMaxSolutions) April 2, 2020
used this unique index:
create unique index ANSWERS_UK1 on ANSWERS (
case when NONE_YN = 'Y' then QUESTION_ID end
);
obviously, the error handling function would display a better/nicer error message to the user pic.twitter.com/u6bLWWpvjz
--
--
Tip of the day
All those meeting clients (Skype, Zoom, Hangouts ...) can also be used for APEX friends and beer meetups. #beer #orclapex
