Ask Question
Help Others
Home › Forums › PeopleSoft › PeopleSoft Technical

You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

set processing

1 reply [Last post]
Thu, 05/03/2012 - 11:03pm
Guest's picture
Guest

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;

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Top
  • Login to post comments
  • 2753 reads
Fri, 05/04/2012 - 1:08am
#1
charan4ever's picture
charan4ever (not verified)
Re: set processing

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.

Top