Need an iSeries SQL that will reduce mutilple entries to one per type

200 pts.
iSeries SQL
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. Thanks Frank

Answer Wiki

Thanks. We'll let you know when a new response is added.

Here is a very rough pass at it. Uses summary functions to bring one copy of folderType from fileA with
one copy of a concat field Policy# and Document. You would have to use a second step to break
the concat field apart.

Select A.folderType, min(concat(b.Policy#, b.Document)) from filea join fileB
on A.Policy# = b.Policy# and a.folderType = b.foldertype
Group by A.folderType;

This would be so….oooo much easier in a procedural language like RPG.

Confidence level in the above code 50%.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
    Thanks for the Help Philip I ended up creating a Stored Procedure to handle the problem.
    200 pointsBadges:
  • WoodEngineer
    Try the "Distrinct" feature on the SQL select statement. Its really cool. I believe it will do what you need with very little effort.
    6,965 pointsBadges:
  • WoodEngineer
    Sorry . . . that should be DISTINCT, not distrinct. Here's a bit of info from the IBM SQL user guide: DISTINCT Eliminates all but one of each set of duplicate rows of the final result table. Two rows are duplicates of one another only if each value in the first row is equal to the corresponding value in the second row. (For determining duplicate rows, two null values are considered equal.) Sort sequence is also used for determining distinct values.
    6,965 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: