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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Transpose with Comma

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Transpose with Comma
 Login/Join
 
Silver Member
posted
Hi All,

I know it has been discussed before but after scanning through search results, did not find any solution giving the exact output I am looking for. Hence my question.

I need row values of a column turn to a single column, single row value separated by comma. Before I try this via SQL, hoping there a is a good simple Focus way.

Thanks in advance!

This message has been edited. Last edited by: Tamra,


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report This Post
Expert
posted Hide Post
  
TABLE FILE CAR
PRINT
     COUNTRY
  BY COUNTRY NOPRINT
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READ HOLD1 NOCLOSE &COUNTRY.A10.
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
Or this ...

TABLE FILE CAR
SUM
COMPUTE CNTR/I5 = CNTR + 1;
COUNTRY
BY COUNTRY NOPRINT
ON TABLE HOLD AS HLD01 FORMAT ALPHA
END
-RUN
TABLE FILE HLD01
PRINT
COMPUTE ALL_CNTRY/A300V = IF CNTR EQ 1 THEN COUNTRY ELSE ALL_CNTRY || ', ' | COUNTRY;
BY TOTAL HIGHEST 1 CNTR NOPRINT
END
-RUN


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Guru
posted Hide Post
Or dis....


DEFINE FILE CAR
POINTER/A10 = LAST COUNTRY;
END
TABLE FILE CAR
PRINT
COMPUTE CLIST/A100 = IF POINTER NE '' THEN COUNTRY || ',' ELSE COUNTRY;
ACROSS HIGHEST COUNTRY NOPRINT
END
-RUN


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by Tom Flynn:
  
TABLE FILE CAR
PRINT
     COUNTRY
  BY COUNTRY NOPRINT
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READ HOLD1 NOCLOSE &COUNTRY.A10.
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT

Tom,

I tried this method. It gives me the row values in the continuous format separated by comma. But also has other values/text as shown below. Also the number 000039 is appearing in front of all values. I just want the data like so.
dynamic text place holder for graph J03, dynamic text place holder for graph J06, dynamic text place holder for graph J07

Can you suggest the changes?


 Your request did not return any output to display.
Possible causes:
 - No data rows matched the specified selection criteria.
 - Output was directed to a destination such as a file or printer.
 - An error occurred during the parsing or running of the request. 


--------------------------------------------------------------------------------

 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      3
 000039dynamic text place holder for graph J03,000039dynamic text place holder for graph J06,000039dynamic text place holder for graph J07

 


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report This Post
Virtuoso
posted Hide Post
quote:

Also the number 000039 is appearing in front of all values


Sandeep,

Does the column that you used (the COUNTRY field used by Tom in his sample) is a variable length field (something such as A39V) ?

Tom sample code will work properly with fix length field (A39). The 000039 is the length of the field used on your side.

If you use Tom's sample as below, you will see a 000010 in front of each value:

TABLE FILE CAR
PRINT
     COUNTRY/A10V
  BY COUNTRY NOPRINT
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READ HOLD1 NOCLOSE &COUNTRY.A10.
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by MartinY:
quote:

Also the number 000039 is appearing in front of all values


Sandeep,

Does the column that you used (the COUNTRY field used by Tom in his sample) is a variable length field (something such as A39V) ?

Tom sample code will work properly with fix length field (A39). The 000039 is the length of the field used on your side.

If you use Tom's sample as below, you will see a 000010 in front of each value:

TABLE FILE CAR
PRINT
     COUNTRY/A10V
  BY COUNTRY NOPRINT
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READ HOLD1 NOCLOSE &COUNTRY.A10.
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT


Martin,

the column I am using is A256V in the mfd. I checked the length of the current value in the column. It is 39. Some how that's value that is being written in the output. Not sure why. Can you suggest what changes to make to get the desired output? Thank you.


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report This Post
Virtuoso
posted Hide Post
quote:
the column I am using is A256V in the mfd

If you are using a variable column length (A256V), Tom's technique will display the field length as the first characters before the text value in the output.

The column as to be a fixed value to avoid the display of its length in the result.

Or another solution is to use READFILE instead of READ such as below.
In my sample I change the format of COUNTRY from a fix length of A10 to variable length of A10V (just for purpose of the sample) but the result is properly displayed using READFILE

TABLE FILE CAR
PRINT COUNTRY/A10V
BY COUNTRY NOPRINT
ON TABLE HOLD AS HOLD1
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
This is the method that I adopted from @TonyA

  
-DEFAULTH &VAL_STR = ''

TABLE FILE CAR
  SUM COMPUTE CNTR = LAST CNTR + 1; NOPRINT
      COMPUTE COMMA/A1 = IF CNTR EQ 1 THEN '' ELSE ','; NOPRINT
      COMPUTE VAL_STR/A100V = LAST VAL_STR || COMMA || LJUST(12, COUNTRY, 'A12V');
   BY COUNTRY NOPRINT
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE TEMPHLD1
  SUM LST.VAL_STR
ON TABLE HOLD AS TEMPHLD2
END
-RUN

-READFILE TEMPHLD2
-SET VAL_STR = TRUNCATE(&VAL_STR);
-TYPE &|VAL_STR = &VAL_STR


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Virtuoso
posted Hide Post
Hallway,

The secret is the use of READFILE instead of READ
If you perform the below (your sample with a READ instead of READFILE), your result will show the 000039 at the beginning
Also, no need to specify the TEMPHLD2 format with a READFILE.

-DEFAULTH &VAL_STR = ''

TABLE FILE CAR
  SUM COMPUTE CNTR = LAST CNTR + 1; NOPRINT
      COMPUTE COMMA/A1 = IF CNTR EQ 1 THEN '' ELSE ','; NOPRINT
      COMPUTE VAL_STR/A100V = LAST VAL_STR || COMMA || LJUST(12, COUNTRY, 'A12V');
   BY COUNTRY NOPRINT
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE TEMPHLD1
  SUM LST.VAL_STR
ON TABLE HOLD AS TEMPHLD2 FORMAT ALPHA
END
-RUN

-READ TEMPHLD2 &VAL_STR.A100
-SET &VAL_STR = TRUNCATE(&VAL_STR);
-TYPE &|VAL_STR = &VAL_STR


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
Martin, Hallway,

I tried both of your versions. Martin's code does not give me any output. Hallway's code shows the values but still have the format length in front of it. I have pasted both the codes below and also the output.

Martin's Code

 
TABLE FILE MFD
PRINT
     GRAPH_COMMENTS/A256V
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1 
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-*ON TABLE HOLD AS HOLD2 FORMAT FLEX
-EXIT 


Output:
 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      3
 ,,

 


Hallway's Code

 -DEFAULTH &VAL_STR = ''

TABLE FILE MFD
  SUM COMPUTE CNTR = LAST CNTR + 1; NOPRINT
      COMPUTE COMMA/A1 = IF CNTR EQ 1 THEN '' ELSE ','; NOPRINT
      COMPUTE VAL_STR/A256V = LAST VAL_STR || COMMA || LJUST(256, GRAPH_COMMENTS, 'A256V');
   BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );   
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE TEMPHLD1
  SUM LST.VAL_STR
ON TABLE HOLD AS TEMPHLD2 FORMAT ALPHA
END
-RUN

-READ TEMPHLD2 &VAL_STR.A256
-SET &VAL_STR = TRUNCATE(&VAL_STR);
-TYPE &|VAL_STR = &VAL_STR

-EXIT 


Output:
 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      3
 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      1
 &VAL_STR = 000256dynamic text place holder for graph J03,dynamic text place holder for graph J06,dynamic text place holder for graph J07

 



The output I am looking for is just the text.

Desired Output:
 dynamic text place holder for graph J03,dynamic text place holder for graph J06,dynamic text place holder for graph J07 


Can you please suggest the changes in the code? Thanks.


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report This Post
Virtuoso
posted Hide Post
Change this line of code from.


COMPUTE VAL_STR/A256V = LAST VAL_STR || COMMA || LJUST(256, GRAPH_COMMENTS, 'A256V');


to


COMPUTE VAL_STR/A256 = LAST VAL_STR || COMMA || LJUST(256, GRAPH_COMMENTS, 'A256');


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2128 | Location: Customer Support | Registered: April 12, 2005Report This Post
Guru
posted Hide Post
or don't use format ALPHA with Hallway's Code:

TABLE FILE TEMPHLD1
SUM LST.VAL_STR
ON TABLE HOLD AS TEMPHLD2
END
-RUN

-READFILE TEMPHLD2
-SET VAL_STR = TRUNCATE(&VAL_STR);
-TYPE &|VAL_STR = &VAL_STR

Alternativly you can use Frans' solution Wink difference is that this is done in one TABLE request and using comma delimited output to put the comma's:

FILEDEF HOLD DISK hold.csv (LRECL 200 RECFM v
TABLE FILE CAR
SUM MAX.COUNTRY
ACROSS COUNTRY NOPRINT
ON TABLE HOLD FORMAT COM
END
-RUN

-READ HOLD &VAL_STR.A200.
-TYPE &VAL_STR

-*if you don't need the double quotes:
-SET &VAL_STRING_STRIPPED = STRIP(&VAL_STR.LENGTH, &VAL_STR.QUOTEDSTRING, '"', 'A&VAL_STR.LENGTH');
-TYPE &VAL_STRING_STRIPPED
-EXIT


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Virtuoso
posted Hide Post
quote:
TABLE FILE MFD
PRINT
GRAPH_COMMENTS/A256V
BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
( MFD.MFD.GRAPH_NO EQ '1.1' );
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END...


Must be
TABLE FILE MFD
PRINT
     GRAPH_COMMENTS
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE HOLD AS HOLD1
END
...


If GRAPH_COMMENTS is already a fix length don't change it to a variable length. And if it's a variable length, no need to change it again in variable length.
In my sample I have changed the format of COUNTRY to A10V because IB sample files don't have variable length.

With READFILE you don't need to reformat the field at all. READFILE will manage the length.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
quote:

If GRAPH_COMMENTS is already a fix length don't change it to a variable length. And if it's a variable length, no need to change it again in variable length.
In my sample I have changed the format of COUNTRY to A10V because IB sample files don't have variable length.

With READFILE you don't need to reformat the field at all. READFILE will manage the length.



Martin,
The graph_comments column is a variable length column.

I used your original code but it is not retuning any data. It only gives output like I had posted in above reply. No values appearing only the commas. What do you suggest. Also in the response I only need the text as output. How do I get rid of the system generated text. Please see the output I had pasted above. Thank you.
 TABLE FILE MFD
PRINT
     GRAPH_COMMENTS
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE HOLD AS HOLD1 
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1 
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT 


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report This Post
Silver Member
posted Hide Post
quote:


Alternatively you can use Frans' solution Wink difference is that this is done in one TABLE request and using comma delimited output to put the comma's:


Frans,

I tried your steps. It gives the values but I get it in column format. I needed the values as continuous text separated by comma.
Here is the code I used. How do I change it to only text format.
 FILEDEF HOLD DISK hold.csv
TABLE FILE MFD
SUM
     MAX.MFD.MFD.GRAPH_COMMENTS
ACROSS MFD.MFD.GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END
-RUN

-READ HOLD &VAL_STR.A256.
-TYPE &VAL_STR
-SET &VAL_STRING_STRIPPED = STRIP(&VAL_STR.LENGTH, &VAL_STR.QUOTEDSTRING, '"', 'A&VAL_STR.LENGTH');
-TYPE &VAL_STRING_STRIPPED
-EXIT
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDA/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
     BORDER=OFF,
$ 


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report This Post
Expert
posted Hide Post
Too many cooks in the kitchen, although, you've been shown the many ways to climb the WebFOCUS mountain...

TABLE FILE MFD
PRINT
     GRAPH_COMMENTS/A256
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS HOLD1 
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &GRAPH_COMMENTS_OUT = '';
-SET &GRAPH_COMMENTS     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1 
-SET &CNTR = &CNTR + 1;
-SET &GRAPH_COMMENTS_OUT = IF &CNTR EQ 1 THEN &GRAPH_COMMENTS || ',' ELSE 
-                          IF &CNTR EQ &XLINES THEN &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS ELSE &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS  || ',';
-GET_DATA
-TYPE &GRAPH_COMMENTS_OUT
-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by Tom Flynn:
Too many cooks in the kitchen, although, you've been shown the many ways to climb the WebFOCUS mountain...


Tom,

I am thankful to all the great cooks for helping me out in preparing the webfocus soufflé Smiler. Have learned a lot. I tried your modified code and I am almost there. The values are coming correctly as continuous text with comma. But the output also comes with the below shown text. How to get rid of it and have just the values? Can you please suggest? Thanks.
 Your request did not return any output to display.
Possible causes:
 - No data rows matched the specified selection criteria.
 - Output was directed to a destination such as a file or printer.
 - An error occurred during the parsing or running of the request. 


--------------------------------------------------------------------------------

 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      3
 


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report This Post
Guru
posted Hide Post
quote:
Originally posted by Sandeep:
quote:


Alternatively you can use Frans' solution Wink difference is that this is done in one TABLE request and using comma delimited output to put the comma's:


Frans,

I tried your steps. It gives the values but I get it in column format. I needed the values as continuous text separated by comma.
Here is the code I used. How do I change it to only text format.


Because you used it a bit differently, here is how it works:

 FILEDEF HOLD DISK hold.csv (LRECL 200 RECFM v
TABLE FILE MFD
SUM
     MAX.MFD.MFD.GRAPH_COMMENTS
ACROSS MFD.MFD.GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
ON TABLE HOLD FORMAT COM
END
-RUN

-READ HOLD &VAL_STR.A256.
-TYPE &VAL_STR
-SET &VAL_STRING_STRIPPED = STRIP(&VAL_STR.LENGTH, &VAL_STR.QUOTEDSTRING, '"', 'A&VAL_STR.LENGTH');
-TYPE &VAL_STRING_STRIPPED
-EXIT


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Virtuoso
posted Hide Post
quote:

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------

0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3


As far as I know, the above is not an error. It just telling you that you are not processing any displayable report since in your case, reason #2 is the cause.
It is just an warning.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Expert
posted Hide Post
You now have the data in an ampere variable - &GRAPH_COMMENTS_OUT
Do with it what you will...

You have no output, hence, the message...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Silver Member
posted Hide Post
Martin,

I agree, this is not an error. I assume it shows when the output is not coming from TABLE or a GRAPH. How do i get the values from &VAL_STRING_STRIPPED only?


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by Tom Flynn:
You now have the data in an ampere variable - &GRAPH_COMMENTS_OUT
Do with it what you will...

Place this at the top of the program:
SET MSG = OFF


Tom,

Just saw your message. Let me try. Will update.


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report This Post
Expert
posted Hide Post
Doesn't stop the message, you have no output. Continue with the program and use the variable for whatever you're doing...

Good Luck!


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Silver Member
posted Hide Post
Thank you Everyone for the help. I got the output I was looking for. Here is the code just in case someone else runs in to the same requirement.

 TABLE FILE MFD
PRINT
     GRAPH_COMMENTS/A256
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS HOLD1 
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &GRAPH_COMMENTS_OUT = '';
-SET &GRAPH_COMMENTS     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1 
-SET &CNTR = &CNTR + 1;
-SET &GRAPH_COMMENTS_OUT = IF &CNTR EQ 1 THEN &GRAPH_COMMENTS || ',' ELSE 
-                          IF &CNTR EQ &XLINES THEN &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS ELSE &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS  || ',';
-GET_DATA
-TYPE &GRAPH_COMMENTS_OUT
-EXITTABLE FILE MFD
PRINT
     GRAPH_COMMENTS/A256
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &GRAPH_COMMENTS_OUT = '';
-SET &GRAPH_COMMENTS     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1
-SET &CNTR = &CNTR + 1;
-SET &GRAPH_COMMENTS_OUT = IF &CNTR EQ 1 THEN &GRAPH_COMMENTS || ',' ELSE
-                          IF &CNTR EQ &XLINES THEN &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS ELSE &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS  || ',';
-GET_DATA
-TYPE &GRAPH_COMMENTS_OUT


TABLE FILE MFD
PRINT
GRAPH_COMMENTS NOPRINT
ON TABLE SUBHEAD
"&GRAPH_COMMENTS_OUT"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDA/_EDAHOME/ETC/endeflt.sty,
$
TYPE=TABHEADING,
     LINE=1,
     JUSTIFY=LEFT,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     FONT='CALIBRI',
     SIZE=11,
     COLOR=RGB(157 34 53),
     STYLE=BOLD,
$
END
-RUN 


WebFocus 8.1 Service Pack 0.5
Build Version branch8105
Build/GEN Number 119

OS/Platform: Apache Tomcat/8.0.21
 
Posts: 46 | Location: Baltimore | Registered: October 27, 2016Report 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     [SOLVED]Transpose with Comma

Copyright © 1996-2020 Information Builders