Home » SQL & PL/SQL » SQL & PL/SQL » SQL Server code required based on Oracle (Oracle 10g)
SQL Server code required based on Oracle [message #406052] Mon, 01 June 2009 23:51 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi my procedure in oracle is this and can help me for the equivalent sql server

create or replace PROCEDURE             UPDATE_SCREENIMMUNDATA1 AS
          
 CURSOR ezEMRxGroups IS 
    SELECT GROUP_ID FROM EMRGROUPMASTER WHERE STATUS = 1;
    
 CURSOR ezEMRxScreenImmunRecords IS 
    SELECT * FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID = 0 
          ORDER BY SCREEN_IMMUN_ID;
          
  currentGroupID NUMERIC(20,0);
  currentScreenImmunID NUMERIC(20,0);
  currentScreenImmunMasterID NUMERIC(20,0);
  currentScreenImmunRecordType VARCHAR2(2);
  seedScreenImmunID NUMERIC(20,0);
  updateQuery1 VARCHAR2(4000);
  updateQuery2 VARCHAR2(4000);
  insertQuery  VARCHAR2(4000);
  propertyName VARCHAR2(50);
  screenImmunRecord EMRScreenImmunLkup%ROWTYPE;
BEGIN
      --Data Migration for group specific screenings and immunizations records.
    SELECT ezEMRxID INTO currentScreenImmunID FROM EMRIDS WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID';    
    OPEN ezEMRxGroups;
    LOOP
      FETCH ezEMRxGroups INTO currentGroupID;
      EXIT WHEN ezEMRxGroups%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Processing for group - ' || currentGroupID);
      
      OPEN ezEMRxScreenImmunRecords;
      LOOP
          FETCH ezEMRxScreenImmunRecords INTO screenImmunRecord;
          EXIT WHEN ezEMRxScreenImmunRecords%NOTFOUND;          
          DBMS_OUTPUT.PUT_LINE('Processing screening / immunization record, ID - ' || currentScreenImmunMasterID);
          
          --updateQuery1 := 'UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID = ' || currentScreenImmunID || ' WHERE SCREEN_IMMUN_ID = ' || currentScreenImmunMasterID;
          screenImmunRecord.MASTER_REFERENCE_ID := screenImmunRecord.SCREEN_IMMUN_ID;
          screenImmunRecord.SCREEN_IMMUN_ID := currentScreenImmunID;
          screenImmunRecord.GROUP_ID := currentGroupID;
          INSERT INTO EMRSCREENIMMUNLKUP VALUES screenImmunRecord;
          updateQuery1 := 'UPDATE EMRPatientScreeningDetails SET SCREENING_REFERENCE_ID = ' || currentScreenImmunID || ' WHERE PATIENT_ID IN ( SELECT DISTINCT PATIENT_ID FROM EMRPATIENTSMASTER WHERE LOCATION_ID IN ( SELECT DISTINCT LOCATION_ID FROM EMRLOCATIONMASTER WHERE GROUP_ID = ' || currentGroupID || '))';
          updateQuery2 := 'UPDATE EMRPatientImmunizationDetails SET IMMUNIZATION_REFERENCE_ID = ' || currentScreenImmunID || ' WHERE PATIENT_ID IN ( SELECT DISTINCT PATIENT_ID FROM EMRPATIENTSMASTER WHERE LOCATION_ID IN ( SELECT DISTINCT LOCATION_ID FROM EMRLOCATIONMASTER WHERE GROUP_ID = ' || currentGroupID || '))';
          
          execute immediate updateQuery1;
          execute immediate updateQuery2;
          commit;
          
          currentScreenImmunID := currentScreenImmunID + 1;          
      END LOOP;      
      CLOSE ezEMRxScreenImmunRecords;
      
    END LOOP;
    
    CLOSE ezEMRxGroups;
    
    updateQuery1 := 'update emrids set ezemrxid = ' ||  seedScreenImmunID || ' where property_name = ''' || propertyName || '''';
    execute immediate updateQuery1;
    commit;
    
END;
/


[MERGED by LF]

[Updated on: Tue, 02 June 2009 07:52] by Moderator

Report message to a moderator

Re: sql server code required based on oracle [message #406064 is a reply to message #406052] Tue, 02 June 2009 00:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe you should first focus on getting your logic right before coding it in any language/dialect.
currentScreenImmunMasterID never gets a value, so the first occurrence of updateQuery1 sets SCREEN_IMMUN_ID to a constant for every record where SCREEN_IMMUN_ID = NULL. That is: no record.
Then the inner loop: there is nothing in the inner-loop query that depends on the current outer loop record, so basically you are doing the same thing over and over.

Finally: get rid of the dynamic sql. You don't use dynamic column-names or table-names, so you don't need dynamic sql.

And as an encore: Do it in a single sql.
Re: sql server code required based on oracle [message #406065 is a reply to message #406064] Tue, 02 June 2009 00:44 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how can i write then?could you help me out
Re: sql server code required based on oracle [message #406073 is a reply to message #406065] Tue, 02 June 2009 01:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Like I said:
First get your logic right. What do you want to update? What do you want to update it to? Where can you get that info?
Find a mentor in your company, someone that can guide you and help you when you are stuck.
From your last several posts, it looks like you have difficulty unravelling your problem to its bare core. You have difficulties looking at your problem from different angles.
Now, that is something that comes with experience, but a lack of it makes it nearly impossible to use forums, because the only thing you are capable of is repeating what you already said.
Once again, I am not trolling you or criticizing, just giving you a good advice: search a real life coach.
exception in procedure [message #406131 is a reply to message #406052] Tue, 02 June 2009 07:33 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi i am facing this exception but i am able to see the records
create or replace PROCEDURE UPDATE_GROUPSCREENIMMUNDATA AS
          
 CURSOR ezEMRxGroups IS 
    SELECT GROUP_ID FROM EMRGROUPMASTER WHERE STATUS = 1;
    
 CURSOR ezEMRxScreenImmunRecords IS 
    SELECT * FROM EMRSCREENIMMUNLKUP WHERE GROUP_ID = 0 
          ORDER BY SCREEN_IMMUN_ID;
          
  currentGroupID NUMERIC(20,0);
  currentScreenImmunID NUMERIC(20,0);
  currentScreenImmunMasterID NUMERIC(20,0);
  currentScreenImmunRecordType VARCHAR2(2);
  seedScreenImmunID NUMERIC(20,0);
  updateQuery1 VARCHAR2(4000);
  updateQuery2 VARCHAR2(4000);
  insertQuery  VARCHAR2(4000);
  propertyName VARCHAR2(50);
  screenImmunRecord EMRScreenImmunLkup%ROWTYPE;
BEGIN
      --Data Migration for group specific screenings and immunizations records.
    SELECT ezEMRxID INTO currentScreenImmunID FROM EMRIDS WHERE PROPERTY_NAME = 'SCREEN_IMMUN_ID';    
    OPEN ezEMRxGroups;
    LOOP
      FETCH ezEMRxGroups INTO currentGroupID;
      EXIT WHEN ezEMRxGroups%NOTFOUND;
      --DBMS_OUTPUT.PUT_LINE('Processing for group - ' || currentGroupID);
      
      OPEN ezEMRxScreenImmunRecords;
      LOOP
          FETCH ezEMRxScreenImmunRecords INTO screenImmunRecord;
          EXIT WHEN ezEMRxScreenImmunRecords%NOTFOUND;          
          --DBMS_OUTPUT.PUT_LINE('Processing screening / immunization record, ID - ' || currentScreenImmunMasterID);
          
          --updateQuery1 := 'UPDATE EMRSCREENIMMUNLKUP SET SCREEN_IMMUN_ID = ' || currentScreenImmunID || ' WHERE SCREEN_IMMUN_ID = ' || currentScreenImmunMasterID;
          screenImmunRecord.MASTER_REFERENCE_ID := screenImmunRecord.SCREEN_IMMUN_ID;
          screenImmunRecord.SCREEN_IMMUN_ID := currentScreenImmunID;
          screenImmunRecord.GROUP_ID := currentGroupID;
          INSERT INTO EMRSCREENIMMUNLKUP VALUES screenImmunRecord;
          updateQuery1 := 'UPDATE EMRPatientScreeningDetails SET SCREENING_REFERENCE_ID = ' || currentScreenImmunID || ' WHERE SCREENING_REFERENCE_ID =' || screenImmunRecord.MASTER_REFERENCE_ID || ' AND PATIENT_ID IN ( SELECT DISTINCT PATIENT_ID FROM EMRPATIENTSMASTER WHERE LOCATION_ID IN ( SELECT DISTINCT LOCATION_ID FROM EMRLOCATIONMASTER WHERE GROUP_ID = ' || currentGroupID || '))';
          updateQuery2 := 'UPDATE EMRPatientImmunizationDetails SET IMMUNIZATION_REFERENCE_ID = ' || currentScreenImmunID || ' WHERE IMMUNIZATION_REFERENCE_ID =' || screenImmunRecord.MASTER_REFERENCE_ID || ' AND PATIENT_ID IN ( SELECT DISTINCT PATIENT_ID FROM EMRPATIENTSMASTER WHERE LOCATION_ID IN ( SELECT DISTINCT LOCATION_ID FROM EMRLOCATIONMASTER WHERE GROUP_ID = ' || currentGroupID || '))';
          
          execute immediate updateQuery1;
          execute immediate updateQuery2;
          commit;
          
          currentScreenImmunID := currentScreenImmunID + 1;          
      END LOOP;      
      CLOSE ezEMRxScreenImmunRecords;
      
    END LOOP;
    
    CLOSE ezEMRxGroups;
    
    updateQuery1 := 'UPDATE EMRIDS SET EZEMRXID = ' ||  TO_CHAR(seedScreenImmunID) || ' WHERE PROPERTY_NAME = ''' || propertyName || '''';
    execute immediate updateQuery1;
    commit;
    
END;
/

Error starting at line 152 in command:

BEGIN

            UPDATE_GROUPSCREENIMMUNDATA;

END;

Error report:

ORA-00936: missing expression

ORA-06512: at "EZEMRXPRIVATE.UPDATE_GROUPSCREENIMMUNDATA", line 56

ORA-06512: at line 2

00936. 00000 -  "missing expression"

*Cause:    

*Action:



Re: exception in procedure [message #406133 is a reply to message #406131] Tue, 02 June 2009 07:43 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
You would be better served to actually test the value of your dynamic SQL string before you actually try to execute it. Instead of EXECUTE IMMEDIATE, use DBMS_OUTPUT.PUT_LINE. view the string and try to assess what you have done wrong.





Edit: Please also, edit your post so that your code doesn't exceed 80 characters per line (i.e. put returns in if it does)

[Updated on: Tue, 02 June 2009 07:45]

Report message to a moderator

Re: exception in procedure [message #406135 is a reply to message #406131] Tue, 02 June 2009 07:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You really never listen to any of our advice, do you?

Current screenimun_id is still never being initialised
You still don't need to use dynamic Sql.


Have a look in user_source, and show us what lines 50-60 of the procedure are (or reopst the code with line numbers).
Re: exception in procedure [message #406137 is a reply to message #406131] Tue, 02 June 2009 07:57 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think problem is here.

Quote:
updateQuery1 := 'UPDATE EMRIDS SET EZEMRXID = ' || TO_CHAR(seedScreenImmunID) || ' WHERE PROPERTY_NAME = ''' || propertyName || '''';



Single quotes are missing after and before TO_CHAR(seedScreenImmunID) CHAR type value.

regards,
Delna
Previous Topic: Separate Integer and decimal from a number datatype column
Next Topic: update
Goto Forum:
  


Current Time: Sun Dec 11 08:31:49 CST 2016

Total time taken to generate the page: 0.15158 seconds