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     SORTED by own order problem..

Read-Only Read-Only Topic
Go
Search
Notify
Tools
SORTED by own order problem..
 Login/Join
 
jackyzsn
Member
posted
Hi,

I want to sorted a field by my own order, but when I use ROWS OVER, it gives me the following error:

If I take out ROWS OVER, it runs fine..
  
TABLE FILE A1CHQ                                              
PRINT                                                         
CHQ_AMT/D17.2 AS 'CHEQUE AMOUNT'                              
CHQ_ID AS 'REFERENCE NUMBER'                                  
CHQ_REF_ID AS 'POL/,CLIENT ID'                                
CHQ_MICR_NUM AS 'CHEQUE,MICRO,NUMBER'                         
CHQ_DESC_LN_1_TXT/A50 AS 'CHEQUE DESCRIPTION'                 
CHQ_PAYE_1_TXT/A35 AS 'PAYEE'                                 
BY HIGHEST CHQ_STATUS NOPRINT                                 
   ROWS 'PRINTED' OVER 'CANCELLED' OVER 'VOID' OVER           
        'STOPPED' OVER 'STALEDATED'                           
BY HIGHEST PRT_ORDER NOPRINT                                  
   SUBTOTAL AS 'CHEQUE SUBTOTAL'                              
BY HIGHEST PRT_STATUS AS 'CHEQUE,STATUS'                      
BY HIGHEST CHQ_DT AS 'CHEQUE,ISSUE DATE'                      
BY HIGHEST CHQ_PRCES_DT AS 'PROCESS   ,DATE'                  
ON CHQ_STATUS PAGE-BREAK                                      
ON CHQ_STATUS SUBFOOT                                         
ERROR AT OR NEAR LINE    200  IN PROCEDURE INPUT   FOCEXEC    
FOC013) THE 'ON FIELDNAME' FIELD IS NOT A SORT FIELD: SUBFOOT 
BYPASSING TO END OF COMMAND                                   
"<CHQ_STATUS CHEQUE TOTAL: <37 <ST.CHEQUE_AMT"                
HEADING                                                       
"<STR_MMX CHEQUE REGISTER (J9817)                             
 <70 DATE: AUGUST 20, 2007    <98 TIME: <104 17.06.00 </1"    
ON TABLE NOTOTAL                                              
MORE                                                          
FILE B1CHQ                                                    
END                                                           
FOC009) INCOMPLETE REQUEST STATEMENT


Regards,

Jacky
 
Posts: 20 | Location: Toronto | Registered: August 13, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Barring a better solution, you may be able to get it to work by adding another BY statement (and you don't need HIGHEST since you're specifying the order):

TABLE FILE A1CHQ                                              
PRINT                                                         
CHQ_AMT/D17.2 AS 'CHEQUE AMOUNT'                              
CHQ_ID AS 'REFERENCE NUMBER'                                  
CHQ_REF_ID AS 'POL/,CLIENT ID'                                
CHQ_MICR_NUM AS 'CHEQUE,MICRO,NUMBER'                         
CHQ_DESC_LN_1_TXT/A50 AS 'CHEQUE DESCRIPTION'                 
CHQ_PAYE_1_TXT/A35 AS 'PAYEE'                                 
BY CHQ_STATUS NOPRINT                                 
   ROWS 'PRINTED' OVER 'CANCELLED' OVER 'VOID' OVER           
        'STOPPED' OVER 'STALEDATED' 

BY CHQ_STATUS NOPRINT    
                      
BY HIGHEST PRT_ORDER NOPRINT                                  
   SUBTOTAL AS 'CHEQUE SUBTOTAL'                              
BY HIGHEST PRT_STATUS AS 'CHEQUE,STATUS'                      
BY HIGHEST CHQ_DT AS 'CHEQUE,ISSUE DATE'                      
BY HIGHEST CHQ_PRCES_DT AS 'PROCESS   ,DATE'                  
ON CHQ_STATUS PAGE-BREAK                                      
ON CHQ_STATUS SUBFOOT                                         
"<CHQ_STATUS CHEQUE TOTAL: <37 <ST.CHEQUE_AMT"                
HEADING                                                       
"<STR_MMX CHEQUE REGISTER (J9817)                             
 <70 DATE: AUGUST 20, 2007    <98 TIME: <104 17.06.00 </1"    
ON TABLE NOTOTAL                                              
MORE                                                          
FILE B1CHQ                                                    
END

The problem is occurring because the ROWS ... OVER is FML code, and that doesn't behave as expected.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
jackyzsn
Member
posted Hide Post
Hi Francis,

I tried, still error:

  
TABLE FILE A1CHQ                                                   
PRINT                                                              
CHQ_AMT/D17.2 AS 'CHEQUE AMOUNT'                                   
CHQ_ID AS 'REFERENCE NUMBER'                                       
CHQ_REF_ID AS 'POL/,CLIENT ID'                                     
CHQ_MICR_NUM AS 'CHEQUE,MICRO,NUMBER'                              
CHQ_DESC_LN_1_TXT/A50 AS 'CHEQUE DESCRIPTION'                      
CHQ_PAYE_1_TXT/A35 AS 'PAYEE'                                      
BY CHQ_STATUS NOPRINT                                              
   ROWS 'PRINTED' OVER 'CANCELLED' OVER 'VOID' OVER                
        'STOPPED' OVER 'STALEDATED'                                
BY CHQ_STATUS NOPRINT                                              
BY HIGHEST PRT_ORDER NOPRINT                                       
   SUBTOTAL AS 'CHEQUE SUBTOTAL'                                   
BY HIGHEST PRT_STATUS AS 'CHEQUE,STATUS'                           
BY HIGHEST CHQ_DT AS 'CHEQUE,ISSUE DATE'                           
BY HIGHEST CHQ_PRCES_DT AS 'PROCESS   ,DATE'                       
ON CHQ_STATUS PAGE-BREAK                                           
ON CHQ_STATUS SUBFOOT                                              
"<CHQ_STATUS CHEQUE TOTAL: <37 <ST.CHEQUE_AMT"                     
HEADING                                                            
"<STR_MMX CHEQUE REGISTER (J9817)                                  
 <70 DATE: AUGUST 20, 2007    <98 TIME: <104 17.29.23 </1"         
ON TABLE NOTOTAL                                                   
MORE                                                               
ERROR AT OR NEAR LINE    208  IN PROCEDURE INPUT   FOCEXEC         
FOC32423) MORE WITHIN AN FML REQUEST IS NOT SUPPORTED              
BYPASSING TO END OF COMMAND                                        
FILE B1CHQ                                                         
END


Regards,

Jacky
 
Posts: 20 | Location: Toronto | Registered: August 13, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Still error... but different error...

Like I said, FML doesn't behave as expected. You will have to merge the two files before the final report.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
jackyzsn
Member
posted Hide Post
sorry, my bad, should be another field.. 

  
TABLE FILE A1CHQ                                              
PRINT                                                         
CHQ_AMT/D17.2 AS 'CHEQUE AMOUNT'                              
CHQ_ID AS 'REFERENCE NUMBER'                                  
CHQ_REF_ID AS 'POL/,CLIENT ID'                                
CHQ_MICR_NUM AS 'CHEQUE,MICRO,NUMBER'                         
CHQ_DESC_LN_1_TXT/A50 AS 'CHEQUE DESCRIPTION'                 
CHQ_PAYE_1_TXT/A35 AS 'PAYEE'                                 
BY HIGHEST CHQ_STATUS NOPRINT                                 
BY HIGHEST PRT_ORDER NOPRINT                                  
   SUBTOTAL AS 'CHEQUE SUBTOTAL'                              
   ROWS 'PRINTED' OVER 'CANCELLED' OVER 'VOID' OVER           
        'STOPPED' OVER 'STALEDATED'                           
BY HIGHEST PRT_STATUS AS 'CHEQUE,STATUS'                      
BY HIGHEST CHQ_DT AS 'CHEQUE,ISSUE DATE'                      
BY HIGHEST CHQ_PRCES_DT AS 'PROCESS   ,DATE'                  
ON CHQ_STATUS PAGE-BREAK                                      
ON CHQ_STATUS SUBFOOT                                         
"<CHQ_STATUS CHEQUE TOTAL: <37 <ST.CHEQUE_AMT"                
HEADING                                                       
"<STR_MMX CHEQUE REGISTER (J9817)                             
 <70 DATE: AUGUST 20, 2007    <98 TIME: <104 17.41.39 </1"    
ON TABLE NOTOTAL                                              
MORE                                                          
ERROR AT OR NEAR LINE    207  IN PROCEDURE INPUT   FOCEXEC    
FOC32423) MORE WITHIN AN FML REQUEST IS NOT SUPPORTED         
BYPASSING TO END OF COMMAND                                   
FILE B1CHQ                                                    
END


What is FML request?


Regards,

Jacky
 
Posts: 20 | Location: Toronto | Registered: August 13, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Try to solve the MORE problem first.

Like I said, MORE will not work with ROWS... OVER (FML commands).


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
jackyzsn
Member
posted Hide Post
OK, I added a new field, sort by it and runs fine now:

  
PRT_ST_FLG/A1 =                                         
   IF  ( CHQ_STAT_CD = 'O' AND CHQ_DT GE LOWDATE )      
      THEN '1'                                          
   ELSE  IF  CHQ_STAT_CD = 'O'                          
      THEN '2'                                          
   ELSE  IF  CHQ_STAT_CD = 'C'                          
      THEN '4'                                          
   ELSE  IF  CHQ_STAT_CD = 'P'                          
      THEN '3'                                          
   ELSE  IF  CHQ_STAT_CD = 'V'                          
      THEN '5'                                          
   ELSE  IF  CHQ_STAT_CD = 'S'                          
      THEN '6'                                          
   ELSE  IF  CHQ_STAT_CD = 'L'                          
      THEN '7';                                         
END                                                   
......

TABLE FILE A1CHQ                                    
PRINT                                               
CHQ_AMT/D17.2 AS 'CHEQUE AMOUNT'                    
CHQ_ID AS 'REFERENCE NUMBER'                        
CHQ_REF_ID AS 'POL/,CLIENT ID'                      
CHQ_MICR_NUM AS 'CHEQUE,MICRO,NUMBER'               
CHQ_DESC_LN_1_TXT/A50 AS 'CHEQUE DESCRIPTION'       
CHQ_PAYE_1_TXT/A35 AS 'PAYEE'                       
BY HIGHEST CHQ_STATUS NOPRINT                       
BY HIGHEST PRT_ORDER NOPRINT                        
   SUBTOTAL AS 'CHEQUE SUBTOTAL'                    
BY PRT_ST_FLG NOPRINT                               
BY HIGHEST PRT_STATUS AS 'CHEQUE,STATUS'            
BY HIGHEST CHQ_DT AS 'CHEQUE,ISSUE DATE'            
BY HIGHEST CHQ_PRCES_DT AS 'PROCESS   ,DATE'        
ON CHQ_STATUS PAGE-BREAK                            
ON CHQ_STATUS SUBFOOT                               
"<CHQ_STATUS CHEQUE TOTAL: <37 <ST.CHEQUE_AMT"      
HEADING                                             
"<STR_MMX CHEQUE REGISTER (J9817)                   
 <70 DATE: &DATETRMDYY <98 TIME: <104 &TOD </1"     
ON TABLE NOTOTAL                                    
MORE                                                
FILE B1CHQ                                          
END


Regards,

Jacky
 
Posts: 20 | Location: Toronto | Registered: August 13, 2007Report This Post
Tony A
Expert
posted Hide Post
I would have preferred a DECODE instead of all those IF .. THEN .. ELSE statements primarily because of the fact that there is an upper limit and if you have to extend a long list at a later date you might hit that limit (admittedly not with your short list at present but still ...)

PRT_ST_FLG/A1 = IF (CHQ_STAT_CD = 'O' AND CHQ_DT GE LOWDATE) THEN '1'
  ELSE DECODE CHQ_STAT_CD ('O' '2' 'C' '4' 'P' '3' 'V' '5' 'S' '6' 'L' '7' ELSE '9');

It is also sometimes useful to use an integer instead of the alpha, but that is down to preference .....

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
jackyzsn
Member
posted Hide Post
quote:
Originally posted by Tony A:
I would have preferred a DECODE instead of all those IF .. THEN .. ELSE statements primarily because of the fact that there is an upper limit and if you have to extend a long list at a later date you might hit that limit (admittedly not with your short list at present but still ...)

PRT_ST_FLG/A1 = IF (CHQ_STAT_CD = 'O' AND CHQ_DT GE LOWDATE) THEN '1'
  ELSE DECODE CHQ_STAT_CD ('O' '2' 'C' '4' 'P' '3' 'V' '5' 'S' '6' 'L' '7' ELSE '9');

It is also sometimes useful to use an integer instead of the alpha, but that is down to preference .....

T


Thanks Tony, very nice to know.


Regards,

Jacky
 
Posts: 20 | Location: Toronto | Registered: August 13, 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     SORTED by own order problem..

Copyright © 1996-2020 Information Builders