×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to reorganize a table?

How to reorganize a table?

How to reorganize a table?

(OP)
Hi folks!

I have a db2 table as source for my webfocus report. Unfortunately the table's organisation does not really fit with what I have to display. I have to order data by fields that do not exist and cannot be created by DEFINE. I figured a way out but don't know how to do it.

Here's the example. It is about telephone calls. The db2 table looks like this:

company  weekday  answered_calls  not_answered_calls
-------  -------  --------------  ------------------
a         Mon        987              123
b         Tue       1034               50
a         Thu        543              100

Yet the table should look like this so that I can make my report:

company  weekday  number  type_of_call
-------  -------  ------  ------------
a         Mon       987     answered
a         Mon       123     not_answered
b         Tue      1034     anserwed
b         Tue        50     not_answered
a         Thu       543     answered
a         Thu       100     not_answered

Can anybody help?
Thanx
Eva

RE: How to reorganize a table?

Hi Eva,

How about this:

DEFINE FILE filename
  ANSCALLS/I9  = ANSWERED_CALLS;
  ANSWERED/A20 = 'answered';
  NOT_ANSCALLS/I9  = NOT_ANSWERED_CALLS;
  NOT_ANSWERED/A20 = 'not_answered';
END

MATCH FILE filename
  SUM   ANSCALLS
     BY COMPANY
     BY WEEKDAY
     BY ANSWERED AS TYPE_OF_CALL
RUN
      FILE filename
  SUM   NOT_ANSCALLS
     BY COMPANY
     BY WEEKDAY
     BY NOT_ANSWERED AS TYPE_OF_CALL
  AFTER MATCH HOLD OLD-AND-NEW
END

DEFINE FILE HOLD
  NUMBER/I9 = IF TYPE_OF_CALL EQ 'answered'
              THEN ANSCALLS
              ELSE NOT_ANSCALLS;
END

TABLE FILE HOLD
  PRINT NUMBER
        TYPE_OF_CALL
     BY COMPANY
     BY WEEKDAY
     BY TYPE_OF_CALL NOPRINT
END

This should do the trick. Good luck!

Frank van de Kant
http://www.explicit.nl

RE: How to reorganize a table?

(OP)
Hello Frank,

Thanks a lot. It works! Now I even have a better understanding of what MATCH does.

Yet there is one mistake in your MATCH statement. It should be OLD-OR-NEW not OLD-AND-NEW.

Best wishes
Eva

RE: How to reorganize a table?

(OP)
Hi Frank,

I'd like to come back to this thread on which we communicated some time ago. The report works fine with this MATCH method. I even used this trick to make a column title which streches across four columns (these I created with ACROSS in the actual report). Works all fine with HTML output. But with PDF output this is what happens: the columns title which should be across four other columns is only above the first ACROSS column. As a result the page width is too small and the report is shown on two pages which I have to print and lay beside eacht other.

Is there a way to prevent this? To make the column title strech across 4 columns in the PDF output as well?

Best wishes
Eva

RE: How to reorganize a table?

Another option to consider, is to treat the data as if it was in an OCCURS segment.

Your layout looks like this:

company  weekday  answered_calls  not_answered_calls

So, if you treat the fields answered_calls and not_answered_calls as if they were 2 instances of a field in its own segment, which occurs twice (you do this with an additional segment in the Master, and the keyword POSITION to reposition you back at those fields), you can use the artificial field ORDER to number the instances 1 and 2. Now it becomes a single pass of the data, and very straight-forward, with 1 decoding to 'answered', and 2 decoding to 'not answered'

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close