Focal Point
Testing for existence of column following ACROSS
July 22, 2008, 06:55 AM
mark66Testing for existence of column following ACROSS
Hi all,
We have a file that contains comments for orders. There can be many comments for an order and these each have their own record on the file, with their own sequence and related order number.
Example:
Order = 1, Sequence = 1, Comment = ABC
Order = 1, Sequence = 2, Comment = DEF
Order = 1, Sequence = 3, Comment = GHI
Order = 1, Sequence = 4, Comment = JKL
Order = 2, Sequence = 1, Comment = AAA
Order = 2, Sequence = 2, Comment = BBB
I need to report all the comments on ONE line per Order in my report, so:
Order Number, Comment 1, Comment 2, Comment 3, Comment 4.
I have created a summarised Comment file using an ACROSS statement. This gives me 1 record per order, with several Comment columns. I can then print this file using their Alias names:
TABLE FILE
Order
E02
E03
E04
E05
This works fine if the user enquires on Order 1 (with 4 comments), but if they only enquire on Order 2 (with just 2 comments) then columns E04 and E05 won't exist. Is there therefore a way I can check these columns exist before I print them?
Many thanks
Mark
WebFocus 765. iSeries v5r4
July 22, 2008, 07:46 AM
FrankDutchMArk
If I understand this well, you put the result of the across report and use that as the source for your printing report.
Why not print the report direct from the original source something like
TABLE FILE SOURCE
SUM COMMENT
BY ORDER
ACROSS SEQUENCE
WHERE ORDER EQ &ORDER
END
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
July 22, 2008, 07:54 AM
GamPAnother possibility is the use of the variable character option.
Like the next example:
DEFINE FILE MFD
TOTCOMM/A400V = IF ORDERNR EQ LAST ORDERNR THEN TOTCOMM || (', ' | COMMENT) ELSE COMMENT;
END
TABLE FILE MFD
SUM TOTCOMM
BY ORDERNR
END
This will print all order comments on one line, with a separator of ', ' (or any other of your choice).
Hope this helps ...
GamP
- Using AS 8.2.01 on Windows 10 - IE11. |
| in Focus since 1988 |
July 22, 2008, 08:11 AM
mark66quote:
Originally posted by FrankDutch:
MArk
If I understand this well, you put the result of the across report and use that as the source for your printing report.
Why not print the report direct from the original source something like
TABLE FILE SOURCE
SUM COMMENT
BY ORDER
ACROSS SEQUENCE
WHERE ORDER EQ &ORDER
END
Hi Frank, The problem is that this is part of a much larger complex report that retrieves data from many different files. The users can select which columns they want in their reports and we only do the processing required depending on what they have requested. So through the program we create the neccessary hold files and then join them all up at the end on Order Number.
quote:
Originally posted by GamP:
Another possibility is the use of the variable character option.
Like the next example:
DEFINE FILE MFD
TOTCOMM/A400V = IF ORDERNR EQ LAST ORDERNR THEN TOTCOMM || (', ' | COMMENT) ELSE COMMENT;
END
TABLE FILE MFD
SUM TOTCOMM
BY ORDERNR
END
This will print all order comments on one line, with a separator of ', ' (or any other of your choice).
Hope this helps ...
Hi Gamp, This was my first suggestion to the users - to append all the comments into one large text column....but apparently they would prefer to see the Comments seperately and may only request to see 'Comment 3' for example!
If all else fails I will use this approach.
WebFocus 765. iSeries v5r4
July 22, 2008, 09:04 AM
mark66My one thought was to try and add a 'dummy' order with 4 empty comments to the Comments File, with a made up Order number – say 999999. I can force this dummy order into the query by also querying Order 99999 along with what the user is querying. This way the across statement will always create 4 comment columns. Therefore when the user queries an Order that only has 2 comments, this dummy would have been in the file and all 4 columns will exist...
Order = 999999, Sequence = 1, Comment = ‘ ’
Order = 999999, Sequence = 2, Comment = ‘ ‘
Order = 999999, Sequence = 3, Comment = ‘ ‘
Order = 999999, Sequence = 4, Comment = ‘ ‘
The only thing is I can't think how to easily add these dummy records to my Comment file before summarising with the Across?!? Hopefully this is possible?
WebFocus 765. iSeries v5r4
July 22, 2008, 09:05 AM
FrankDutchMark
Is the number of replies per order limited to 5 or 6?
If so you can create some fields static in the define otherwise you should create them on the fly by reading the hold master file.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
July 22, 2008, 09:16 AM
FrankDutchOnly 4...
ok you can do this
TABLE FILE SOURCE
SUM COMMENT
BY ORDER
ACROSS SEQUENCE
COLUMNS 1 AND 2 AND 3 AND 4
END
Now you force the use of 4 columns if it holds data or not.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
July 22, 2008, 09:42 AM
mark66quote:
Originally posted by FrankDutch:
Only 4...
ok you can do this
TABLE FILE SOURCE
SUM COMMENT
BY ORDER
ACROSS SEQUENCE
COLUMNS 1 AND 2 AND 3 AND 4
END
Now you force the use of 4 columns if it holds data or not.
Whoaa! I think that is just what I needed!! It works fine in my small test Fex and I will now into the main program where I am sure it will be good too.
Many thanks all
WebFocus 765. iSeries v5r4
July 22, 2008, 01:51 PM
susannahand there's always this:
for a variable count of acrosses
TABLE FILE CAR BY COUNTRY ON TABLE HOLD
END
-RUN
-SET &howmany = &LINES ;
TABLE FILE CAR
SUM SALES ACROSS COUNTRY
BY CAR
ON TABLE HOLD AS HDATA
END
-RUN
TABLE FILE HDATA
PRINT E01
-* set to how many BY fields you start with
-SET &kounter = 1 ;
-REPEAT endloop &howmany TIMES
-SET &kounter = &kounter + 1 ;
E0&kounter
-endloop
END
if you have more than 9 comments, you gotta start messing with the concatenation.
It becomes E&kounter, not E0&kounter.
| In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
July 23, 2008, 01:01 PM
AlanHave you considered doing a MATCH on the file (you can even MATCH it to itself) which would then produce one output file with all fields rolled up into one row? And it will generate the necessary MFD, as well.
WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL