I need to produce an extract that is a combination of two files. File A has a policy number and a folder type. File B has the document number. File C will be the product of File A and File B.
I can join the files on the policy number and folder type to get the document number, however, I need to only extract each folder type once. In other words, my extract must contain one of each folder type in File A only. The policy number and document number themselves are not important at this point as long as I have a Policy and Document number associated to the Folder Type in File C.
Any type of distinct or group clause, I can think of, will not work because the results will either be no policy and a folder type or a folder type with multiple policies. The one idea I did have was to layout the SQL so that it will check if the folder type is already in the File C before writing a record to File C.
If anyone has any suggestions, they would be most appreciated.