Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Testing for existence of column following ACROSS

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Testing for existence of column following ACROSS
 Login/Join
 
Platinum Member
posted
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
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Virtuoso
posted Hide Post
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




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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 ...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
quote:
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! Confused

If all else fails I will use this approach.


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Platinum Member
posted Hide Post
My 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
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Virtuoso
posted Hide Post
Mark

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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
quote:
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 Good One


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Expert
posted Hide Post
and 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
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Gold member
posted Hide Post
Have 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
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Testing for existence of column following ACROSS

Copyright © 1996-2020 Information Builders