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.

How to insert or update the Peoplesoft table by reading the key field from input file

No replies
Guest's picture
Guest

Hi,
I am writing an SQR code to insert /update Peoplesoft Table by reading the key fields from the input file.
I have mailID and phone number,fax in input file.I read the mail ID from file, and get the EmplID from PS_EMPLOYEES table.Now I have to check if the same EMPLID is in PS_PERSONAL_PHONE.If so then I will check mail ID in file and Mail ID in PS_EMPLOYEES table and update phome number in PHONE table, else I will insert the new row in the Phone table.

I have below queries :

1)I have tried implementing using SQR-select.But , mail ID is not getting overriden when it is reading the next record form the file.When the where condtion is met for the first time, it is updating the same phone number for all records in table. How to reset the userdefiend fields and begin-select ?

2)Also my mailID in file is like "John.Henry@company.com,but in the table it is like

.
Will this be checked and updated if the match is found or it is case sensitive?

Here is the SQR code:
Open and Read the file
while 1
READ 1 into $REC_INP:400
If #end-file
CLOSE 1
BREAK
end-if

Do table_update_process
Do table_insert_process

end-while

table_update_process:

begin-sql
UPDATE PS_PERSONAL_PHONE
SET PHONE = $REC_FIELD12_TELEPHONE
WHERE EMPLID IN (SELECT B.EMPLID FROM PS_EMPLOYEES B) AND
B.WB_EMAIL_ADDRESS = $ps_emailid

table_insert_process:

begin-select
PS.EMPLID &EMPID
PS.WB_EMAIL_ADDRESS
from PS_EMPLOYEES PS
WHERE (PS.EMPLID NOT IN (SELECT P.EMPLID FROM PS_PERSONAL_PHONE P) AND
$ps_emailid1 IN (SELECT PS.WB_EMAIL_ADDRESS FROM PS_EMPLOYEES PS))
WITH UR
end-select

begin-sql
insert into PS_PERSONAL_PHONE (
EMPLID
,PHONE_TYPE
,COUNTRY_CODE
,PHONE
)
values
(&EMPID,
'BUSS',
'USA',
$REC_FIELD12_TELEPHONE
)

end-sql

Correct me where am I going wrong in this code.