5 Replies Latest reply on Dec 14, 2012 2:37 PM by OsamaJweihan


      db and dev 10g rel2 , xp sp3
      hi all ,
      i've a "patients" block of type "form" with columns (pat_id "primary key" , pat_name, test,....).

      - my application inserts patients every day , so the pat_id can reach to "2000" or 20000 or even 200,000 one day" ,
      so i do not see that it's appropriate for the user to see such these big-sized numbers in the "pat_id" field .

      so i am thinking of creating a control block's field , to show the patients' id's one by one automatically ,each day individually ,
      i mean that ,
      today 13/12/2012 , the first patient comes , so the new field will hold "1" , then the second patients comes , so
      the field holds "2" , and so on , untill the next day comes , the first patient of the next day comes , so the field holds
      "1" , then the second patient comes , the field holds "2" and so on again .
      - is there a sequence i can create , and ends on the next day ?

      and of course i should insert a value into the pat_id column in the database , because it is a primary key column ,
      but how ?
        • 1. Re: sequences
          Pradhyumn (PS)
          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
          • 2. Re: sequences
            Alok Dubey
            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.

            Alok Dubey
            • 3. Re: sequences
              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 ?

              • 4. Re: sequences
                Andreas Weiden

                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?
                • 5. Re: sequences
                  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).
                  Have a question/reply, contact me at Osama_979@hotmail.com (I check my email more than OTN account).
                  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? ...?

                  Edited by: OsamaJweihan on Dec 14, 2012 6:31 AM