Focal Point
Drill Down : A FIELD FORMAT IS NOT ALLOWED IN -SET: &AL''0=2;

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7321097331

July 27, 2004, 12:55 PM
<sully>
Drill Down : A FIELD FORMAT IS NOT ALLOWED IN -SET: &amp;AL''0=2;
This is the error whilst attmpting to run a drill down report. The &AL is the value of one of the parameters passed to the drill report.
Webfocus 5.2.
Does anyone have an idea what is causing this?
July 27, 2004, 02:31 PM
Stan
This is just a hunch, but you cannot set a variable equal to a field format..for example in a define, you can't say

NEWFIELD/A9 = SUBSTR(9,INFIELD,1,9,9,&FMT);

In that case the variable &FMT is supposed to be a field format and it is not allowed to be a variable.

My hunch in your case is because you are using two letters that can be a format field (A=alpha and L is for leading zeros) that it is possible you are having troubles because it thinks that amper variable you have passed is a field format instead of a text or numeric field. Why don't you try renaming the variable something else and use three different letters and see if it will work
July 27, 2004, 02:34 PM
<WFUser>
That error is somtimes caused by some other syntax error that confuses WebFOCUS. Can you post your code?
July 27, 2004, 04:26 PM
<sully>
Thanks

Here is the code-:
---------------------------------------
SET TRACEOFF = ALL;
SET TRACEON = SQLAGGR//CLIENT;
SET TRACEON = STMTRACE//CLIENT;
SET TRACEON = STMTRACE/2/CLIENT;
SET TRACEUSER = ON;

-TYPE '&STATE'

DEFINE FILE PAYMTBRD
TEMPA/YYMD = 'STARTDATE'
TEMPB/YYMD = 'ENDDATE'
STARTDATE/YYMD = TEMPA;
ENDDATE/YYMD = TEMPB;
SORTDATE/MtrYY = PBR_PAYMENT_DT;
PERCENT/F7.3 = PBR_PAYMENT_CNT;
END

TABLE FILE PAYMTBRD

-SET PAYD3=''
-SET PAYD4=''
-SET PAYD5=''
-SET PAYD6=''
-SET PAYD7=''
-SET PAYD8=''
-SET PAYD9=''
-SET GRP1='State'
-SET GRP2=''
-SET GRP3=''
-SET GRP4=''
-SET GRP1X=''
-SET GRP2X=''
-SET GRP3X=''
-SET GRP4X=''
-SET STDATE='2004-03-01'
-SET EDDATE='2004-04-01'
-SET PAYONE=''
-SET PAYTWO=''
-SET PAYTHREE=''
-SET GRP1TOT=''
-SET GRP2TOT=''
-SET GRPT3TOT=''
-SET GRP4TOT=''
-SET AMTPAID=''
-SET GRPDESC=''
-SET STATUSD=''
-SET PAYGRP=''
-SET PAYLSTGRP=''
-SET GRP3TOT=''
-SET TENURETYPE=''
-SET LASTGRP=''
-SET AMTPDDESC=''
-SET STATE1='CA'
-SET STATE2=''
-SET STATE3=''
-SET STATE4=''
-SET STATE='CA'


HEADING CENTER
"Payment Snapshot Detail"
"Filtering Criteria"
"Group by &GRPDESC"
"State: '&STATE1' "
-IF '&STATE2' EQ '' THEN GOTO HEAD01
",'&STATE2' "
-IF '&STATE3' EQ '' THEN GOTO HEAD01
",'&STATE3' "
-IF '&STATE4' EQ '' THEN GOTO HEAD01
",'&STATE4' "

-HEAD01
"Policy Status: &STATUSD"
"Payment Method: &PAYD1"
-IF &PAYD2 EQ '' THEN GOTO PAY01
"&PAYD2"
-IF &PAYD3 EQ '' THEN GOTO PAY01
"&PAYD3"
-IF &PAYD4 EQ '' THEN GOTO PAY01
"&PAYD4"
-IF &PAYD5 EQ '' THEN GOTO PAY01
"&PAYD5"
-IF &PAYD6 EQ '' THEN GOTO PAY01
"&PAYD6"
-IF &PAYD7 EQ '' THEN GOTO PAY01
"&PAYD7"
-IF &PAYD8 EQ '' THEN GOTO PAY01
"&PAYD8"
-IF &PAYD9 EQ '' THEN GOTO PAY01
"&PAYD9"
-PAY01

"Amount Paid: &AMTPDDESC"
"Date Range: STDATE - &EDDATE </1"

SUM
PBR_PAYMENT_CNT AS 'Policy Count'
PBR_PAYMENT_AMT AS 'Total Premium'
COMPUTE
buffer1/D7.2 = SUM.PBR_PAYMENT_CNT;
Distribution/D7.1 = (PBR_PAYMENT_CNT/buffer1) * 100.0;
AS 'Variance from, due date'
-IF '&PAYGRP' EQ '' OR '&PAYLSTGRP' EQ 'false' THEN GOTO SKP01

PCT.PERCENT/F7.1 WITHIN 'LASTGRP'
AS '% Payment,Method'
-SKP01
-IF &GRP1 NE 'PayMethod' OR &LASTGRP NE 'PayMethodOnly'
-THEN GOTO SKP02
PCT.PERCENT/F7.1 AS '% Pay Method'
-SKP02

BY &GRP1
ON &GRP1 SUMMARIZE MULTILINES
AS &GRP1TOT
-IF &GRP2 EQ '' GOTO XXX01
BY &GRP2
ON &GRP2 SUMMARIZE MULTILINES
AS &GRP2TOT
-XXX01
-IF &GRP3 EQ '' GOTO XXX02
BY &GRP3
ON &GRP3 SUMMARIZE MULTILINES
AS &GRP3TOT
-XXX02
-IF &GRP4 EQ '' GOTO XXX03
BY &GRP4
ON &GRP4 SUMMARIZE MULTILINES
AS &GRP4TOT
-XXX03

-IF STARTDATE EQ &ENDDATE THEN GOTO XXX04

WHERE PBR_PAYMENT_DT GE STARTDATE AND PBR_PAYMENT_DT LE &ENDDATE
-GOTO XXX05;

-XXX04
WHERE PBR_PAYMENT_DT EQ STARTDATE;
-XXX05
-IF 'STATE' EQ 'ALL' THEN GOTO SQL2
WHERE PBR_STATE_CD IN
('X', '&STATE1')
-IF 'STATE2' EQ '' THEN GOTO SQL2
OR PBR_STATE_CD IN
('X', '&STATE2')
-IF 'STATE3' EQ '' THEN GOTO SQL2
OR PBR_STATE_CD IN
('X', '&STATE3')
-IF 'STATE4' EQ '' THEN GOTO SQL2
OR PBR_STATE_CD IN
('X', '&STATE4')
-SQL2
-IF STATUS EQ 'ALL' THEN GOTO SQL5
WHERE PBR_STS_AT_PAY_CD IN
('X', '&STATUS')
-SQL5
-IF PAYONE EQ 'ALL' THEN GOTO SQL6
WHERE PBR_POST_SUB_CD IN
('X', '&PAYONE')

-IF PAYTWO EQ '' THEN GOTO SQL6
OR PBR_POST_SUB_CD IN
('X', '&PAYTWO')
-IF PAYTHREE EQ '' THEN GOTO SQL6
OR PBR_POST_SUB_CD IN
('X', '&PAYTHREE')
-SQL6
-IF TENURETYPE EQ 'ALL' THEN GOTO SQL8
WHERE PBR_TENURE_RG_CD IN
('X', '&TENURETYPE')
-SQL8
-IF AMTPAID EQ 'ALL' THEN GOTO SQL12
WHERE PBR_AMT_VAR_RG_CD IN
('X', '&AMTPAID')

-SQL12
ACROSS PBR_DAYS_VAR_RG_CD

ON TABLE SET STYLE *
TYPE=REPORT, GRID=ON, $
TYPE=HEADING, COLOR=BLACK, STYLE=BOLD, $
TYPE=TITLE, COLOR=NAVY, STYLE=BOLD, $
TYPE=DATA, COLUMN=N1, STYLE=BOLD, $
TYPE=ACROSSTITLE, ACROSS=1, COLOR=NAVY, STYLE=BOLD, $
TYPE=ACROSSVALUE, ACROSS=1, STYLE=BOLD, $
ENDSTYLE
END


-* Line 207
-IF (LINES LE 0) GOTO NODATAA
-EXIT

-NODATAA
TABLE FILE PAYMTBRD
HEADING CENTER
"" PLEASE TRY ANOTHER SELECTION."

WHERE RECORDLIMIT EQ 1
ON TABLE SET PAGE-NUM OFF
ON TABLE SET EMPTYREPORT ON
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, SIZE=10, JUSTIFY=CENTER, SQUEEZE=OFF, $
TYPE=HEADING, GRID=OFF, SIZE=10, COLOR=NAVY, STYLE=BOLD, JUSTIFY=CENTER, $
ENDSTYLE
END
-EXIT
July 27, 2004, 04:44 PM
<monte2000>
Sully's message was originally sent as a moderator alert, i.e., "Report This Post". The "Report This Post" feature is only for reporting potentially objectionable content. Alerts sent for any other reason will be posted to the board.

Thanks for participating in the forum!

Monica
July 29, 2004, 02:11 AM
<Kyle>
I've seen this error when you try to drill down on a field that has single quotes in it or when you try to pass a value to the drill down with single quotes in it.
July 29, 2004, 02:43 PM
<Mike Miles>
According to the FOCUS manual all -SET commands must be terminated with a semi-colon. You also have what appears to be an extra double quote in the heading line of your report.
July 29, 2004, 02:44 PM
<Mike Miles>
The extra double quote is in the NODATA section at the bottom of the FOCEXEC.