Need help with a loop
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Need help with a loop

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    Need help with a loop

    In a 10g Db I have these 3 tables Appoint, ptprod and producer. The appoint tbl has all patients appointments and the producer table holds all the providers we have, and the ptprod tbl holds the patient and the provider the patient is assigned to. I need to write something that looks to see if a patient has an apppointment in a certian clinic for the day and if so assign them to certian providers. I need a loop because there could be more then one patient and there are several providers available. I am kind of stuck at were to start.

    Code:
    select "Patient" from appoint where "Clinic"='760' and "Date"=trunc(sysdate)
    shows me all the patients that have an appointment that day,
    and
    Code:
    select "Producer" from producer where "PGroup"='SED' and "Inactive"='0'
    returns the provider numbers for all available providers
    and
    Code:
    INSERT INTO ptprod ("Patient", "Producer", "StartDate", "EndDate",
              "IsRestricted", "IsPrimary", "Discipline", "NeedReq", "Id",
        "CreateDate", "User", "StationId", "Deleted", "Note")
      VALUES ( '783968', 'Z8922', trunc(sysdate), trunc(sysdate +5),0, 0, '', 0,
        PTPROD_ID_SEQ.NEXTVAL, TRUNC(SYSDATE), '20240', '1458', 0, ' ')
    is the insert statement I need to do for each patient and for each provider. this is just one example from one of the patients for one of the providers. I need to do the above insert several times for this one patient for each provider then again for a differnet patient.

    I was thinking the loop would do this, but I am stuck at how to begin. I searching the web for syntax and examples but I thought I would through my question out here. any help would be greatly appreciated.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    i'm probably a little slow today but I don't totally understand how Providers are assigned to specific Appointments.

    Either way - if a loop is really needed - I would probably load all appointments into a cursor then loop the cursor assigning Providers as needed.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2005
    Posts
    143
    PAVB,
    thanks so much for the reply. you got me started and I really appreciate it. You're not slow I must not have expained myself well. the producer doesn't need to be assigned to appoinments but to the patients that have the appointment. either way this is the code I wrote
    Code:
    declare 
    prod1 CHAR(10 Byte) ;
    prod2 CHAR(10 Byte) ;
    cursor pat_cursor is
        select "Patient" from appoint where "Clinic"='760' and "Date"=trunc(sysdate);
    pat_val pat_cursor%ROWTYPE;
    begin
    prod1 := 'Z8922';
    prod2 := 'Z8970';
    open pat_cursor;
    loop
    fetch pat_cursor into pat_val;
    exit when pat_cursor%NOTFOUND;
    INSERT INTO ptprod  VALUES ( pat_val."Patient", prod1, trunc(sysdate), trunc(sysdate +5),0, 0, '', 0,
        PTPROD_ID_SEQ.NEXTVAL, TRUNC(SYSDATE), '20240', '1458', 0, ' ');
    INSERT INTO ptprod  VALUES ( pat_val."Patient", prod2, trunc(sysdate), trunc(sysdate +5),0, 0, '', 0,
        PTPROD_ID_SEQ.NEXTVAL, TRUNC(SYSDATE), '20240', '1458', 0, ' ');    
        end loop;
    close pat_cursor;
    end;
    /
    I have 2 issues with the code though.
    1- it is only working for one patient and it should be working for 2 patients. the loop doesn't seem to be working.
    2- I have hard coded two producers (Z8922 and Z8970). this works for now however the providers change every year and I don't want to keep changing the code as we get new doctors. do I need a second cursor for the producers?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    There is an issue with the LOOP statement, you may want to use it with a WHILE and also include and END LOOP statement to close the loop - like...

    Code:
    WHILE condition LOOP 
       statement1; 
       statement2; 
       ... 
    END LOOP;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Mar 2005
    Posts
    143
    PAVB,
    once again I really appreciate the help. So I took your suggestion and worked out code using a while loop. Ran it, and when I checked it was still only updating one patient. I said this is crazy. turns out the way I was verifing if the code worked was selecting from the table I was updating, where the patient was the 2 patient #'s that should be getting updated. well upon further review I realized I had a typo in one of the patient numbers. It did update both patients. I re-ran my original code to see If my original code worked and that worked to. I feel bad I didn't catch this sooner. Sorry I wasted your time. However I have it with the providers hard coded and I don't want it like that. I still want to modify it to loop through the providers, so I will keep tweaking it. Thanks and sorry again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width