| checking for duplicates in multirecord block [message #85860] |
Thu, 05 August 2004 00:57  |
Neeti
Messages: 7
Registered: November 2001
|
Junior Member |
|
|
I have a form with two blocks. The second block is a tabular block which displays 20 records . Its a DB block. Before committing , I need to check if there are duplicate entries in onw of the form items. e.g Say my tabular block has 2 items. The user enters a twice in item1. and then presses commit key. It should raise error . E.g
Item1 item2
a 23
a 565
b 676
c 4
A is entered twice so it should raise form trigger failure.
What i did was i created the following code
go_block('IPC');
first_record;
declare
L_found varchar2(1);
cnt number := 0;
Begin
loop
:global.item_index := :ipc.item_index;
loop
first_record;
If :ipc.item_index = :global.item_index then
cnt := cnt + 1;
end if;
exit when :system.last_record = 'TRUE';
next_record;
end loop;
next_record;
exit when :system.last_record = 'TRUE';
end loop;
if :system.last_record = 'TRUE' then
:global.item_index := :ipc.item_index;
first_record;
loop
If :ipc.item_index = :global.item_index then
cnt := cnt + 1;
end if;
exit when :system.last_record = 'TRUE';
next_record;
end loop;
If cnt > 0 then
p_show_alert('Duplicate Item Index found');
raise form_trigger_failure;
else
commit_form;
end if;
end if;
end;
wherein item_index id the form item which i am checking if the user has entered the same value twice. But the logic just hangs when i run it. I was a bit confused as to how when i am at the first record the first record value should be taken and then compared with the other values.
Thanks in advance.
|
|
|
|
| Re: checking for duplicates in multirecord block [message #85876 is a reply to message #85860] |
Sun, 08 August 2004 05:30  |
omar
Messages: 19
Registered: February 2002
|
Junior Member |
|
|
The problem is that you are not saving the record number!! the following code is not compiled so it may contain errors.
go_block('IPC');
first_record;
declare
L_found varchar2(1);
cnt number := 0;
Record_number number;
Begin
loop outer
record_number:=:SYSTEM.CURSOR_RECORD;
:global.item_index := :ipc.item_index;
loop inner
first_record;
If :ipc.item_index = :global.item_index then
cnt := cnt + 1;
end if;
exit when :system.last_record = 'TRUE' OR CNT > 1;
next_record;
end loop inner;
GO_RECORD(RECORD_NUMBER);
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE' OR CNT > 1;
NEXT_RECORD;
END LOOP OUTER;
IF CNT > 1 THEN
p_show_alert('Duplicate Item Index found');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
|
|
|
|
| Re: checking for duplicates in multirecord block [message #85883 is a reply to message #85876] |
Sun, 08 August 2004 21:59 |
Neeti
Messages: 7
Registered: November 2001
|
Junior Member |
|
|
Hi,
This too is hanging. I think the reason being, That the inner loop has got a FIRST_RECORD; and then there is a Next_record at the end. So everytime the next_Record in the inner loop is executed and it comes back to the top, there is first_Record, so it will always stay at the first record. So it hangs. Any other workaround. Thanks for the help though.
go_block('IPC');
first_record;
declare
L_found varchar2(1);
cnt number := 0;
Record_number number;
Begin
loop outer
record_number:=:SYSTEM.CURSOR_RECORD;
:global.item_index := :ipc.item_index;
loop inner
first_record;
If :ipc.item_index = :global.item_index then
cnt := cnt + 1;
end if;
exit when :system.last_record = 'TRUE' OR CNT > 1;
next_record;
end loop inner;
GO_RECORD(RECORD_NUMBER);
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE' OR CNT > 1;
NEXT_RECORD;
END LOOP OUTER;
IF CNT > 1 THEN
p_show_alert('Duplicate Item Index found');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
|
|
|
|
| Re: checking for duplicates in multirecord block [message #85893 is a reply to message #85883] |
Mon, 09 August 2004 22:25 |
omar
Messages: 19
Registered: February 2002
|
Junior Member |
|
|
I had the same problem in a form. but i had to check many blocks for duplication so i created a control block named dummy that contains 1 items(dummy_index). and used that block as a temp block for saving the values to compare.
GO_BLOCK('BLK_DUMMY');
CLEAR_BLOCK(NO_COMMIT);
go_block('ipc');
FIRST_RECORD;
LOOP
-------- insert into the dummy block
:BLK_DUMMY.DUMMY_index := :ipc.item_index
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
GO_BLOCK('BLK_DUMMY');
NEXT_RECORD;
CREATE_RECORD;
go_block('ipc');
NEXT_RECORD;
END LOOP;
GO_BLOCK('BLK_DUMMY');
FIRST_RECORD;
LOOP
GO_BLOCK('ipc');
FIRST_RECORD;
LOOP
IF :BLK_DUMMY.DUMMY_index= :BLK_EVENT_COMMANDS.COMMAND_ORDER THEN
V_counter := V_counter + 1;
END IF;
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE' OR V_COUNTER > 1;
NEXT_RECORD;
END LOOP;
IF v_counter > 1 THEN
p_show_alert('Duplicate Item Index found');
RAISE FORM_TRIGGER_FAILURE;
END IF;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
