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.
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.
M1234Acme Employer 789 Main Street
BJoe Smith $123.75
BMickey Mouse $534.00
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 .
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.
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.
do pre-process in SAS :) it is will be most simply