All Forums Tools
Greyghost 51 posts Joined 07/09
17 Aug 2010
Retain Previous record field value

Hello,

I have a file that contains a Employer record ("M"), followed by multiple Employee records ("B"). There is nothing in the Employee record that ties it to the Employer record other than the fact if follows the Employer record in the text file.

My question:

Is there a way (in Mload, or BTEQ), when I am loading this data when I read the "M" record (employer) I can retain the employer_id and assign it to every "B" (employee) record that follows until I encounter another "M" record? I can do this in SAS and VB, but I would prefer keeping in SQL if possible.

Record Example:

M1234Acme Employer 789 Main Street
BJoe Smith $123.75
BMickey Mouse $534.00
M9873Test Employer.....
B....
B....

Basically, on the first "M" record, I want to carry the 1234 identifier and assign it to the two B records following so when loaded to a table this idenitfier is loaded with the employee detail. The identifier woould be reset whenj the next M record is encountered.

I was thinking some sort of parmeter would do it, but I am not sure how to cod that .

Any ideas?

Thanks!

Paul

enjoycoding 20 posts Joined 08/10
14 Nov 2010

Hi Paul,

You can use inmod facility of fastload/multiload(I did not try inmod on multiload before). Basically when we are using fastload through inmod, when fastload gives a return code of 0 then it means fastload is asking for the first record. This time in the inmod c code you have to open the required file and read first record and store your required number (1234 in the above example) and read second record appended with the stored variable and return it to fastload. This may not be the best way to maintain but i am not aware of any way doing it in plain SQL.

Jimm 298 posts Joined 09/07
15 Nov 2010

Another method commonly used is to pre-process the file using sed/ awk on Unix/ Linux (Sort on mainframe) to take the employer-id from the M record and add it to the following B records.

teradater 29 posts Joined 11/10
25 Nov 2010

do pre-process in SAS :) it is will be most simply

You must sign in to leave a comment.