Home » Developer & Programmer » Forms » How to find a duplicate record before commit?
How to find a duplicate record before commit? [message #314823] Thu, 17 April 2008 23:16 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Hi..

I am using a form with three blocks. one of the block is based on table PRF. PRF table is as below

Name                                                  Null?    Type
----------------------------------------------------- -------- ----------------
CARDNO                                                         NUMBER(5)
PREFERENCES                                                    NUMBER(1)
FACULTY                                                        CHAR(4)



The field PREFERENCES only accepts number from 0 to 6.

My requirements is:

1. Number 0 can be repeated but other Numbers(1.2.3.4.5.6)are not allowed to be repeated.

I think Primary or Unique key constraints will not work in this situation.so how can I control this programatically. Please help me. I am stucked here...



Thanks

Riaz


[MERGED by LF]

[Updated on: Sat, 19 April 2008 11:00] by Moderator

Report message to a moderator

Re: Duplicate Record [message #314830 is a reply to message #314823] Thu, 17 April 2008 23:53 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Number 0 can be repeated but other Numbers(1.2.3.4.5.6)are not allowed to be repeated.
Is it true for the whole table, or is it restricted to a certain 'cardno'?

Check constraint will solve the problem of allowed numbers. WHEN-VALIDATE-ITEM might check whether such a number already exists in the table and suppress use of this value.
Re: Duplicate Record [message #314838 is a reply to message #314830] Fri, 18 April 2008 00:16 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
It is restricted to CARDNO. i.e

ALLOWED:
cardno preferences
80001 0
80001 0
80001 2
80001 0

80002 1
80002 2
80002 3
80002 0
80002 0
80002 6


NOT ALLOWED:
cardno preferences
80001 3
80001 3






. But before COMMIT , how the WHEN-VALIDATE-ITEM can check for repeated value? If yes, Please how...

Thanks
Riaz


Find duplicate before commit [message #314970 is a reply to message #314823] Fri, 18 April 2008 07:31 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
In forms 6i can one find duplicate records before commit to the database NOT using table or row level constraints?

riaz
Re: Find duplicate before commit [message #315129 is a reply to message #314970] Sat, 19 April 2008 04:49 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
hi rzkhan

you can control duplication like this


begin
select empno into :emp.empno from emp where empno=:emp.empno;
message('Record already exist');
message('Record already exist');
Raise form_trigger_failure;
Exception
when no_data_found then
null;
end;

dont feel hazitation for further question

owais
owaisuzair2007@yahoo.com
Re: Duplicate Record [message #315130 is a reply to message #314823] Sat, 19 April 2008 04:56 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
you can do like this


declare
a number(1):=a;
f_name varchar2(35);
begin
f_name:=:form_name;
while :form_name is not null loop
if f_name=:form_name then
a:=a+1;
next_record;
end loop;
previous_record;
if a=2 then
message('already exist');
message('already exist');
else
next_record;
end if;
end;

dobt feel haziate for further question

owaisuzair2007@yahoo.com

owais
Re: Duplicate Record [message #315689 is a reply to message #315130] Tue, 22 April 2008 07:36 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Could u explain the logic and which trigger to use?

thanks
riaz
Re: Duplicate Record [message #315724 is a reply to message #315130] Tue, 22 April 2008 10:03 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
hi rzkhan

its very simple this logic is restricting the duplicate records
in multiple records block like tabular fields
you can put this logic key-next-item or when-validate-record

both trigger you can use this logic will not check the database table records this will check multiple block only weither record we have selected in a multiple fields or not

thanks
owais

Re: Duplicate Record [message #315852 is a reply to message #315724] Wed, 23 April 2008 00:23 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Will the follwing work?
a number(1):=a;


Should I replace the following my field name
:agdtet03facprf.preferences

:form_name;


it also shows ILLEGAL CONSTRUCT IN when-validate-record
Re: Duplicate Record [message #316063 is a reply to message #315724] Wed, 23 April 2008 10:49 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
hi dear

:form_name is field where you have to put trigger key-next-item



thanks
owais

Re: How to find a duplicate record before commit? [message #316196 is a reply to message #314823] Thu, 24 April 2008 04:02 Go to previous messageGo to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Hi Owaise,

Where im wrong in this? it showing error ( Encountered the smbol Loop" 'when expecting one of the following if)


declare
a number(1):=a;
f_name varchar2(35);
begin
f_name:=:asset_master_new.arn_no;
while :asset_master_new.arn_no is not null loop
if f_name=:asset_master_new.arn_no then
a:=a+1;
next_record;
end loop;
previous_record;
if a=2 then
message('already exist');
message('already exist');
else
next_record;
end if;
end if;
end;

Re: How to find a duplicate record before commit? [message #316225 is a reply to message #316196] Thu, 24 April 2008 05:44 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Above is your original code; this is how it should look like. Next time, please, do it yourself. Because, if you properly formatted it, you'd easily see what is missing and where.

declare 
  a number(1):=a;
  f_name varchar2(35);
begin 
  f_name:=:asset_master_new.arn_no;
  while :asset_master_new.arn_no is not null loop
    if f_name=:asset_master_new.arn_no then 
       a:=a+1;
       next_record;     
  end loop;             --> END LOOP without END IF
  previous_record;
  if a=2 then 
     message('already exist');
     message('already exist');
  else 
     next_record;
  end if;
end if;                 --> where does this END IF belong to?
end;

Re: How to find a duplicate record before commit? [message #316231 is a reply to message #316225] Thu, 24 April 2008 05:56 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
When I enter the block, the whole FORM hangs and stops responding
declare 
a number(1);
f_name varchar2(35);
begin 

f_name:=:agdtet03facprf.preferences;

while :agdtet03facprf.preferences is not null 
	loop
		if f_name=:agdtet03facprf.preferences then 
			a:=a+1;
			next_record;
		end if;
	end loop;
			previous_record;
				if a=2 then 
					message('already exist');
					message('already exist');
				else 
					next_record;
				end if;
end;




Riaz
Re: How to find a duplicate record before commit? [message #316256 is a reply to message #316231] Thu, 24 April 2008 07:06 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The reason it "stopped responding" is called a DEAD LOOP. It is rarely result of Oracle bug, but often result of a bug in developer's code.

That's exactly what you've done - didn't pay attention and WHILE loop never exits. How come? As soon as "preferences" item is different from "f_name" value, NEXT_RECORD is never executed again.

Take piece of a paper, pencil, draw yourself a table (which represents tabular form), enter some values in there and debug your code line by line, value by value. I hope you'll find what was wrong and fix it.
Re: How to find a duplicate record before commit? [message #316366 is a reply to message #316225] Thu, 24 April 2008 16:25 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
sorry dear again little mistake take last one this is correct sorry for cause iam very busy that's why this much hurry


declare
a number(1):=a;
f_name varchar2(35);
begin
f_name:=:asset_master_new.arn_no;
first_record;
while :asset_master_new.arn_no is not null loop
if f_name=:asset_master_new.arn_no then
a:=a+1;
end if; ---here end if will belong sorry for that
next_record;
end loop; --> END LOOP without END IF
previous_record;
if a=2 then
message('already exist');
message('already exist');
else
next_record;
end if;
end;


use this
owais
Re: How to find a duplicate record before commit? [message #316423 is a reply to message #316366] Fri, 25 April 2008 01:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@owais_baba, please, learn how to properly format code (either do it manually by indenting lines, or use an appropriate tool. OraFAQ offers formatter as well.) and enclose it into [code] tags to preserve formatting.

In order to test your skills, go to Test Forum (the last one in the list, at the bottom of the main OraFAQ Forum).

Because, code you post is unreadable.
Re: How to find a duplicate record before commit? [message #316480 is a reply to message #316366] Fri, 25 April 2008 05:34 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
hi dears

we are not here to teach you "ABCD" you have to use yours skills also i am attaching this .fmb file download this form and run and dont say this is not working before i cauld send you this form in my opinion try to explore your self i think you are in administration staff stop criticizing the people like that increase your skills 1st test you self we have been working here since 10 years in all over the world thanks



thanks
owais

Re: How to find a duplicate record before commit? [message #316486 is a reply to message #316480] Fri, 25 April 2008 06:11 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid you completely misunderstood what I was saying.

As far as I'm concerned, you can write your post upside-down if you wish, from right to left or even scrambled. It really doesn't matter, as long as you'll find someone who is going to read and (try to) understand that mess.

Your previous (and, as far as I can remember, ALL of your previous) post is a mess too. It may be clear to you at the moment you write this code. But, as you aren't doing it for your own amusement but to help other Forum member(s), try to understand that reading unformatted code may be extremely difficult; no indentation, which statement belongs to IF-THEN-ELSE, which one is in the loop and which is out ... as I've said - a complete mess.

Besides, try to write your messages using punctuation. It includes "full stop" (a "dot" (.)) sign at the end of each sentence. Your last message was written without it and, honestly, I'm not quite sure what you meant to say and - at the moment - I don't feel like deciphering another mess you produced.

As of criticizing you (or anyone else), you're out of luck - I was doing it, am doing it and will continue to do that any time I feel it should be done. Once again: if someone is illiterate, that's fine with me as long as it doesn't make this Forum look like a mess.

Finally, it's all up to you; if you continue to produce such messages, you'll find yourself ignored by some/many/most members as they will not want to spend their (free and precious) time to something hardly comprehensible.

I appreciate your contribution to the OraFAQ Forum, I really do; if only you could do that additional effort and make your posts easier to read and understand.
Re: How to find a duplicate record before commit? [message #316936 is a reply to message #314823] Mon, 28 April 2008 21:21 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Look at http://www.orafaq.com/forum/m/140853/67467/?srch=duplicate+record_group+david#msg_140853 and consider using the 'record_group' method.

David
Re: How to find a duplicate record before commit? [message #318235 is a reply to message #314823] Tue, 06 May 2008 03:22 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I have the following code in key-next-item trigger.
declare
  a       number (1)    := 0;
  v_prf   varchar2 (35);
  i       number;
begin
  if :agdtet03facprf.preferences between 0 and 6 then   
----- if preference is between 0 and 6 then do following  (1)
----go to first record
    v_prf  := :agdtet03facprf.preferences;
    first_record;
------------check for duplicate preference
    while :agdtet03facprf.preferences is not null
    loop
      ------------dont check for duplicate of 0
      if v_prf = :agdtet03facprf.preferences then
        if v_prf = 0 then
          a  := 0;
        else
          a  := a + 1;
        end if;
      end if;
      next_record;
    end loop;
------message('out of loop. v_prf '||v_prf||'a '||a);
    previous_record;
    if a = 2 then
      message ('Already Exists');
      message ('Already Exists');
      raise form_trigger_failure;   ----------new line
    else
      if :system.last_record != 'TRUE' then
        next_record;
      else
        go_item ('bsd.testcentre');
        raise form_trigger_failure;
      end if;
    end if;
  else   ----- if preference is NOT between 0 and 6 then do following
    message ('Invalid Preference');
    message ('Invalid Preference');
    raise form_trigger_failure;
  end if;   -- end (if)
end;

everything is working fine except when I query the records and enter the first item in agdtet03facprf block and press ENTER KEY the cursor moves directly to TESTCENTRE in another block.

Can any one help me to go to next record instead of going to another block.

sample screen shot is attached please.

Thanks
riaz

Upd mod: Fixed exceptionally crappy code and post formatting
  • Attachment: prf.JPG
    (Size: 11.42KB, Downloaded 134 times)

[Updated on: Tue, 06 May 2008 19:51] by Moderator

Report message to a moderator

Re: How to find a duplicate record before commit? [message #318243 is a reply to message #318235] Tue, 06 May 2008 03:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
press ENTER KEY the cursor moves directly to TESTCENTRE in another block.

Can any one help me to go to next record instead of going to another block.

Check "Navigation Style" block property; try to set it to "Changer Record" (because, perhaps it is now set to "Change Data Block"). Also, check "Previous/Next Navigation Item" item properties and see what's been set.

Or, redefine the KEY-NEXT-ITEM trigger and put something like this in there:
IF :system.last_record = 'FALSE' THEN
   NEXT_ITEM;
ELSE
   NEXT_BLOCK;
END IF;

Re: How to find a duplicate record before commit? [message #318247 is a reply to message #314823] Tue, 06 May 2008 04:06 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I tried both the methods, no one is doing my job.

riaz
Re: How to find a duplicate record before commit? [message #318248 is a reply to message #318247] Tue, 06 May 2008 04:11 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, perhaps I should have been more careful; what happens if you change NEXT_ITEM to NEXT_RECORD?
IF :system.last_record = 'FALSE' THEN
   NEXT_RECORD;
ELSE
   NEXT_BLOCK;
END IF;
Re: How to find a duplicate record before commit? [message #318295 is a reply to message #314823] Tue, 06 May 2008 07:19 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
	[QUOTE]while :agdtet03facprf.preferences is not null 		loop[/QUOTE]


I think the problem is here in the while loop. As it does not see null value when there are six records in the block, it directly goes to go_item.

M I right?

riaz
Re: How to find a duplicate record before commit? [message #318443 is a reply to message #318295] Tue, 06 May 2008 19:53 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Do you have a default value specified for ':agdtet03facprf.preferences'?

David
Re: How to find a duplicate record before commit? [message #318463 is a reply to message #318443] Tue, 06 May 2008 23:01 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
No. default value is specified.
Re: How to find a duplicate record before commit? [message #318514 is a reply to message #318463] Wed, 07 May 2008 01:30 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Perhaps you should have "OR :system.last_record = 'TRUE'" as an extra on the 'while' statement.

David
Re: How to find a duplicate record before commit? [message #318602 is a reply to message #314823] Wed, 07 May 2008 04:45 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
hangs after entering into the block and adding the above statement in WHILE loop
Re: How to find a duplicate record before commit? [message #318763 is a reply to message #318602] Wed, 07 May 2008 21:33 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
On which line? After looping how many times?

David
Previous Topic: FRM-30457
Next Topic: Linking Forms with Finger Scanner
Goto Forum:
  


Current Time: Sat Dec 10 16:28:47 CST 2016

Total time taken to generate the page: 0.12074 seconds