I am getting these error messages below. I create a hold file right before my TABLE request that uses the hold file for the report. I get these errors right at my final request that does the report. The preceding TABLE request that creates the hold file runs fine. Does anyone have any ideas.
(FOC1346) : FORMAT ERROR: Record 1 , Column 32783 (FOC1346) : FORMAT ERROR: Record 1 , Column 32799 (FOC1346) : FORMAT ERROR: Record 1 , Column 65602 (FOC1346) : FORMAT ERROR: Record 1 , Column 65618 (FOC1346) : FORMAT ERROR: Record 1 , Column 65634 (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : TOT_USG (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKDY_USG (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKEND_USG 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0 -EXITThis message has been edited. Last edited by: FP Mod Chuck,
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 14, 2017, 03:02 PM
MartinY
Difficult to say without seeing your code... Please post it
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
July 14, 2017, 03:34 PM
AMC
Code is below.
-SET &ECHO = 'ALL';
-DEFAULT &ST_DT = '05/05/2017;'
-DEFAULT &END_DT = '06/05/2017';
-DEFAULT &METER = '135628301';
-DEFAULT &MIN_KW_THRSH = 1.5;
-*-DEFAULT &MAX_KW_THRSH = 6;
-DEFAULT &KW_CHANNEL = 1;
-DEFAULT &KV_CHANNEL = 1;
-*-DEFAULT &OPP = 3;
-*-DEFAULT &ADJ = 5;
DEFINE FILE MDM_READINGS
-*Convert dates from original Epoch
d_st_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_cvtdate/I8YYMD = HDATE(d_st_date_cvte_hyymd,'YYMD');
d_end_cvtdate/I8YYMD = HDATE(d_end_date_cvte_hyymd,'YYMD');
-*
d_st_date_day/A4 = DOWK(d_st_cvtdate, d_st_date_day);
d_end_date_day/A4 = DOWK(d_end_cvtdate, d_end_date_day);
-*
INTVL_MINS/I3 = (END_DATE_MILLIS - START_DATE_MILLIS) / 60000;
-*Minimum KW Demand Calculation - 1st Part
MIN_THRSH/D12.2 = '&MIN_KW_THRSH';
MIN_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE
IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE
IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE
IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0;
-*
-*MIN_KW_DEM_TEST/D12.2 = IF MIN_KW_DEM GT MIN_THRSH THEN MIN_KW_DEM ELSE 0;
-*
-*Maximum KW Demand (KWpeak) Calculation - 1st Part
MAX_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE
IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE
IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE
IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0;
END
-*Minimum KW Demand Calculation - 2nd Part
TABLE FILE MDM_READINGS
PRINT MIN_KW_DEM
BY LOWEST 1 MIN_KW_DEM NOPRINT
WHERE AMI_METER_ID EQ '&METER';
WHERE d_st_date_cvte_hmdyy GE '&ST_DT';
WHERE d_end_date_cvte_hmdyy LE '&END_DT';
WHERE CHANNEL EQ '&KW_CHANNEL';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
WHERE MIN_KW_DEM GT &MIN_KW_THRSH;
ON TABLE HOLD AS HOLDMIN
END
-RUN
-*Read min demand into amper variable
-SET &MIN_KW_DEM = '';
-BEGIN
-READFILE HOLDMIN
-*-TYPE &MIN_KW_DEM
-DONE
-*Maximum KW Demand Calculation - 2nd Part
TABLE FILE MDM_READINGS
PRINT MAX_KW_DEM
BY HIGHEST 1 MAX_KW_DEM NOPRINT
WHERE AMI_METER_ID EQ '&METER';
WHERE d_st_date_cvte_hmdyy GE '&ST_DT';
WHERE d_end_date_cvte_hmdyy LE '&END_DT';
WHERE CHANNEL EQ '&KW_CHANNEL';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
ON TABLE HOLD AS HOLDMAX
END
-RUN
-*Read max demand into amper variable
-SET &MAX_KW_DEM = '';
-BEGIN
-READFILE HOLDMAX
-TYPE &MAX_KW_DEM
-DONE
DEFINE FILE MDM_READINGS ADD
-*KWH_VAL_2/D12.2 = '&KWH_VAL';
-*KVH_VAL_2/D12.2 = '&KVH_VAL';
-*KWH and KVH Totals
KWH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '&KW_CHANNEL.EVAL' THEN VALUE ELSE 0;
KVH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KV' AND CHANNEL EQ '&KV_CHANNEL.EVAL' THEN VALUE ELSE 0;
-*Load Factor Calculation - Will get from other pieces of the DEFINE
-*D_LF/P33.2 = MAX_KW_DEM_2 * INTVL_HRS;
-*LD_FACT/P33.2 = KWH_VAL_2 / (MAX_KW_DEM_2 * INTVL_HRS);
-*
-*Avg Power Factor Calculation
-*POW_FACT/P33.2 = 1/SQRT(1+(KWH_VAL_2/KVH_VAL_2)*2); -*1st Formula
-*POW_FACT2/P33.2 = KVH_VAL_2/(SQRT(KWH_VALZ_2*2+KVH_VAL_2*2)); -*2nd Formula
-*
-*Lagging or Leading Calculation for Power Factor
-*LAG_LEAD_PF/P33.3 = KWH_VAL_2/(SQRT(KWH_VAL_2*2+KVH_VAL_2*2));
-*LAG_LEAD_TEST/P33.3 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT;
-*
-*Total Usage
TOT_USG/P33.3 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '&KW_CHANNEL.EVAL' THEN VALUE ELSE 0;
-*
-*Weekday Usage
WKDY_USG/P33.3 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '&KW_CHANNEL.EVAL' AND d_st_date_day EQ 'MON' OR 'TUES' OR 'WED' OR 'THU' OR 'FRI')
THEN VALUE ELSE 0;
-*
-*Weekend Usage
WKEND_USG/P33.3 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '&KW_CHANNEL.EVAL' AND d_st_date_day EQ 'SAT' OR 'SUN')
THEN VALUE ELSE 0;
-*
-*Missing Intervals
MISS_INTVL/A3 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) OR (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'NOP' ELSE 'YES';
-*MISS_INTVL/A3 = IF AMI_VENDOR_CODE EQ 'NISC_FAKE_READ' THEN 'NOP' ELSE 'YES';
-*
-*Notes
NOTES/A25 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) THEN 'Channel &KW_CHANNEL partial data' ELSE
IF (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'Channel &KW_CHANNEL partial data' ELSE '';
END
TABLE FILE MDM_READINGS
SUM KVH_VAL
KWH_VAL
START_DATE_MILLIS
END_DATE_MILLIS
d_st_date_cvte_hmdyy
d_end_date_cvte_hmdyy
CHANNEL
UNIT_OF_MEASURE
TOT_USG AS 'Total Usage'
WKDY_USG AS 'Weekday Usage'
WKEND_USG AS 'Weekend Usage'
NOTES AS 'Notes'
BY AMI_METER_ID
-*BY d_st_date_cvtepoch
WHERE AMI_METER_ID EQ '&METER';
WHERE d_st_date_cvte_hmdyy GE '&ST_DT';
WHERE d_end_date_cvte_hmdyy LE '&END_DT';
WHERE CHANNEL EQ '&KW_CHANNEL' OR '&KV_CHANNEL';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
WHERE MISS_INTVL EQ 'YES';
ON TABLE HOLD AS HOLDRPT
END
-RUN
DEFINE FILE HOLDRPT
MIN_KW_DEM_2/D12.2 = '&MIN_KW_DEM';
MAX_KW_DEM_2/D12.2 = '&MAX_KW_DEM';
-*
-*H period
INTVL_HRS/D5.2 = (END_DATE_MILLIS - START_DATE_MILLIS) / 3600000;
-*
-*Load Factor Calculation - Will get from other pieces of the DEFINE
D_LF/P33.2 = MAX_KW_DEM_2 * INTVL_HRS;
LD_FACT/P33.2 = KWH_VAL / (MAX_KW_DEM_2 * INTVL_HRS);
-*
-*Avg Power Factor Calculation
POW_FACT/P33.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); -*1st Formula
POW_FACT2/P33.2 = KVH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2)); -*2nd Formula
-*
-*Lagging or Leading Calculation for Power Factor
LAG_LEAD_PF/P33.3 = KWH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2));
LAG_LEAD_TEST/P33.3 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT;
END
TABLE FILE HOLDRPT
PRINT *
END
-RUN
-EXIT
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
What the above is supposed to do ? Is this a function that you've created ?
Look at each of your fields that have an error and test/verify them and the value that are assigned to them. I don't have your metadata, so you need to look at this.
Also, run only the first TABLE FILE and fix the error that may have at that step and then go to the next step.
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
July 14, 2017, 04:34 PM
Dan Satchell
You might also want to use the WHENCE command to see if you have another HOLDRPT master lurking somewhere in your APP PATH.
WebFOCUS 7.7.05
July 14, 2017, 04:58 PM
AMC
MartinY - Yes, this is a custom function to handle Epoch dates that we have stored in the database.
The fields are being generated in the hold file directly above the request that creates the final report. If I comment out that preceding hold file, the fields generate successfully. Are you referring to that TABLE FILE...one that generates the preceding hold file?
Also, there is not another HOLDRPT master out there. I used the WHENCE command.
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 14, 2017, 05:12 PM
Dan Satchell
See if removing the AS names makes a difference. If you're not using the SET ASNAMES = ON setting, you are probably getting duplicate field names in your HOLD file.
WebFOCUS 7.7.05
July 15, 2017, 12:02 PM
AMC
I am using SET ASNAMES=ON now and I get the same errors. Anymore ideas?
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 16, 2017, 11:20 AM
Dan Satchell
I'm not sure what the limit is on packed (P) fields these days. It used to be 31, so P33.3 may be an issue - although that probably doesn't explain the problem with START_DATE_MILLIS and END_DATE_MILLIS. Also see if adding SET HOLDLIST = PRINTONLY helps.
WebFOCUS 7.7.05
July 16, 2017, 11:21 AM
AMC
MartinY - Did I understand correctly your last comments that you posted about what you suggested I should do from the response that I posted? I want to make sure I understand what you are suggesting.
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 16, 2017, 02:46 PM
AMC
Dan S - I tried both of your suggestions....I changed the P33 to P20 and I used the SET HOLDLIST = PRINTONLY. I get the same format errors. Here is the log file below.
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 SET ASNAMES = ON; -DEFAULT &ST_DT = '05/05/2017'; -DEFAULT &END_DT = '06/05/2017'; -DEFAULT &METER = '135628301'; -DEFAULT &MIN_KW_THRSH = 1.5; -*-DEFAULT &MAX_KW_THRSH = 6; -DEFAULT &KW_CHANNEL = 1; -DEFAULT &KV_CHANNEL = 1; -*-DEFAULT &OPP = 3; -*-DEFAULT &ADJ = 5; DEFINE FILE MDM_READINGS -*Convert dates from original Epoch d_st_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_cvtdate/I8YYMD = HDATE(d_st_date_cvte_hyymd,'YYMD'); d_end_cvtdate/I8YYMD = HDATE(d_end_date_cvte_hyymd,'YYMD'); -* d_st_date_day/A4 = DOWK(d_st_cvtdate, d_st_date_day); d_end_date_day/A4 = DOWK(d_end_cvtdate, d_end_date_day); -* INTVL_MINS/I3 = (END_DATE_MILLIS - START_DATE_MILLIS) / 60000; -*Minimum KW Demand Calculation - 1st Part MIN_THRSH/D12.2 = '1.5'; MIN_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0; -* -*MIN_KW_DEM_TEST/D12.2 = IF MIN_KW_DEM GT MIN_THRSH THEN MIN_KW_DEM ELSE 0; -* -*Maximum KW Demand (KWpeak) Calculation - 1st Part MAX_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0; END -*Minimum KW Demand Calculation - 2nd Part TABLE FILE MDM_READINGS PRINT MIN_KW_DEM BY LOWEST 1 MIN_KW_DEM NOPRINT WHERE AMI_METER_ID EQ '135628301'; WHERE d_st_date_cvte_hmdyy GE '05/05/2017;'; WHERE d_end_date_cvte_hmdyy LE '06/05/2017'; WHERE CHANNEL EQ '1'; WHERE UNIT_OF_MEASURE CONTAINS 'KWH'; WHERE MIN_KW_DEM GT 1.5; ON TABLE HOLD AS HOLDMIN END -RUN (FOC822) VALID VALUES ARE ASNAMES=OFF ON FOCUS MIXED FLIP SUBST 1 0 NUMBER OF RECORDS IN TABLE= 30 LINES= 1 -*Read min demand into amper variable -SET &MIN_KW_DEM = ''; -BEGIN (FOC2906) WARNING: &MIN_KW_DEM WILL BE OVERWRITTEN BY -READFILE HOLDMIN -READFILE HOLDMIN -*-TYPE &MIN_KW_DEM -DONE -*Maximum KW Demand Calculation - 2nd Part TABLE FILE MDM_READINGS PRINT MAX_KW_DEM BY HIGHEST 1 MAX_KW_DEM NOPRINT WHERE AMI_METER_ID EQ '135628301'; WHERE d_st_date_cvte_hmdyy GE '05/05/2017;'; WHERE d_end_date_cvte_hmdyy LE '06/05/2017'; WHERE CHANNEL EQ '1'; WHERE UNIT_OF_MEASURE CONTAINS 'KWH'; ON TABLE HOLD AS HOLDMAX END -RUN 1 0 NUMBER OF RECORDS IN TABLE= 48 LINES= 1 -*Read max demand into amper variable -SET &MAX_KW_DEM = ''; -BEGIN (FOC2906) WARNING: &MAX_KW_DEM WILL BE OVERWRITTEN BY -READFILE HOLDMAX -READFILE HOLDMAX -TYPE 4.03 4.03 -DONE DEFINE FILE MDM_READINGS ADD -*KWH_VAL_2/D12.2 = '&KWH_VAL'; -*KVH_VAL_2/D12.2 = '&KVH_VAL'; -*KWH and KVH Totals KWH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' THEN VALUE ELSE 0; KVH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KV' AND CHANNEL EQ '1' THEN VALUE ELSE 0; -*Load Factor Calculation - Will get from other pieces of the DEFINE -*D_LF/P20.2 = MAX_KW_DEM_2 * INTVL_HRS; -*LD_FACT/P20.2 = KWH_VAL_2 / (MAX_KW_DEM_2 * INTVL_HRS); -* -*Avg Power Factor Calculation -*POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL_2/KVH_VAL_2)*2); -*1st Formula -*POW_FACT2/P20.2 = KVH_VAL_2/(SQRT(KWH_VALZ_2*2+KVH_VAL_2*2)); -*2nd Formula -* -*Lagging or Leading Calculation for Power Factor -*LAG_LEAD_PF/P20.2 = KWH_VAL_2/(SQRT(KWH_VAL_2*2+KVH_VAL_2*2)); -*LAG_LEAD_TEST/P20.2 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT; -* -*Total Usage TOT_USG/P20.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' THEN VALUE ELSE 0; -* -*Weekday Usage WKDY_USG/P20.2 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' AND d_st_date_day EQ 'MON' OR 'TUES' OR 'WED' OR 'THU' OR 'FRI') THEN VALUE ELSE 0; -* -*Weekend Usage WKEND_USG/P20.2 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' AND d_st_date_day EQ 'SAT' OR 'SUN') THEN VALUE ELSE 0; -* -*Missing Intervals MISS_INTVL/A3 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) OR (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'NOP' ELSE 'YES'; -*MISS_INTVL/A3 = IF AMI_VENDOR_CODE EQ 'NISC_FAKE_READ' THEN 'NOP' ELSE 'YES'; -* -*Notes NOTES/A25 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) THEN 'Channel 1 partial data' ELSE IF (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'Channel 1 partial data' ELSE ''; END TABLE FILE MDM_READINGS SUM KVH_VAL KWH_VAL START_DATE_MILLIS END_DATE_MILLIS d_st_date_cvte_hmdyy d_end_date_cvte_hmdyy CHANNEL UNIT_OF_MEASURE TOT_USG WKDY_USG WKEND_USG NOTES BY AMI_METER_ID -*BY d_st_date_cvtepoch WHERE AMI_METER_ID EQ '135628301'; WHERE d_st_date_cvte_hmdyy GE '05/05/2017;'; WHERE d_end_date_cvte_hmdyy LE '06/05/2017'; WHERE CHANNEL EQ '1' OR '1'; WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV'; WHERE MISS_INTVL EQ 'YES'; ON TABLE HOLD AS HOLDRPT END -RUN 0 NUMBER OF RECORDS IN TABLE= 48 LINES= 1 DEFINE FILE HOLDRPT MIN_KW_DEM_2/D12.2 = ' 1.59 '; MAX_KW_DEM_2/D12.2 = ' 4.03 '; -* -*H period INTVL_HRS/D6.2 = (END_DATE_MILLIS - START_DATE_MILLIS) / 3600000; -* -*Load Factor Calculation - Will get from other pieces of the DEFINE D_LF/P20.2 = MAX_KW_DEM_2 * INTVL_HRS; LD_FACT/P20.2 = KWH_VAL / (MAX_KW_DEM_2 * INTVL_HRS); -* -*Avg Power Factor Calculation POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); -*1st Formula POW_FACT2/P20.2 = KVH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2)); -*2nd Formula -* -*Lagging or Leading Calculation for Power Factor LAG_LEAD_PF/P20.2 = KWH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2)); LAG_LEAD_TEST/P20.2 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT; END SET HOLDLIST = PRINTONLY; TABLE FILE HOLDRPT PRINT KWH_VAL KVH_VAL INTVL_HRS MIN_KW_DEM_2 AS 'Minimum kW Demand' MAX_KW_DEM_2 AS 'Maximum kW Demand' LD_FACT AS 'Load Factor' LAG_LEAD_TEST AS 'Average Power Factor' TOT_USG AS 'Total Usage' WKDY_USG AS 'Weekday Usage' WKEND_USG AS 'Weekend Usage' BY AMI_METER_ID END -RUN (FOC822) VALID VALUES ARE HOLDLIST=ALL ALLKEYS PRINTONLY NOFOCLIST EXPLICIT (FOC1346) : FORMAT ERROR: Record 1 , Column 32783 (FOC1346) : FORMAT ERROR: Record 1 , Column 32799 (FOC1346) : FORMAT ERROR: Record 1 , Column 65602 (FOC1346) : FORMAT ERROR: Record 1 , Column 65618 (FOC1346) : FORMAT ERROR: Record 1 , Column 65634 (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : TOT_USG (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKDY_USG (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKEND_USG 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0 -EXIT
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 16, 2017, 05:31 PM
Dan Satchell
You're getting an error on the SET ASNAMES command:
(FOC822) VALID VALUES ARE ASNAMES=OFF ON FOCUS MIXED FLIP SUBST
It's not working because you have a semi-colon at the end. Semi-colons are used to terminate -SET commands, but not plain SET commands.This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
July 17, 2017, 08:22 AM
MartinY
AMC,
quote:
MartinY - Did I understand correctly your last comments that you posted about what you suggested I should do from the response that I posted? I want to make sure I understand what you are suggesting.
My point was to execute only the first step of your code and look at the result since it's the first place where START_DATE_MILLIS and END_DATE_MILLIS are used. Also take a special attention to you date function. Be sure that it work properly. Then go step by step until you have the error. But, according to your previous post, it seems to work ok.
quote:
(FOC1346) : FORMAT ERROR: Record 1 , Column 32783 (FOC1346) : FORMAT ERROR: Record 1 , Column 32799 (FOC1346) : FORMAT ERROR: Record 1 , Column 65602 (FOC1346) : FORMAT ERROR: Record 1 , Column 65618 (FOC1346) : FORMAT ERROR: Record 1 , Column 65634 (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : TOT_USG (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKDY_USG (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKEND_USG 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
As for the above I would also take a look at the source data itself using another tool than Focus if possible. It is possible that your data is corrupted.
Since you are doing
TABLE FILE MDM_READINGS
PRINT MIN_KW_DEM
BY LOWEST 1 MIN_KW_DEM NOPRINT
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '05/05/2017;';
WHERE d_end_date_cvte_hmdyy LE '06/05/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
WHERE MIN_KW_DEM GT 1.5;
What I would do is the below and take a look at the data :
TABLE FILE MDM_READINGS
PRINT START_DATE_MILLIS
END_DATE_MILLIS
VALUE
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '05/05/2017;';
WHERE d_end_date_cvte_hmdyy LE '06/05/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH';
WHERE MIN_KW_DEM GT 1.5;
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT
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
July 17, 2017, 03:50 PM
AMC
Dan S - Oops...I forgot I added the semi-colon to the end by mistake to the SET command...going to fast between SETs and -SETs and got crossed up there. I removed it.
MarinY - So for this single query below that you said to take a look at to see the data, I get zero records returned. Here's the query with the Defines for the dates. I get no records returned. As you suggest, I also ran the SQL query version of this in my SQL tool DBVis amd I get no records. I may have expand my date range or something.
What's odd though is that I get records from the step right before this one, which has essentially the same filters where I am doing a SUM.
DEFINE FILE MDM_READINGS
-*Convert dates from original Epoch
d_st_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS );
d_end_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS );
-*
d_st_cvtdate/I8YYMD = HDATE(d_st_date_cvte_hyymd,'YYMD');
d_end_cvtdate/I8YYMD = HDATE(d_end_date_cvte_hyymd,'YYMD');
-*
d_st_date_day/A4 = DOWK(d_st_cvtdate, d_st_date_day);
d_end_date_day/A4 = DOWK(d_end_cvtdate, d_end_date_day);
END
TABLE FILE MDM_READINGS
PRINT START_DATE_MILLIS
END_DATE_MILLIS
VALUE
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '05/05/2017;';
WHERE d_end_date_cvte_hmdyy LE '06/05/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
-*WHERE MIN_KW_DEM GT 1.5;
WHERE VALUE GT 1.5;
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 17, 2017, 05:08 PM
AMC
So I changed my date range to a range that I know I should be getting records for from the database, but I get the same format errors. Also, my query right above that step indicates that it has records and it will generate record successfully when I bring back to screen. Any more ideas?
(FOC1346) : FORMAT ERROR: Record 1 , Column 32783 (FOC1346) : FORMAT ERROR: Record 1 , Column 32799 (FOC1346) : FORMAT ERROR: Record 1 , Column 65602 (FOC1346) : FORMAT ERROR: Record 1 , Column 65618 (FOC1346) : FORMAT ERROR: Record 1 , Column 65634 (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : TOT_USG (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKDY_USG (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : WKEND_USG 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0 -EXIT
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 17, 2017, 05:29 PM
AMC
Also, if I run the single query by itself, with a different date range for which I know records exists, it runs successfully and returns the records.
TABLE FILE MDM_READINGS
PRINT START_DATE_MILLIS
END_DATE_MILLIS
VALUE
WHERE AMI_METER_ID EQ '135628301';
WHERE d_st_date_cvte_hmdyy GE '02/01/2017;';
WHERE d_end_date_cvte_hmdyy LE '02/04/2017';
WHERE CHANNEL EQ '1';
WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV';
-*WHERE MIN_KW_DEM GT 1.5;
WHERE VALUE GT 1.5;
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 18, 2017, 04:16 AM
Frans
My 2 cents:
I'm not sure commenting like this works:
POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); -*1st Formula
I would put it like this:
-*1st Formula
POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2);
Second, because you do not specify a HOLD format, due to your config it might be storing it as ALPHA and if a delimiter is in your data it can cause problems. Try
I'm not sure commenting like this works:POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); -*1st Formula
Frans,
This style of commenting works perfectly OK and has been acceptable since 8.1.something (I think, may have been earlier?)
@ AMC,
I think this post goes to show that it is always worth breaking down the component parts of your code for testing / debugging
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
July 18, 2017, 03:29 PM
AMC
Francs - I tried the FORMAT BINARY and it did not work.
I have resolved all of the FORMAT errors but two of them below.
(FOC1346) : FORMAT ERROR: Record 1 , Column 32783 (FOC1346) : FORMAT ERROR: Record 1 , Column 32799 (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0 -EXIT
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 19, 2017, 03:52 AM
Tony A
quote:
(FOC1346) : FORMAT ERROR: Record 1 , Column 32783 (FOC1346) : FORMAT ERROR: Record 1 , Column 32799 (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : START_DATE_MILLIS (FOC1130) FORMAT CONVERSION ERROR FIELD/KEY : END_DATE_MILLIS
Have you trapped your output and opened it up in a text editor? Try using HOLD FORMAT ALPHA to enable you to actually see numerics, dates etc.
Actually checking the data that errors should be your first port of call. Knowing the attributes of your data and the values that are actually being rejected will often identify the error to you.
Is it possible that your date(time) fields are NULL? Try using SET HOLDMISS = ON and SET HOLDATTR = ON to transfer MISSING=ON and other attributes to your hold file.
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
July 19, 2017, 12:12 PM
AMC
Tony A - I inspected the date-time fields (which they are epoch dates that are stored as integer fields in the database) within the data set at hand and I don't see any nulls or anything odd.
Well I decided to take the route of reading my epoch date values into amper variables and Define them in the following HOLD file (HOLDRPT). I get this error on my last TABLE request that does the report: (FOC1065) PROGRAM ERROR
Here is the log now:
0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 SET ASNAMES = ON -DEFAULT &ST_DT = '02/01/2017'; -DEFAULT &END_DT = '02/04/2017'; -DEFAULT &METER = '135628301'; -DEFAULT &MIN_KW_THRSH = 1.5; -*-DEFAULT &MAX_KW_THRSH = 6; -DEFAULT &KW_CHANNEL = 1; -DEFAULT &KV_CHANNEL = 1; -*-DEFAULT &OPP = 3; -*-DEFAULT &ADJ = 5; DEFINE FILE MDM_READINGS -*Convert dates from original Epoch d_st_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvtepoch/HMDYYS=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvte_hmdyy/HMDYY=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvte_hyymd/HYYMD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvte_hi/HI=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.START_DATE_MILLIS ); d_end_date_cvte_hd/HD=DF.convertepochvalues.EpochToDateTime1 ( MDM_READINGS.MDM_READINGS.END_DATE_MILLIS ); -* d_st_cvtdate/I8YYMD = HDATE(d_st_date_cvte_hyymd,'YYMD'); d_end_cvtdate/I8YYMD = HDATE(d_end_date_cvte_hyymd,'YYMD'); -* d_st_date_day/A4 = DOWK(d_st_cvtdate, d_st_date_day); d_end_date_day/A4 = DOWK(d_end_cvtdate, d_end_date_day); -* INTVL_MINS/I20 = (END_DATE_MILLIS - START_DATE_MILLIS) / 60000; -*Minimum KW Demand Calculation - 1st Part MIN_THRSH/D12.2 = '1.5'; MIN_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0; -* -*MIN_KW_DEM_TEST/D12.2 = IF MIN_KW_DEM GT MIN_THRSH THEN MIN_KW_DEM ELSE 0; -* -*Maximum KW Demand (KWpeak) Calculation - 1st Part MAX_KW_DEM/D12.2 = IF INTVL_MINS EQ 5 THEN (VALUE * 12) ELSE IF INTVL_MINS EQ 15 THEN (VALUE * 4) ELSE IF INTVL_MINS EQ 30 THEN (VALUE * 2) ELSE IF INTVL_MINS EQ 60 THEN (VALUE * 1) ELSE 0; END -*Minimum KW Demand Calculation - 2nd Part TABLE FILE MDM_READINGS PRINT MIN_KW_DEM BY LOWEST 1 MIN_KW_DEM NOPRINT WHERE AMI_METER_ID EQ '135628301'; WHERE d_st_date_cvte_hmdyy GE '02/01/2017'; WHERE d_end_date_cvte_hmdyy LE '02/04/2017'; WHERE CHANNEL EQ '1'; WHERE UNIT_OF_MEASURE CONTAINS 'KWH'; WHERE MIN_KW_DEM GT 1.5; ON TABLE HOLD AS HOLDMIN END -RUN 1 0 NUMBER OF RECORDS IN TABLE= 67 LINES= 2 -*Read min demand into amper variable -SET &MIN_KW_DEM = ''; -BEGIN (FOC2906) WARNING: &MIN_KW_DEM WILL BE OVERWRITTEN BY -READFILE HOLDMIN -READFILE HOLDMIN -*-TYPE &MIN_KW_DEM -DONE -*Maximum KW Demand Calculation - 2nd Part TABLE FILE MDM_READINGS PRINT MAX_KW_DEM BY HIGHEST 1 MAX_KW_DEM NOPRINT WHERE AMI_METER_ID EQ '135628301'; WHERE d_st_date_cvte_hmdyy GE '02/01/2017'; WHERE d_end_date_cvte_hmdyy LE '02/04/2017'; WHERE CHANNEL EQ '1'; WHERE UNIT_OF_MEASURE CONTAINS 'KWH'; ON TABLE HOLD AS HOLDMAX END -RUN 1 0 NUMBER OF RECORDS IN TABLE= 267 LINES= 1 -*Read max demand into amper variable -SET &MAX_KW_DEM = ''; -BEGIN (FOC2906) WARNING: &MAX_KW_DEM WILL BE OVERWRITTEN BY -READFILE HOLDMAX -READFILE HOLDMAX -TYPE 10.86 10.86 -DONE DEFINE FILE MDM_READINGS ADD -*KWH_VAL_2/D12.2 = '&KWH_VAL'; -*KVH_VAL_2/D12.2 = '&KVH_VAL'; -*KWH and KVH Totals KWH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' THEN VALUE ELSE 0; KVH_VAL/D12.2 = IF UNIT_OF_MEASURE CONTAINS 'KV' AND CHANNEL EQ '1' THEN VALUE ELSE 0; -*Load Factor Calculation - Will get from other pieces of the DEFINE -*D_LF/P20.2 = MAX_KW_DEM_2 * INTVL_HRS; -*LD_FACT/P20.2 = KWH_VAL_2 / (MAX_KW_DEM_2 * INTVL_HRS); -* -*Avg Power Factor Calculation -*POW_FACT/P20.2 = 1/SQRT(1+(KWH_VAL_2/KVH_VAL_2)*2); -*1st Formula -*POW_FACT2/P20.2 = KVH_VAL_2/(SQRT(KWH_VALZ_2*2+KVH_VAL_2*2)); -*2nd Formula -* -*Lagging or Leading Calculation for Power Factor -*LAG_LEAD_PF/P20.2 = KWH_VAL_2/(SQRT(KWH_VAL_2*2+KVH_VAL_2*2)); -*LAG_LEAD_TEST/P20.2 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT; -* -*Total Usage TOT_USG/D20.2 = IF UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' THEN VALUE ELSE 0; -* -*Weekday Usage WKDY_USG/D20.2 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' AND d_st_date_day EQ 'MON' OR 'TUES' OR 'WED' OR 'THU' OR 'FRI') THEN VALUE ELSE 0; -* -*Weekend Usage WKEND_USG/D20.2 = IF (UNIT_OF_MEASURE CONTAINS 'KWH' AND CHANNEL EQ '1' AND d_st_date_day EQ 'SAT' OR 'SUN') THEN VALUE ELSE 0; -* -*Missing Intervals MISS_INTVL/A3 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) OR (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'NOP' ELSE 'YES'; -*MISS_INTVL/A3 = IF AMI_VENDOR_CODE EQ 'NISC_FAKE_READ' THEN 'NOP' ELSE 'YES'; -* -*Notes NOTES/A25 = IF (KWH_VAL EQ MISSING AND KVH_VAL NE MISSING) THEN 'Channel 1 partial data' ELSE IF (KWH_VAL NE MISSING AND KVH_VAL EQ MISSING) THEN 'Channel 1 partial data' ELSE ''; -*START_DATE_MILLIS/P39 = START_DATE_MILLIS; -*END_DATE_MILLIS/P39 = END_DATE_MILLIS; END SET MISSING = ON TABLE FILE MDM_READINGS SUM KVH_VAL KWH_VAL -* START_DATE_MILLIS -* END_DATE_MILLIS d_st_date_cvte_hmdyy d_end_date_cvte_hmdyy CHANNEL UNIT_OF_MEASURE TOT_USG WKDY_USG WKEND_USG NOTES BY AMI_METER_ID -*BY d_st_date_cvtepoch WHERE AMI_METER_ID EQ '135628301'; WHERE d_st_date_cvte_hmdyy GE '02/01/2017'; WHERE d_end_date_cvte_hmdyy LE '02/04/2017'; WHERE CHANNEL EQ '1' OR '1'; WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV'; WHERE MISS_INTVL EQ 'YES'; ON TABLE HOLD AS HOLDRPT FORMAT ALPHA END -RUN (FOC822) VALID VALUES ARE MISSINGTEST=OLD SPECIAL 0 NUMBER OF RECORDS IN TABLE= 267 LINES= 1 -*Hold epoch dates TABLE FILE MDM_READINGS SUM START_DATE_MILLIS END_DATE_MILLIS BY AMI_METER_ID NOPRINT WHERE AMI_METER_ID EQ '135628301'; WHERE d_st_date_cvte_hmdyy GE '02/01/2017'; WHERE d_end_date_cvte_hmdyy LE '02/04/2017'; WHERE CHANNEL EQ '1' OR '1'; WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV'; -*WHERE MISS_INTVL EQ 'YES'; ON TABLE HOLD AS HOLDDTS END -RUN 0 NUMBER OF RECORDS IN TABLE= 267 LINES= 1 -*Read epoch dates into amper variables -SET &START_DATE_MILLIS = ''; -SET &END_DATE_MILLIS = ''; -BEGIN (FOC2906) WARNING: &START_DATE_MILLIS WILL BE OVERWRITTEN BY -READFILE HOLDDTS (FOC2906) WARNING: &END_DATE_MILLIS WILL BE OVERWRITTEN BY -READFILE HOLDDTS -READFILE HOLDDTS -TYPE 396774493200000 396774493200000 -TYPE 396774730800000 396774730800000 -DONE -RUN DEFINE FILE HOLDRPT MIN_KW_DEM_2/D20.2 = 1.56 ; MAX_KW_DEM_2/D20.2 = 10.86 ; START_DATE_MILLIS/P33 = 396774493200000; END_DATE_MILLIS/P33 = 396774730800000; -* -*D_START_DATE_MILLIS/I20 = EDIT(START_DATE_MILLIS); -*D_END_DATE_MILLIS/I20 = EDIT(END_DATE_MILLIS); -*H period INTVL_HRS/I20 = (END_DATE_MILLIS - START_DATE_MILLIS) / 3600000; -*INTVL_HRS/I20 = (D_END_DATE_MILLIS - D_START_DATE_MILLIS) / 3600000; -*INTVL_HRS/I20 = (8000000000 - 4000000000) / 3600000; -* -*Load Factor Calculation - Will get from other pieces of the DEFINE D_LF/D20.2 = MAX_KW_DEM_2 * INTVL_HRS; LD_FACT/D20.2 = KWH_VAL / (MAX_KW_DEM_2 * INTVL_HRS); -* -*Avg Power Factor Calculation POW_FACT/D20.2 = 1/SQRT(1+(KWH_VAL/KVH_VAL)*2); -*1st Formula POW_FACT2/D20.2 = KVH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2)); -*2nd Formula -* -*Lagging or Leading Calculation for Power Factor LAG_LEAD_PF/D20.2 = KWH_VAL/(SQRT(KWH_VAL*2+KVH_VAL*2)); LAG_LEAD_TEST/D20.2 = IF LAG_LEAD GE 0 THEN (POW_FACT * -1) ELSE POW_FACT; END TABLE FILE HOLDRPT HEADING CENTER "Min Max Summary" PRINT KVH_VAL KWH_VAL INTVL_HRS D_LF MIN_KW_DEM_2 AS 'Minimum kW Demand' MAX_KW_DEM_2 AS 'Maximum kW Demand' LD_FACT AS 'Load Factor' LAG_LEAD_TEST AS 'Average Power Factor' TOT_USG AS 'Total Usage' WKDY_USG AS 'Weekday Usage' WKEND_USG AS 'Weekend Usage' NOTES AS 'Notes' BY AMI_METER_ID -*BY d_st_date_cvtepoch -*WHERE AMI_METER_ID EQ '&METER'; -*WHERE d_st_date_cvte_hmdyy GE '&ST_DT'; -*WHERE d_end_date_cvte_hmdyy LE '&END_DT'; -*WHERE CHANNEL EQ '&KW_CHANNEL' OR '&KV_CHANNEL'; -*WHERE UNIT_OF_MEASURE CONTAINS 'KWH' OR 'KV'; -*WHERE MISS_INTVL EQ 'YES'; ON TABLE PCHOLD FORMAT AHTML ON TABLE NOTOTAL ON TABLE SET AUTOFIT OFF ON TABLE SET CACHELINES 100 ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET SQUEEZE ON ON TABLE SET HTMLCSS ON ON TABLE SET HTMLENCODE ON ON TABLE SET EMPTYREPORT ON ON TABLE SET WEBVIEWTARG OFF ON TABLE SET WEBVIEWER ON ON TABLE SET GRWIDTH 1 ON TABLE SET STYLE * ENDSTYLE END -RUN (FOC1065) PROGRAM ERROR -EXIT
WF 8.1.05 Windows SQL Server 2014 HTML, PDF, EXCEL
July 28, 2017, 10:46 AM
FP Mod Chuck
Hi AMC
I think you should open a case with techsupport on this one...
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats