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     [CLOSED] printing direct-debit voucher =0 which should not be printed

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] printing direct-debit voucher =0 which should not be printed
 Login/Join
 
Gold member
posted
Hi I have below fex file

where in reports its printing direct debit voucher = 0 but it should not print , its not getting direct_debit is not 0 den it should not print it.
If someone can help me with this. Its bit urgent issue i need to fix and im not understanding what could be the solution


-SET EXT_NAME = FGETENV(19,'EP02_EXT_SHORT_NAME',14,'A14');
-SET LAY_NAME = FGETENV(19,'EP02_LAY_SHORT_NAME',14,'A14');
-* Linux Migration Start Change
-SET &WORKDIR=FGETENV(128,'TLG_SHORTNAME_DIR',128,'A128');
-SET &UNAME_RESULT = FGETENV(12,'UNAME_RESULT',20,'A20');
-* Linux Migration End Change

-* Linux Migration Start Change
-SET EXT_FILE = (&WORKDIR || '/'||&EXT_NAME);
-IF &UNAME_RESULT EQ 'Linux' THEN GOTO L1556551 ;
-SET EXT_FILE = ('..' || '/'||&EXT_NAME);
-L1556551

-* Linux Migration End Change

-* Linux Migration Start Change
-SET &&REP_FILE = (&WORKDIR || '/'||&LAY_NAME);
-IF &UNAME_RESULT EQ 'Linux' THEN GOTO L1556631 ;
-SET &&REP_FILE = ('..' || '/'||&LAY_NAME);
-L1556631

-* Linux Migration End Change


-SET &REPCODE='AREP-02-EXP';
-SET &REPNAME='E X C E P T I O N A L P A Y M E N T S R E P O R T';
SET PANEL=360
EX apcon
-RUN
-IF &FOCERRNUM NE 0 GOTO ERR;

DEFINE FILE M_PYMMTD
PMDESC/A10 = ''''|METHOD_SHORT_DESC|'''';
SPACE1/A2 = ' ';
PYMMETHOD/A4 = ''''|PYM_METHOD|'''';
END

TABLE FILE M_PYMMTD
PRINT PYMMETHOD SPACE1 PMDESC
ON TABLE SAVE AS PMSAVE
END

EX dbdis
-RUN
-IF &FOCERRNUM NE 0 GOTO ERR;

FILEDEF OFFLINE DISK &&REP_FILE
OFFLINE
-INCLUDE gnrpdef.fex

-*-----Start chnage #1
-*FILEDEF arep02 DISK &EXT_FILE (RECFM F LRECL 796
FILEDEF arep02 DISK &EXT_FILE (RECFM F LRECL 808
-*-----End change #1
DEFINE FILE AREP02
ROWID/A1=' ';
STR_NAME/A15=SUBSTR(50,STR_NAME,1,15,15,'A15');
ARE_CITY/A20=SUBSTR(26,ARE_CITY,1,20,20,'A20');
ARE_MEMO/A90=SUBSTR(180,ARE_MEMO,1,90,90,'A90');
PM_DCOD/A8=DECODE PYM_METH(PMSAVE);
PYMMETH/A11=IF PYM_METH EQ ' ' THEN ' '
ELSE PYM_METH||'-'||PM_DCOD;
-*DEP_DATE/A8YYMD=DEP_DATE;
-*DEP_DATE/MDYY=DEP_DATE;
N_DEP_DATE/A10=EDIT(DEP_DATE,'$$$$99/99/')|EDIT(DEP_DATE,'9999');


ACT_DATE/A8YYMD=ACT_DATE;
ACT_DATE/MDYY=ACT_DATE;
RSUB_NO/A10=RJUST(10,SUB_NO,'A10');
SUBSCRIB/A12=IF RSUB_NO EQ ' ' THEN ' '
ELSE RSUB_NO;
SOURCE/A10=SOU_TYPE|' '|SOU_ID;
OPE_ID/A9=EDIT(OPE_ID);
OPEID/A9=IF OPE_ID EQ '000000000' THEN ' '
ELSE OPE_ID;
-*-----Start change #1
PRT_ACTV_AMT/P15.2C=ACTV_AMT;
ORI_AMT/P14.2C = ORI_AMT;
ACTV_AMT/P14.2C = ACTV_AMT;
-*PRT_ACTV_AMT/P12.2C=ACTV_AMT;
-*ORI_AMT/P11.2C = ORI_AMT;
-*ACTV_AMT/P11.2C = ACTV_AMT
-*-----End change #1
LINEX/I7C WITH BAN = 1;
NAME/A60=IF FIR_NAME NE ' ' THEN FIR_NAME
ELSE BUS_NAME ;
ADR/A100=IF STR_NAME NE ' ' THEN HOU_NO|STR_NAME
ELSE PRI_LN ;
END

-RUN
-IF &FOCERRNUM NE 0 GOTO ERR;

TABLE FILE AREP02
HEADING
-INCLUDE gnrphdr.fex
" "<61 +----- File Information -----+"
PRINT
FST.BAN NOPRINT
SEQ_NO1 AS ' PYM ,SEQ NO' IN 1
ORI_BAN AS ' ORIGINAL, BAN' IN 12
SUBSCRIB AS ' SUBSCRIBER , ' IN 23
PYMMETH AS ' PAYMENT , METHOD' IN 37
N_DEP_DATE AS ' DEPOSIT , DATE' IN 49
SOURCE AS ' SOURCE , ' IN 61
SEQ_NO AS ' SEQ , ' IN 73
BATCH_NO AS 'BATCH, ' IN 80
LIN_NO AS 'LINE, ' IN 87
ORI_AMT AS ' ORIGINAL, AMOUNT' IN 92
-*-----Start change #1
ACTV_AMT AS ' PAYMENT , AMOUNT' IN 110
OPEID AS ' OPERATOR, ID' IN 129
-* ACTV_AMT AS ' PAYMENT , AMOUNT' IN 104
-* OPEID AS ' OPERATOR, ID' IN 120
-*-----End change #1
BY ROWID NOPRINT
BY BAN NOPRINT
BY ACT_DATE NOPRINT
BY HIGHEST ACTV_AMT NOPRINT
BY SEQ_NO1 NOPRINT

ON SEQ_NO1 SUBFOOT
"<24 Bank: WHEN (BAN_CODE1 NE ' ') OR (ACC_NO NE ' ') OR (CHECK_NO NE ' ');
SUBFOOT
"<24 Coupon Code: WHEN (SUB_METH NE ' ') AND (GIF_NO NE ' ');
SUBFOOT
"<24 Credit Card Type: WHEN (SUB_METH NE ' ') AND (CAR_NO NE ' ');
SUBFOOT
"<41 Direct Debit Voucher: WHEN &DBT_VOUC.EVAL NE 0;

SUBFOOT
"<24 Exception Reason: Posting date: "
SUBFOOT
"<5 NAME : STREET : <5 CITY : <74 STATE : WHEN ((NAME NE ' ') OR (ADR NE ' ')) AND (CAR_NO NE ' ');

ON ACTV_AMT NOSPLIT

ON BAN PAGE-BREAK
ON BAN SUBFOOT
"<+4 Number of Payments: <60 "
"<42 Total Amount: <55 "

ON ROWID SUBFOOT
""
"<42 Total Amount: <55 "

FOOTING BOTTOM

-IF &&NOCSV GOTO NO_CSV_SET;
-WRITE DDTTL 'Exception Control Account','PYM SEQ NO','ORIGINAL BAN',,
-'SUBSCRIBER','PAYMENT METHOD','DEPOSIT DATE','SOURCE','SEQ','BATCH',,
-'LINE','ORIGINAL AMOUNT','PAYMENT AMOUNT','OPERATOR ID'
-SET &STOP_FIELD = 'OPEID';
-NO_CSV_SET

-INCLUDE gnrpftr.fex
END

-RUN
-IF &FOCERRNUM NE 0 GOTO ERR;

-INCLUDE gnrpempt.fex

-* Linux Migration Start Change
-IF &UNAME_RESULT EQ 'Linux' THEN GOTO LINUX_OS;
-QUIT FOCUS 0
-* Linux Migration End Change

-ERR
-TYPE Report failed.

-* Linux Migration Start Change
-IF &UNAME_RESULT EQ 'Linux' THEN GOTO LINUX_OS;
-QUIT FOCUS &FOCERRNUM
-LINUX_OS
-EXIT
-* Linux Migration End Change
END


Thanks a lot in advance

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Virtuoso
posted Hide Post
Where is the condition to filter ?


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
hi,

Its failing at below location
I tried changing it to below SUBFOOT
"<41 Direct Debit Voucher: WHEN &DBT_VOUC.EVAL NE 0;

SUBFOOT

but original was

SUBFOOT
"<41 Direct Debit Voucher: WHEN DBT_VOUC NE 0;

SUBFOOT

but its printing in report direct debit voucher =0 whereevre its 0


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Virtuoso
posted Hide Post
A filter cannot be applied from a SUBFOOT. It has to be a filter by itself.
Everything that you place in a SUBFOOT is a "text display". From a SUBFOOT, variables and/or fields will be evaluated to by displayed, but a WHERE clause cannot be evaluated to perform a data filter.
You need to place the WHERE clause on its own row.


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
hi
in code its like that only

SUBFOOT
"<41 Direct Debit Voucher: WHEN DBT_VOUC NE 0;

so im not understanding what could be done to resolve the issue


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Gold member
posted Hide Post
i dont know but while printing here why its posting like that but original code they are on seperate lines only one line for " " and other line we have when statement


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Virtuoso
posted Hide Post
The below is not a proper valid Focus syntax
ON SEQ_NO1 SUBFOOT
"<24 Bank: WHEN (BAN_CODE1 NE ' ') OR (ACC_NO NE ' ') OR (CHECK_NO NE ' ');
SUBFOOT
"<24 Coupon Code: WHEN (SUB_METH NE ' ') AND (GIF_NO NE ' ');
SUBFOOT
"<24 Credit Card Type: WHEN (SUB_METH NE ' ') AND (CAR_NO NE ' ');
SUBFOOT
"<41 Direct Debit Voucher: WHEN &DBT_VOUC.EVAL NE 0;

SUBFOOT
"<24 Exception Reason:  Posting date: "
SUBFOOT
"<5 NAME :  STREET :  <5 CITY :  <74 STATE : WHEN ((NAME NE ' ') OR (ADR NE ' ')) AND (CAR_NO NE ' ');


SUBFOOT should be enclose with double quotes " ... " such as :
ON SEQ_NO1 SUBFOOT
"<24 Bank: "
WHEN (BAN_CODE1 NE ' ') OR (ACC_NO NE ' ') OR (CHECK_NO NE ' ');

The WHEN condition IS NOT a data filter.
It's a condition to perform or not the SUBFOOT.
Meaning that :
1- ON SEQ_NO1 SUBFOOT "<24 Bank: " will be performed only when WHEN (BAN_CODE1 NE ' ') OR (ACC_NO NE ' ') OR (CHECK_NO NE ' '); is true
2- SUBFOOT "<24 Coupon Code: " will be performed only when : WHEN (SUB_METH NE ' ') AND (GIF_NO NE ' '); is true and so on.

Look at these three samples. First one is similar as your, second as it should and third with a filter
-* Invalid sample with conditional SubFoot
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
ON COUNTRY SUBFOOT
"SUBFOOT 1 WHEN COUNTRY EQ 'FRANCE';
SUBFOOT
"SUBFOOT 2 WHEN COUNTRY NE 'FRANCE';
END


-* Valid sample with conditional SubFoot
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
ON COUNTRY SUBFOOT
"SUBFOOT 1 FOR WHEN COUNTRY IS FRANCE"
WHEN COUNTRY EQ 'FRANCE';
SUBFOOT
"SUBFOOT 2 FOR WHEN COUNTRY IS NOT FRANCE"
WHEN COUNTRY NE 'FRANCE';
END


-* Valid sample with conditional SubFoot and Filter
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
ON COUNTRY SUBFOOT
"SUBFOOT 1 FOR WHEN COUNTRY IS FRANCE"
WHEN COUNTRY EQ 'FRANCE';
SUBFOOT
"SUBFOOT 2 FOR WHEN COUNTRY IS NOT FRANCE"
WHEN COUNTRY NE 'FRANCE';

WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND' OR 'JAPAN';
END


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
When you post code or sample, use the code tag to enclose the code and/or sample.
This is the far right icon on the ribbon
</>

That way it will keep the text formatting as is


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
This is the correct syntax
SUBFOOT
"<41 Direct Debit Voucher: "
WHEN DBT_VOUC NE 0 ;


You need to use the code tag to post code otherwise hidden characters (carriage return per example) may be not resolved properly by the Forum.


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
quote:
"<41 Direct Debit Voucher: < DBT_VOUS"


WHEN DBT_VOUS NE 0
  

Hi i have code line as above


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Gold member
posted Hide Post
i think its not able to get NE 0 value so its printing everytime .

Is there any other way i can sprcify NE 0 or to compare exact value


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Virtuoso
posted Hide Post
If you want the value of the field DBT_VOUS displayed in the SUBFOOT, it should be like the below :
"<41 Direct Debit Voucher: <DBT_VOUS"
WHEN DBT_VOUS NE 0;


And you should use the code tag, not the quote tag :-)
When using the code tag the following will appear in your post (without spaces) : [ code] [/ code]
Then you place your code in between the two tags.

I repeat myself:

The WHEN clause is NOT TO FILTER data. It's a CONDITION to perform or not the previous action (in your case the SUBFOOT).

To filter data you need to use WHERE. See my previous three samples.


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
Hi
I want the value of the field itself so i can compare if its not equal to 0 then only print it. this is what i want but currently its printing even if its 0

Thanks a lot for ur help


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Gold member
posted Hide Post
Can u please suggest what can be done to resolve my issue.
As we have same conditions in other code and its wokring fine.


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Virtuoso
posted Hide Post
quote:

but currently its printing even if its 0


Because you don't have any data filter (WHERE).

I already gave you all the samples and proper syntax that you need to answer your need.

Adjust your code to follow the syntax rules and it should work.

Then if you still have issue, post back your code using the code tag and we'll see if something else can be done.


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
hi i tried using where condition diffrent diffrent types but still its not working.with below where its not generating expected report data is diffrent.

  
SUBFOOT
"<41 Direct Debit Voucher: <DBT_VOUC"
WHEN   DBT_VOUC NE 0  ;
WHERE DBT_VOUC EQ DBT_VOUC ;
SUBFOOT




My original code is as below

  
-SET &ECHO=ALL;
-SET EXT_NAME = FGETENV(19,'EP02_EXT_SHORT_NAME',14,'A14');
-SET LAY_NAME  = FGETENV(19,'EP02_LAY_SHORT_NAME',14,'A14');
-* Linux Migration Start Change
-SET &WORKDIR=FGETENV(128,'TLG_SHORTNAME_DIR',128,'A128');
-SET &UNAME_RESULT = FGETENV(12,'UNAME_RESULT',20,'A20');
-* Linux Migration End Change

-* Linux Migration Start Change
-SET EXT_FILE = (&WORKDIR || '/'||&EXT_NAME);
-IF  &UNAME_RESULT EQ 'Linux' THEN GOTO L1556551 ;
-SET EXT_FILE = ('..' || '/'||&EXT_NAME);
-L1556551

-* Linux Migration End Change

-* Linux Migration Start Change
-SET &&REP_FILE  = (&WORKDIR || '/'||&LAY_NAME);
-IF  &UNAME_RESULT EQ 'Linux' THEN GOTO L1556631 ;
-SET &&REP_FILE  = ('..' || '/'||&LAY_NAME);
-L1556631

-* Linux Migration End Change

 
-SET &REPCODE='AREP-02-EXP';
-SET &REPNAME='E X C E P T I O N A L   P A Y M E N T S   R E P O R T';
SET PANEL=360 
EX apcon
-RUN
-IF &FOCERRNUM NE 0 GOTO ERR;
 
DEFINE FILE M_PYMMTD
PMDESC/A10 = ''''|METHOD_SHORT_DESC|'''';
SPACE1/A2 = '  ';
PYMMETHOD/A4 = ''''|PYM_METHOD|'''';
END
 
TABLE FILE M_PYMMTD
PRINT PYMMETHOD SPACE1 PMDESC
ON TABLE SAVE AS PMSAVE
END
 
EX dbdis
-RUN
-IF &FOCERRNUM NE 0 GOTO ERR;
 
FILEDEF OFFLINE DISK &&REP_FILE
OFFLINE
-INCLUDE gnrpdef.fex

-*-----Start chnage #1 
-*FILEDEF arep02 DISK &EXT_FILE (RECFM F  LRECL 796
FILEDEF arep02 DISK &EXT_FILE (RECFM F  LRECL 808
-*-----End change #1
DEFINE FILE AREP02
ROWID/A1=' ';
STR_NAME/A15=SUBSTR(50,STR_NAME,1,15,15,'A15');
ARE_CITY/A20=SUBSTR(26,ARE_CITY,1,20,20,'A20');
ARE_MEMO/A90=SUBSTR(180,ARE_MEMO,1,90,90,'A90');
PM_DCOD/A8=DECODE PYM_METH(PMSAVE);
PYMMETH/A11=IF PYM_METH EQ '  ' THEN '           '
ELSE PYM_METH||'-'||PM_DCOD;
-*DEP_DATE/A8YYMD=DEP_DATE;
-*DEP_DATE/MDYY=DEP_DATE;
N_DEP_DATE/A10=EDIT(DEP_DATE,'$$$$99/99/')|EDIT(DEP_DATE,'9999');
 
 
ACT_DATE/A8YYMD=ACT_DATE;
ACT_DATE/MDYY=ACT_DATE;
RSUB_NO/A10=RJUST(10,SUB_NO,'A10');
SUBSCRIB/A12=IF RSUB_NO EQ '          ' THEN '            '
ELSE RSUB_NO;
SOURCE/A10=SOU_TYPE|' '|SOU_ID;
OPE_ID/A9=EDIT(OPE_ID);
OPEID/A9=IF OPE_ID EQ '000000000' THEN '         '
ELSE  OPE_ID;
-*-----Start change #1
PRT_ACTV_AMT/P15.2C=ACTV_AMT;
ORI_AMT/P14.2C = ORI_AMT;
ACTV_AMT/P14.2C = ACTV_AMT;
-*PRT_ACTV_AMT/P12.2C=ACTV_AMT;
-*ORI_AMT/P11.2C = ORI_AMT;
-*ACTV_AMT/P11.2C = ACTV_AMT
-*-----End change #1
LINEX/I7C WITH BAN = 1;
NAME/A60=IF FIR_NAME NE ' ' THEN FIR_NAME
ELSE BUS_NAME ;
ADR/A100=IF STR_NAME NE ' ' THEN HOU_NO|STR_NAME
ELSE PRI_LN ;
END
 
-RUN
-IF &FOCERRNUM NE 0 GOTO ERR;
 
TABLE FILE AREP02
HEADING
-INCLUDE gnrphdr.fex
"</1 <48 Exception Control Account: <BAN> </1"
"<61 +----- File Information -----+"
PRINT
   FST.BAN  NOPRINT
      SEQ_NO1  AS ' PYM ,SEQ NO'                IN 1
      ORI_BAN  AS ' ORIGINAL,   BAN'            IN 12
      SUBSCRIB AS ' SUBSCRIBER ,        '       IN 23
      PYMMETH  AS '  PAYMENT ,  METHOD'         IN 37
    N_DEP_DATE AS '  DEPOSIT ,   DATE'          IN 49
      SOURCE   AS '  SOURCE  ,        '         IN 61
      SEQ_NO   AS ' SEQ ,     '                 IN 73
      BATCH_NO AS 'BATCH,     '                 IN 80
      LIN_NO   AS 'LINE, '                      IN 87
      ORI_AMT  AS ' ORIGINAL,  AMOUNT'          IN 92
-*-----Start change #1      
      ACTV_AMT AS ' PAYMENT , AMOUNT'           IN 110
      OPEID    AS ' OPERATOR,    ID'            IN 129
-*    ACTV_AMT AS ' PAYMENT , AMOUNT'           IN 104
-*    OPEID    AS ' OPERATOR,    ID'            IN 120
-*-----End change #1      
BY          ROWID    NOPRINT
BY          BAN      NOPRINT
BY          ACT_DATE NOPRINT
BY HIGHEST  ACTV_AMT NOPRINT
BY          SEQ_NO1  NOPRINT
 
ON SEQ_NO1 SUBFOOT
"<24 Bank: <BAN_CODE1 Bank Account: <ACC_NO Check#: <CHECK_NO"
WHEN  (BAN_CODE1 NE ' ') OR (ACC_NO NE ' ') OR (CHECK_NO NE ' ');
SUBFOOT
"<24 Coupon Code: <SUB_METH Gift Certificate#: <GIF_NO"
WHEN  (SUB_METH NE ' ') AND (GIF_NO NE ' ');
SUBFOOT
"<24 Credit Card Type: <SUB_METH Credit Card#: <CAR_NO"
WHEN  (SUB_METH NE ' ') AND (CAR_NO NE ' ');
SUBFOOT
"<41 Direct Debit Voucher: <DBT_VOUC"
WHEN   DBT_VOUC NE 0  ;
SUBFOOT
"<24 Exception Reason: <EXC_DESC>       Posting date: <ACT_DATE>"
SUBFOOT
"<5 NAME : <NAME> STREET : <ADR> </1
 <5 CITY : <ARE_CITY> <74 STATE  : <ARE_STAT> </1"
WHEN ((NAME NE ' ') OR (ADR NE ' ')) AND (CAR_NO NE ' ');
 
ON ACTV_AMT NOSPLIT
 
ON BAN PAGE-BREAK
ON BAN SUBFOOT
"</1 *** Exception Control Account Totals:
<+4 Number of Payments: <60 <ST.LINEX>"
"<42 Total Amount: <55 <ST.PRT_ACTV_AMT>"
 
ON ROWID SUBFOOT
"</1 *** Report Totals: <+23 Number of Payments: <60 <TOT.LINEX>"
"<42 Total Amount: <55 <TOT.PRT_ACTV_AMT>"
 
FOOTING BOTTOM
 
-IF &&NOCSV GOTO NO_CSV_SET;
-WRITE DDTTL 'Exception Control Account','PYM SEQ NO','ORIGINAL BAN',,
-'SUBSCRIBER','PAYMENT METHOD','DEPOSIT DATE','SOURCE','SEQ','BATCH',,
-'LINE','ORIGINAL AMOUNT','PAYMENT AMOUNT','OPERATOR ID'
-SET &STOP_FIELD = 'OPEID';
-NO_CSV_SET
 
-INCLUDE gnrpftr.fex
END
 
-RUN
-IF &FOCERRNUM NE 0 GOTO ERR;
 
-INCLUDE gnrpempt.fex

-* Linux Migration Start Change
-IF  &UNAME_RESULT EQ 'Linux' THEN GOTO LINUX_OS;
-QUIT FOCUS 0
-* Linux Migration End Change

-ERR
-TYPE  Report failed.

-* Linux Migration Start Change
-IF  &UNAME_RESULT EQ 'Linux' THEN GOTO LINUX_OS;
-QUIT FOCUS &FOCERRNUM
-LINUX_OS
-EXIT
-* Linux Migration End Change
END



If u can help something here
Thanks a lot for ur help


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Virtuoso
posted Hide Post
WHERE and WHEN are two different thing.

WHERE is to filter data. So when you put WHERE clauses, the report output data will only meet the criteria. Such as below, the report will only include FRANCE, ENGLAND and JAPAN records.

WHEN is to "activate" a condition (not a filter). Such as below WHEN the country is FRANCE the SUBHEAD/SUBFOOT 1 will be displayed. WHEN it's not FRANCE it's going to be SUBHEAD/SUBFOOT 2. WHEN is "normally" used in styling options

Focus is applying WHERE clause on data source to extract data as per specified filter.
Focus is then applying WHEN clause to extracted (and filtered) data to perform action on specific condition.

TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR

-* WHERE to filter data source
WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND' OR 'JAPAN';

ON COUNTRY SUBHEAD
"----------------------------------"
"SUBHEAD 1 : when country is France"
-* WHEN to apply specific condition on extracted data
WHEN COUNTRY EQ 'FRANCE';

ON COUNTRY SUBHEAD
"22222222222222222222222222222222222222"
"SUBHEAD 2 : when country is NOT France"
-* WHEN to apply specific condition on extracted data
WHEN COUNTRY NE 'FRANCE';

ON COUNTRY SUBFOOT
"SUBFOOT 1 : when country is France"
"----------------------------------"
""
-* WHEN to apply specific condition on extracted data
WHEN COUNTRY EQ 'FRANCE';

ON COUNTRY SUBFOOT
"SUBFOOT 2 : when country is NOT France"
"22222222222222222222222222222222222222"
""
-* WHEN to apply specific condition on extracted data
WHEN COUNTRY NE 'FRANCE';

ON TABLE SET STYLE *
TYPE=DATA,
   COLOR=BLUE,
   WHEN=COUNTRY EQ 'FRANCE',
$
TYPE=DATA,
   COLOR=GREEN,
   WHEN=COUNTRY NE 'FRANCE',
$
TYPE=SUBHEAD,
   COLOR=BLUE,
   WHEN=COUNTRY EQ 'FRANCE',
$
TYPE=SUBHEAD,
   COLOR=GREEN,
   WHEN=COUNTRY NE 'FRANCE',
$
TYPE=SUBFOOT,
   COLOR=BLUE,
   WHEN=COUNTRY EQ 'FRANCE',
$
TYPE=SUBFOOT,
   COLOR=GREEN,
   WHEN=COUNTRY NE 'FRANCE',
$
ENDSTYLE
END

Difficult to understand what you want since it seems that from all this conversation, you need basic Focus training. So contact your local IBI office.

From what I can understand you want to omit (not include some records in the output), so add a WHERE clause.
So as per below, only records where DBT_VOUS is not 0 will be included in the report output.

BY          ROWID    NOPRINT
BY          BAN      NOPRINT
BY          ACT_DATE NOPRINT
BY HIGHEST  ACTV_AMT NOPRINT
BY          SEQ_NO1  NOPRINT

WHERE DBT_VOUC NE 0;

ON SEQ_NO1 SUBFOOT
"<24 Bank: <BAN_CODE1 Bank Account: <ACC_NO Check#: <CHECK_NO"
WHEN  (BAN_CODE1 NE ' ') OR (ACC_NO NE ' ') OR (CHECK_NO NE ' ');
.....


But the below have no sense since it's always going to be true : "A" is ALWAYS going to be equal to "A"
WHERE DBT_VOUC EQ DBT_VOUC ;

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


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Expert
posted Hide Post
SUBFOOT
"<41 Direct Debit Voucher: <DBT_VOUC"
WHEN   DBT_VOUC NE 0  ;

What is the format of the column DBT_VOUC?


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
Gold member
posted Hide Post
Hi,

Yes im very new to webfocus and directly working on support for webfocus . sorry for silly questions.

bascially what i want is to print direct debit voucher no only when its a digit no and not to print it when its 0

my format for DBT_VOUC is as below

FIELDNAME=DBT_VOUC ,ALIAS= ,FORMAT=P12 ,ACTUAL=P6 ,$


Thanks a lot for ur help


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Virtuoso
posted Hide Post
BY          ROWID    NOPRINT
BY          BAN      NOPRINT
BY          ACT_DATE NOPRINT
BY HIGHEST  ACTV_AMT NOPRINT
BY          SEQ_NO1  NOPRINT

WHERE DBT_VOUC NE 0;

ON SEQ_NO1 SUBFOOT
"<24 Bank: <BAN_CODE1 Bank Account: <ACC_NO Check#: <CHECK_NO"
WHEN  (BAN_CODE1 NE ' ') OR (ACC_NO NE ' ') OR (CHECK_NO NE ' ');
.....


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
hi martin,

With below condition added it will only take record where direct debit voucher is not equal to 0 but i want all the records where its 0 and not 0 ,
But i want it to only print direct debit voucher no below record where its not 0. and not to print direct debit voucher no where its 0

as below



+----- File Information -----+
PYM ORIGINAL SUBSCRIBER PAYMENT DEPOSIT SOURCE SEQ BATCH LINE ORIGINAL PAYMENT OPERATOR
SEQ NO BAN METHOD DATE AMOUNT AMOUNT ID
------ --------- ------------ ---------- ---------- ---------- ----- ----- ---- --------- --------- ---------
7038 520394552 CC-CREDITCD 03/26/2012 O OCSM 0 0 0 232.56 232.56 000051504
Credit Card Type: AM Credit Card#: UQN97EEGCNXP1002
Direct Debit Voucher: 226303961
Exception Reason: Posting date: 03/26/2012
13008 520394552 CC-CREDITCD 04/30/2012 O OCSM 0 0 0 300.00 300.00 000051504
Credit Card Type: AM Credit Card#: UQN97EEGCNXP1002
Direct Debit Voucher: 0
Exception Reason: Posting date: 04/30/2012
13009 520394552 CC-CREDITCD 04/30/2012 O OCSM 0 0 0 500.00 100.00 000900083
Credit Card Type: AM Credit Card#: UQN97EEGCNXP1002
Direct Debit Voucher: 0


Here its printing direct debit voucher even if its 0but it should display like this


+----- File Information -----+
PYM ORIGINAL SUBSCRIBER PAYMENT DEPOSIT SOURCE SEQ BATCH LINE ORIGINAL PAYMENT OPERATOR
SEQ NO BAN METHOD DATE AMOUNT AMOUNT ID
------ --------- ------------ ---------- ---------- ---------- ----- ----- ---- --------- --------- ---------
7038 520394552 CC-CREDITCD 03/26/2012 O OCSM 0 0 0 232.56 232.56 000051504
Credit Card Type: AM Credit Card#: UQN97EEGCNXP1002
Direct Debit Voucher: 226303961
Exception Reason: Posting date: 03/26/2012
13008 520394552 CC-CREDITCD 04/30/2012 O OCSM 0 0 0 300.00 300.00 000051504
Credit Card Type: AM Credit Card#: UQN97EEGCNXP1002

Exception Reason: Posting date: 04/30/2012
13009 520394552 CC-CREDITCD 04/30/2012 O OCSM 0 0 0 500.00 100.00 000900083
Credit Card Type: AM Credit Card#: UQN97EEGCNXP1002


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Virtuoso
posted Hide Post
Genius

If this is a critical production issue you need to open a case with techsupport. The forum is meant to help with development questions but it is not intended to assist with critical urgent issues.

You have been provided with some excellent suggestions but if they are not solving the problem please open a case.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Virtuoso
posted Hide Post
quote:
But i want it to only print direct debit voucher no below record where its not 0. and not to print direct debit voucher no where its 0

Genius, your syntax is hard to read. There is so many "no" "not" "0" then it's hard to determine it the "no" is for "number" or it mean "no" as a negation (not).
Also, as I mentioned previously, us the code tag when sharing samples. The code tag is useful for code sample and data sample. It keep proper alignment.

Expressing your condition as a logical test may help to understand your need and could help (even yourself) to define proper condition.

Each WHEN clause is applied to a whole styling word section (per example a whole SUBFOOT).
So, the simplest way is to have two (or more depending on number of cases) distinct SUBFOOT section with each its own WHEN clause
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR

ON COUNTRY SUBFOOT
"When country is France"
"----------------------------------"
"Model : <MODEL"
WHEN COUNTRY EQ 'FRANCE';

ON COUNTRY SUBFOOT
"When country is NOT France"
"----------------------------------"
WHEN COUNTRY NE 'FRANCE';
END

As per above, when it's FRANCE the Model is displayed otherwise it's nothing (a blank line could also have been printed instead).


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Gold member
posted Hide Post
hi martin,

thanks a lot for ur help, i will try the way u said.

Can u please tell me one thing if i want to check what is value of DBT_VOUC how do i print it with -TYPE command

so i will get an idea whats comming in DBT_VOUC

Thanks a lot


WebFOCUS, Linux
Excel, CSV
 
Posts: 62 | Registered: May 23, 2017Report This Post
Virtuoso
posted Hide Post
The simplest : Add it as in a BY or a PRINT.
DBT_VOUC is a field from your data source so you need to include it as part of the report to see its value.

Sorry to tell that, but this question is so basic that it confirm that you really need Focus training.
Go get some as soon as you can. It urge. it will help you a lot.


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
Genius,

You seems to have a lot of syntax error or code misplaced such as in :
-1-
PM_DCOD/A8=DECODE PYM_METH(PMSAVE);

-2-
OPEID/A9=IF OPE_ID EQ '000000000' THEN '         ' ELSE  OPE_ID;
 "<41 Direct Debit Voucher: <DBT_VOUC"
WHEN  DBT_VOUC NE 1;
-*  change

-3-
SUBFOOT
"<24 Credit Card Type: <SUB_METH Credit Card#: <CAR_NO"
WHEN  (SUB_METH NE ' ') AND (CAR_NO NE ' ');
  "<41 Direct Debit Voucher: <DBT_VOUC"
WHEN  DBT_VOUC NE 1;

-4-
SUBFOOT
"<5 NAME : <NAME> STREET : <ADR> </1
 <5 CITY : <ARE_CITY> <74 STATE  : <ARE_STAT> </1"
WHEN ((NAME NE ' ') OR (ADR NE ' ')) AND (CAR_NO NE ' ');

-5-
ON BAN SUBFOOT
"</1 *** Exception Control Account Totals:
<+4 Number of Payments: <60 <ST.LINEX>"
"<42 Total Amount: <55 <ST.PRT_ACTV_AMT>"

But I cannot know if they are syntax error or just copy/paste error.

Also the following will never be true since a P12 cannot be equal to spaces. A numeric field cannot be space, it could be missing, but not space. What you see as space is probably Null value.
-6-
-*  change
DBT_VOUC/P12=IF DBT_VOUC EQ '            ' THEN 1 ELSE DBT_VOUC;
-*  chnage end 


Should be as below for correct syntax but still, I don't know your expected result :
-1-
-* Below is invalid since for DECODE you need other parameter in that function, see manual
PM_DCOD/A8=DECODE PYM_METH(PMSAVE);

-2-
OPEID/A9=IF OPE_ID EQ '000000000' THEN '         ' ELSE  OPE_ID;
-*  change

-3-
SUBFOOT
"<24 Credit Card Type: <SUB_METH Credit Card#: <CAR_NO"
WHEN  (SUB_METH NE ' ') AND (CAR_NO NE ' ');
SUBFOOT
"<41 Direct Debit Voucher: <DBT_VOUC"
WHEN  DBT_VOUC NE 1;

-4-
SUBFOOT
"<5 NAME : <NAME> STREET : <ADR> </1"
 <5 CITY : <ARE_CITY> <74 STATE  : <ARE_STAT> </1"
WHEN ((NAME NE ' ') OR (ADR NE ' ')) AND (CAR_NO NE ' ');

-5-
ON BAN SUBFOOT
"</1 *** Exception Control Account Totals:"
<+4 Number of Payments: <60 <ST.LINEX>"
"<42 Total Amount: <55 <ST.PRT_ACTV_AMT>"

-6-
-*  change
-* You should never change source value except if you are sure that it won't have any impact further on. Define another field.
-* Then you can use this new field AND original field value
DBT_VOUC_X/P12 MISSING ON =IF DBT_VOUC EQ MISSING THEN 1 ELSE DBT_VOUC;
-*  chnage end 


Please open a case with IBI.
It's no longer possible to help you further.
Too many unknown information and cannot run your code without having your data.


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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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     [CLOSED] printing direct-debit voucher =0 which should not be printed

Copyright © 1996-2020 Information Builders