July 27, 2004, 02:31 PM
StanThis 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, 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
"1 There is no data based on the selections made: 2"
" 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