iSeries: Text fields from different records in same file

350405 pts.
Tags:
IBM DB2
IBM iSeries
SQL
We have some data (in a file of course) on iSeries using DB2. Here's the file structure:
Item     Textline    Text

12755    1           this item
12755    2           is no longer
12755    3           for sale
abc123   1           please use
abc123   2           another code
xyz987   1           obsolete
This is the result that we're trying to get:
  Item     Text

   12755    this item is no longer for sale
   abc123   please use another code
   xyz987   obsolete
Basically, we want it grouped by item code and the text lines are added up regardless of how many there are.

We've tried in a query by joining the file to itself 5 times but that didn't work. We next tried it in SQL but we can't get the commands to work correctly with text. What should we do?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta

    When a database has such an ugly structure, it's best to redesign it all. But "best" usually isn't allowed by management. So, second best is to write a simple program function that does the work. But programming requires having programmers, and those who want to access or create the output usually don't have a programmer on demand.

    So, it often comes down to SQL. In this case, "recursive" SQL is what is likely needed.

    A technical description:

    • A recursive query is one that is defined by a Union All with an initialization fullselect that seeds the recursion. The iterative fullselect contains a direct reference to itself in the FROM clause.

    The "initialization fullselect" in this case will grab the first part of a series of "Text" values. That will be UNIONed with the next part of "Text". And it will happen recursively until all parts of one of your full "Text" descriptions are pulled together.

    The Using recursive queries topic in the Information Center shows a number examples. That link is for i 7.1 and includes various new features, but earlier releases also allowed some types of recursive queries. The method that's appropriate for your question is one of the basic methods.

    Play with this query to see how it matches what you need and also to see how it fits with other recursive queries in the above link:

    WITH rquery (Item, Textline, sentence)
    AS
    (
      SELECT base.Item, base.Textline, base.Text 
      FROM SQLEXAMPLE.RECURSE base 
      WHERE Textline = 1
      
      UNION ALL
      
      SELECT t1.Item, t1.Textline, sentence || ' ' || t1.Text
      FROM rquery t0, SQLEXAMPLE.RECURSE t1
      WHERE t0. Item = t1. Item
        AND t0.Textline + 1 = t1.Textline
    )
    SELECT * 
    FROM rquery rq
    WHERE rq.Textline = (SELECT max(Textline) FROM rquery WHERE Item = rq.Item)

    I had to create a table named RECURSE in my SQLEXAMPLE schema, so you'll need to replace those references with your actual names to run any tests. Other names like 'base' and 'rquery' can be whatever you want; they're only used in the query itself.

    Tom



    125,585 pointsBadges:
    report
  • TomLiotta
    And be aware that your definition of "Textline" might not allow for a full result to be built up. You might need to create a temporary column in the base query that is long enough to hold a full Text description. -- Tom
    125,585 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following