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     [SOLVED] WHERE statement limits

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] WHERE statement limits
 Login/Join
 
tinamshorty
Member
posted
I have reached the limit of WHERE statements in my fex so I created a file to use in a
WHERE IN FILE .
The problem is that I still reach some sort of limit. Here is a snippet of the code. My report creates the transaction description only up to a point and then every one after a certain point will get a description of "Miscellaneous-Not calculated" which is the default when not found. Can somebody advise me if there is some other workaround?
My data file of transaction codes (IMTRNCD) contains 162 entries.
You will note that I had to break up the IF statements also or I hit a limit there too.
DEFINE FILE IMACCM
R_TC/A30 = IF TC EQ '0001' THEN 'CREDIT INTEREST ADJ     (0001)' ELSE
           IF TC EQ '0002' THEN 'DEBIT INTEREST ADJ      (0002)' ELSE
           IF TC EQ '0014' THEN 'DEPOSIT                 (0014)' ELSE
           IF TC EQ '0015' THEN 'FORCE PAY CREDIT        (0015)' ELSE
           IF TC EQ '0016' THEN 'CREDIT MEMO             (0016)' ELSE
           IF TC EQ '0017' THEN 'DISBURSEMENT REVERSAL   (0017)' ELSE
           IF TC EQ '0018' THEN 'TRANSFER FROM SFM       (0018)' ELSE
           IF TC EQ '0029' THEN 'CHECKS CERTIFIED        (0029)' ELSE
           IF TC EQ '0030' THEN 'DEBIT MEMO              (0030)' ELSE
           IF TC EQ '0031' THEN 'TRANSFER TO CHECKING    (0031)' ELSE
           IF TC EQ '0033' THEN 'CHECK FORCE PAY         (0033)' ELSE
           IF TC EQ '0034' THEN 'CHECK FORCE PAY W/D     (0034)' ELSE
           'MISCELLANEOUS - NOT CALCULATED';                         
R_TC2/A30= IF R_TC NE 'MISCELLANEOUS - NOT CALCULATED' THEN R_TC ELSE
           IF TC EQ '0035' THEN 'CHECK FORCE PAY DEBIT   (0035)' ELSE
           IF TC EQ '0039' THEN 'CHECK OVER THE COUNTER  (0039)' ELSE
           IF TC EQ '0040' THEN 'CHECK INCLEARING        (0040)' ELSE
           IF TC EQ '0084' THEN 'SAVINGS/MMDA WITHDRAWAL (0084)' ELSE
           IF TC EQ '0230' THEN 'LOAN PAYMENT            (0230)' ELSE
           IF TC EQ '0330' THEN 'DEBIT DEPOSIT CORRECTION(0330)' ELSE
           IF TC EQ '0416' THEN 'CREDIT DEPOSIT CORRECTN (0416)' ELSE
           IF TC EQ '1016' THEN 'DEPOSIT BY MAIL         (1016)' ELSE
           IF TC EQ '1230' THEN 'CONVENIENCE BANK DEBIT  (1230)' ELSE
           IF TC EQ '1313' THEN 'AMERICAN DREAM BONUS    (1313)' ELSE
           IF TC EQ '1330' THEN 'SAFE DEPOSIT BOX PMT    (1330)' ELSE
           'MISCELLANEOUS - NOT CALCULATED';                         
R_TC3/A30= IF R_TC2 NE 'MISCELLANEOUS - NOT CALCULATED' THEN R_TC2 ELSE
           IF TC EQ '1416' THEN 'CONVENIENCE BANK CREDIT (1416)' ELSE  
           IF TC EQ '1530' THEN 'EXPENSE DIRECT DEPOSIT  (1530)' ELSE  
           IF TC EQ '1550' THEN 'PREMIUM PAYMENT         (1550)' ELSE  
           IF TC EQ '2430' THEN 'WIRE TRANSFER OUT       (2430)' ELSE  
           IF TC EQ '2516' THEN 'WIRE TRANSFER IN        (2516)' ELSE  
           IF TC EQ '2907' THEN 'STAR/POS CREDIT         (2907)' ELSE  
           IF TC EQ '3037' THEN 'STAR/POS DEBIT          (3037)' ELSE  
           IF TC EQ '3212' THEN 'EMPLOYEE PAYROLL DEP    (3212)' ELSE  
           IF TC EQ '3316' THEN 'TELEPH TRANSFER CREDIT  (3316)' ELSE  
           IF TC EQ '3330' THEN 'TELEPH TRANSFER DEBIT   (3330)' ELSE  
           IF TC EQ '3416' THEN 'SERVICE CHARGE WAIVER   (3416)' ELSE  
           'MISCELLANEOUS - NOT CALCULATED';                           
R_TC4/A30= IF R_TC3 NE 'MISCELLANEOUS - NOT CALCULATED' THEN R_TC3 ELSE
           IF TC EQ '3516' THEN 'RTN ITEM UNPD FEE WAIVE (3516)' ELSE  
           IF TC EQ '3616' THEN 'OVERDRAFT FEE WAIVER    (3616)' ELSE  
           IF TC EQ '3716' THEN 'DAU FEE WAIVER          (3716)' ELSE  
           IF TC EQ '4001' THEN 'CONVENIENCE BANK CREDIT (4001)' ELSE  
           IF TC EQ '4002' THEN 'CONVENIENCE BANK DEBIT  (4002)' ELSE  
           IF TC EQ '4003' THEN 'WIRE TRANSFER IN        (4003)' ELSE  
           IF TC EQ '4004' THEN 'WIRE TRANSFER OUT       (4004)' ELSE  
           IF TC EQ '4006' THEN 'CASH DEPOSIT            (4006)' ELSE  
           IF TC EQ '4007' THEN 'LOCKBOX DEPOSIT         (4007)' ELSE  
           IF TC EQ '4008' THEN 'WHOLESALE LOCKBOX DEP   (4008)' ELSE  
           IF TC EQ '4009' THEN 'REMOTE CHK CAPTURE DEP  (4009)' ELSE  
           'MISCELLANEOUS - NOT CALCULATED';                           
-****there are 15 sets of these IF statements***********************
END
TABLE FILE IMACCM
SUM                                               
  COMPUTE AMT_PCT/D7.4% = PCT.TRAN_AMT; NOPRINT   
  COMPUTE CNT_PCT/D7.4% = PCT.CNT.ACCT; NOPRINT   
  AND ROW-TOTAL AND COLUMN-TOTAL                  
BY BANK NOPRINT                                                                      
BY TC NOPRINT                                     
COUNT ACCT AS ''                                  
BY BANK     NOPRINT PAGE-BREAK                                 
BY TC NOPRINT                                     
BY R_TC8 AS 'TC'             
BY DTPOST AS 'POSTED'        
ACROSS R_TRAN_AMT AS 'TX AMT'
WHERE TC IN FILE IMTRNCD
ON TC RECAP                                                         
CNT_ACCT/D8.5=CNT.ACCT;                                             
ON TC SUBFOOT                                                       
  "</1 TOTAL PERCENTAGE OF DOLLAR AMOUNT FOR <TC = <ST.AMT_PCT"     
  "</1 TOTAL PERCENTAGE OF ALL TRANSACTIONS FOR <TC = <ST.CNT_PCT"  
END


Sample of output


TX AMT
<$1,000 $1,000-$5,000
TC POSTED
-----------------------------------------------------------------------------
CREDIT INTEREST ADJ (0001) 03/02/2009 0 0
03/04/2009 2 0
03/10/2009 0 0
03/11/2009 1 0
03/16/2009 1 0
03/19/2009 1 0
03/24/2009 2 0
03/27/2009 4 0
03/31/2009 1 0

TOTAL PERCENTAGE OF DOLLAR AMOUNT FOR 0001 = .0000%
TOTAL PERCENTAGE OF ALL TRANSACTIONS FOR 0001 = .0003%

DEBIT INTEREST ADJ (0002) 03/04/2009 1 0
03/09/2009 14 0
03/10/2009 1 0
03/12/2009 1 0
03/13/2009 2 0
03/16/2009 1 0
03/24/2009 2 0
03/26/2009 2 0

TOTAL PERCENTAGE OF DOLLAR AMOUNT FOR 0002 = .0000%

TOTAL PERCENTAGE OF ALL TRANSACTIONS FOR 0002 = .0004%

... After output of about 50 tran codes, i start getting this....

MISCELLANEOUS - NOT CALCULATED 03/02/2009 15 0
03/03/2009 16 0
03/04/2009 11 1
03/05/2009 21 0
03/06/2009 9 1
03/09/2009 14 0
03/10/2009 15 0
03/11/2009 11 1
03/12/2009 24 0

TOTAL PERCENTAGE OF DOLLAR AMOUNT FOR 4831 = .0000%

TOTAL PERCENTAGE OF ALL TRANSACTIONS FOR 4831 = .0062%

As you can see, i do get the tran code in the SUBFOOT, but not in the detail.

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


FOCUS for Mainframe 7.2
Windows and IBM Z9 Mainframe processor
FOCUS, Excel


 
Posts: 21 | Registered: October 31, 2008Report This Post
<JG>
posted
try using a decode using a file

quote:
The file can contain up to 32,767 characters in the file
 
Report This Post
susannah
Expert
posted Hide Post
you're right, IF THEN's have a limit
but did you know that
you can redefine the same variable?
RTC/A30=IF TC EQ ..... ELSE 'XXX';
RTC/A30=IF TC EQ .... ELSE RTC ;
RTC/A30=IF TC EQ ..... ELSE RTC ;
lather , rinse, repeat...

and when your fex references RTC, it will pull the last of the defined versions.



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
tinamshorty
Member
posted Hide Post
Susannah,
I did not know that! Thanks for the info. I am trying the DECODE from a file function suggested by JG right now. If I don't have success with that, I will look into your suggestion.
Thanks!


FOCUS for Mainframe 7.2
Windows and IBM Z9 Mainframe processor
FOCUS, Excel


 
Posts: 21 | Registered: October 31, 2008Report This Post
j.gross
Virtuoso
posted Hide Post
Or load them into a Focus table and join to it on TC.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Tina,

IF ... THEN ... ELSE are limited to 16 nestings.

DECODE, as JG rightly says, is limited to 32K bytes, so beware of trailing blanks. If you have 15 sets of IFs, about 230 entries then your DECODE file should be about 7K which shouldn't slow down things too much.

I would go with Jack's suggestion.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
tinamshorty
Member
posted Hide Post
Thanks everyone! I used the DECODE with my file of tran codes and it works like a charm!


FOCUS for Mainframe 7.2
Windows and IBM Z9 Mainframe processor
FOCUS, Excel


 
Posts: 21 | Registered: October 31, 2008Report 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] WHERE statement limits

Copyright © 1996-2020 Information Builders