This content has been marked as final. Show 5 replies
Add a column : Creation_Date date(in your table)
Create a composite key on (pat_id and creation_date) and reset it by trunc(creation_date).
Edited by: PS on Dec 13, 2012 9:42 AM
As PS has suggested, u need to change the primary key to a combination of columns. it can be either with the combination of Creationdt and patient id.
I was curious for knowing the reason for creation of this type of combination. for ex: consider a case ,where in the patient has visited on 10 dec,2012 and a patient id has been created. if he revisits the hospital on 12 dec,2012 are you trying to day that again his patient id is created?
i suggest to keep the patient id master and then a Visit transctions forms.
In the visit transaction form, we need to generate a visiting number, which checks what is the max visiting number generated on this particuar date and increment it by 1.
The base table of transaction form will have the combination of the Transactiondt and visiting number as the primary key.
thanks to you all ,
i already have a "test_date" field based on a "test_date" column .
what about createing a non database field "c_pat_id" , and remove the "pat_id" field , then create a "when new form instance" trigger , then write this :
c_pat_id := 1 .
then at the end of the "insert button" code -- it is a button to insert the patients data ,
i'll write c_pat_id := c_pat_id +1 .
and in a "pre_insert" trigger , "insert into pat.pat_id values (pat_patid_seq.nextval)"
is this a good solution , or not ?
first, what happens when two users use the form separately? Or when the user exits the form and reopens it?
What exactly do you need the number for?
Do what PS said about primary key, then make a db procedure with dynamic SQL to reset the sequence to 1 , and ask your DBA to schedule a run of the procedure every day then the sequence will start at 1 every day and no conflicts in primary key or users input will happen.
that could solve the problem, but what do you want the ID for(if for number of patients for a day, and the order of who came first this will solve it).
Please supply us with more information about insertion/querys , Will pat_id be used as ID for a Query or for a patient? Is the problem all about first patient, second, third...? is it a hospital/clinic with more than one section? ...?
Have a question/reply, contact me at Osama_979@hotmail.com (I check my email more than OTN account).
Edited by: OsamaJweihan on Dec 14, 2012 6:31 AM