can someone pls let me know how to convert the below code to set processing one:
Local string &oprid, &emplid, &operpswd, &flag;
Local SQL &SQL1 = CreateSQL("Select OPRID FROM PSOPRDEFN");
&flag = "N";
While (&SQL1.Fetch(&oprid))
rem MessageBox(0, "", 0, 0, "&oprid:-" | &oprid);
SQLExec("select 'Y' from PS_USER where OPRID = :1", &oprid, &flag);
MessageBox(0, "", 0, 0, "&oprid:- " | &oprid | " Flag is " | &flag);
If (&flag = "Y") Then
SQLExec("Select PSOPRDEFN.EMPLID,PSOPRDEFN.OPERPSWD from PSOPRDEFN where OPRID = :1", &oprid, &emplid, &operpswd);
SQLExec("Update PS_USER set EMPLID= :1, OPERPSWD= :2 where OPRID= :3", &emplid, &operpswd, &oprid);
MessageBox(0, "", 0, 0, "****************************Updated**********************************");
Else
MessageBox(0, "", 0, 0, "&oprid:- " | &oprid | &flag);
MessageBox(0, "", 0, 0, "***************************Inserted******************************");
SQLExec("Insert into PS_USER(OPRID, EMPLID, OPERPSWD) (SELECT OPRID,EMPLID,OPERPSWD FROM PSOPRDEFN where oprid = :1 )", &oprid);
rem SQLExec("INSERT INTO PS_USER(OPRID , EMPLID , OPERPSWD) (SELECT OPRID ,EMPLID ,OPERPSWD FROM PSOPRDEFN WHERE oprid NOT IN (SELECT oprid FROM PS_TEST))");
End-If;
&flag = "N";
End-While;
Title | Under | Posted on |
---|---|---|
Component interface Error: no rows exist for the specified keys | PeopleSoft Technical | 03/15/2019 - 3:54am |
ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
You can convert the row by row processing to set processing using the below steps
1st Step for Inserting New OPRID's: SQL Action
INSERT into PS_USER
select
A.OPRID ,
A.EMPLID ,
A.OPERPSWD
FROM PSOPRDEFN A
WHERE A.OPRID NOT IN (SELECT DISTINCT OPRID FROM PS_USER)
2nd Step for Updating EMPLID for Existing OPRID's: SQL Action
UPDATE PS_USER A SET A.EMPLID = (SELECT EMPLID FROM PSOPRDEFN WHERE OPRID = A.OPRID)
WHERE EXISTS(SELECT 'X' FROM PSOPRDEFN WHERE OPRID = A.OPRID)
3rd Step for Updating OPERPSWD for Existing OPRID's: SQL Action
UPDATE PS_USER A SET A.OPERPSWD = (SELECT OPERPSWD FROM PSOPRDEFN WHERE OPRID = A.OPRID)
WHERE EXISTS(SELECT 'X' FROM PSOPRDEFN WHERE OPRID = A.OPRID)
In this way you can process all the rows at once using set processing.. but you will not be able to get the messages for updating and inserting for each and every row as you are processing the OPRID's as a set.
Let me know if you need any further information.