All, I want to retrieve the previous month's data. The column that I must use is called DATE_TIME and is in the HYYMDs format. The following script
-SET &ECHO=ALL;
-* store the last month's current date in &DATETMP parameter
-SET &DATETMP = DATECVT((DATEADD((DATECVT(&YYMD, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
-TYPE &DATETMP
-* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
-SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(&DATETMP,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-TYPE &MON_BEG_DT2
-* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
-SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(&DATETMP,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE &MON_END_DT2
-* append time
-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') || '00.00.00.000000';
-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') || '23.59.59.999999';
-TYPE &FR_DT
-TYPE &TO_DT
TABLE FILE MY_FILE
PRINT
USER_ID
DATE_TIME
BYTES_SENT
BYTES_RECEIVED
BY USER_ID
WHERE DATE_TIME FROM DT(&FR_DT) TO DT(&TO_DT);
WHERE READLIMIT EQ 10
ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
END
results the following error message.
-* store the last month's current date in &DATETMP parameter
-SET &DATETMP = DATECVT((DATEADD((DATECVT(20101206, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
-TYPE 20101106
20101106
-* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
-SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-TYPE 20101101
20101101
-* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
-SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE 20101130
20101130
-SET &FR_DT = EDIT(20101101,'99999999') || '00.00.00.000000';
-SET &TO_DT = EDIT(20101130,'99999999') || '23.59.59.999999';
-TYPE 2010110100.00.00.000000
2010110100.00.00.000000
-TYPE 2010113023.59.59.999999
2010113023.59.59.999999
TABLE FILE MY_FILE
PRINT
USER_ID
DATE_TIME
BYTES_SENT
BYTES_RECEIVED
BY USER_ID
WHERE DATE_TIME FROM DT(2010110100.00.00.000000) TO DT(2010113023.59.59.999999);
WHERE READLIMIT EQ 10
ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
END
0 ERROR AT OR NEAR LINE 33 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
Here, I tried to use the DT function to change the format of the date, but was not successful. I would prefer to have the format changed in the Dialogue Manager. What is the best way to change the format of &FR_DT and &TO_DT to 'HYYMDS' to successfully compare them against DATE_TIME column?
Thank you for your help,
SeyedThis message has been edited. Last edited by: SeyedG,
WebFOCUS 8.0.09 App Studio 8009 Linux Kernel-2.6 DBMS: Oracle 11g all output (Excel, HTML, AHTML, PDF)
December 06, 2010, 04:49 PM
Waz
Make sure that there is a space between the date and time, and a colon between the hours and minutes, and minutes and seconds.
Waz, I appreciate you help with this. I followed your advice, but now I am getting the following error message:
-* store the last month's current date in &DATETMP parameter
-SET &DATETMP = DATECVT((DATEADD((DATECVT(20101206, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
-TYPE 20101106
20101106
-* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
-SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-TYPE 20101101
20101101
-* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
-SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE 20101130
20101130
-* append time
-*-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') || '00.00.00.000000';
-*-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') || '23.59.59.999999';
-SET &FR_DT = EDIT(20101101,'99999999') | ' 00:00:00.000000';
-SET &TO_DT = EDIT(20101130,'99999999') | ' 23:59:59.999999';
-TYPE 20101101 00:00:00.000000
20101101 00:00:00.000000
-TYPE 20101130 23:59:59.999999
20101130 23:59:59.999999
TABLE FILE MY_TABLE
PRINT
USER_ID
DATE_TIME
BYTES_SENT
BYTES_RECEIVED
BY USER_ID
WHERE DATE_TIME FROM DT(20101101 00:00:00.000000) TO DT(20101130 23:59:59.999999);
WHERE READLIMIT EQ 10
ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
END
(FOC1400) SQLCODE IS 242 (HEX: 000000F2) XOPEN: S0003
: (242) [S0003] The conversion of a char data type to a datetime data typ
: e resulted in an out-of-range datetime value.
(FOC1407) SQL FETCH CURSOR ERROR. : MY_TABLE
Any ideas what I am doing wrong here?
Thanks again,
Seyed
WebFOCUS 8.0.09 App Studio 8009 Linux Kernel-2.6 DBMS: Oracle 11g all output (Excel, HTML, AHTML, PDF)
December 06, 2010, 05:28 PM
Waz
Have you turned client tracing on to see the SQL generated ?
What is the data source ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 06, 2010, 05:37 PM
SeyedG
Waz, Thank you for your help. The table that I am reading is a SQL Server table. Here is the code and the resulting error:
-SET &ECHO=ALL;
SET TRACEOFF=ALL
SET TRACEUSER=CLIENT
SET TRACEON=STMTRACE//CLIENT
-* store the last month's current date in &DATETMP parameter
-SET &DATETMP = DATECVT((DATEADD((DATECVT(&YYMD, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
-TYPE &DATETMP
-* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
-SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(&DATETMP,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-TYPE &MON_BEG_DT2
-* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
-SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(&DATETMP,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE &MON_END_DT2
-* append time
-*-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') || '00.00.00.000000';
-*-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') || '23.59.59.999999';
-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') | ' 00:00:00.000000';
-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') | ' 23:59:59.999999';
-TYPE &FR_DT
-TYPE &TO_DT
TABLE FILE MY_TABLE
PRINT
USER_ID
DATE_TIME
BYTES_SENT
BYTES_RECEIVED
BY USER_ID
WHERE DATE_TIME FROM DT(&FR_DT) TO DT(&TO_DT);
WHERE READLIMIT EQ 10
ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
END
Here is the error:
SET TRACEOFF=ALL
SET TRACEUSER=CLIENT
SET TRACEON=STMTRACE//CLIENT
-* store the last month's current date in &DATETMP parameter
-SET &DATETMP = DATECVT((DATEADD((DATECVT(20101206, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
-TYPE 20101106
20101106
-* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
-SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-TYPE 20101101
20101101
-* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
-SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE 20101130
20101130
-* append time
-*-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') || '00.00.00.000000';
-*-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') || '23.59.59.999999';
-SET &FR_DT = EDIT(20101101,'99999999') | ' 00:00:00.000000';
-SET &TO_DT = EDIT(20101130,'99999999') | ' 23:59:59.999999';
-TYPE 20101101 00:00:00.000000
20101101 00:00:00.000000
-TYPE 20101130 23:59:59.999999
20101130 23:59:59.999999
TABLE FILE LOG_DETAILS
PRINT
USER_ID
DATE_TIME
BYTES_SENT
BYTES_RECEIVED
BY USER_ID
WHERE DATE_TIME FROM DT(20101101 00:00:00.000000) TO DT(20101130 23:59:59.999999);
WHERE READLIMIT EQ 10
ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
END
16.28.59 AE SELECT TOP 10 T1."DATE_TIME",T1."USER_ID",T1."BYTES_SENT",
16.28.59 AE T1."BYTES_RECEIVED" FROM wslogdb70.dbo.log_details T1 WHERE
16.28.59 AE (T1."DATE_TIME" BETWEEN '16777216 00:00:00.000' AND '16777216
16.28.59 AE 00:00:00.000') ORDER BY T1."USER_ID";
(FOC1400) SQLCODE IS 242 (HEX: 000000F2) XOPEN: S0003
: (242) [S0003] The conversion of a char data type to a datetime data typ
: e resulted in an out-of-range datetime value.
(FOC1407) SQL FETCH CURSOR ERROR. : MY_TABLE
Thank you,
Seyed
WebFOCUS 8.0.09 App Studio 8009 Linux Kernel-2.6 DBMS: Oracle 11g all output (Excel, HTML, AHTML, PDF)
December 06, 2010, 06:08 PM
Waz
Well thats bizarre, the date is converted to a number.
Can you try adding the "/" chars to the date and see what happens.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
December 06, 2010, 07:48 PM
Dan Satchell
I would try two things. First, try using .EVAL to make sure your DM variables are being properly evaluated:
WHERE DATE_TIME FROM DT(&FR_DT.EVAL) TO DT(&TO_DT.EVAL);
Second, try placing your date values directly in the WHERE statement without the EDITs:
WHERE DATE_TIME FROM DT(&MON_BEG_DT2 00:00:00.000000) TO DT(&MON_END_DT2 23:59:59.999999);
My preference is the second approach.
WebFOCUS 7.7.05
December 06, 2010, 08:05 PM
Waz
I have to admit, I haven't had this sort of issue.
I've been useing
WHERE MODIFY_DTM LE DT(&SELDATE001..&SELTIME001)
without any issues.
Could it also be something to do with the version of MSSQL ?
and that fixed the error. I just have one more question though, why hour, minute and second are separated by a ':' but millisecond is separated by a '.'?
Thank you very much,
SeyedThis message has been edited. Last edited by: SeyedG,
WebFOCUS 8.0.09 App Studio 8009 Linux Kernel-2.6 DBMS: Oracle 11g all output (Excel, HTML, AHTML, PDF)
December 07, 2010, 11:45 AM
GamP
I guess it is because of the different meaning of the parts. When you separate hours from minutes, (15:59) you do not want to be able to misinterpret it (15.59, which may mean something else entirely). Same for separating minutes and seconds. But seconds and milliseconds can't be misinterpreted, so there you can use the . for separation.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
December 07, 2010, 11:54 AM
SeyedG
GamP, Thank you for your explanation. It now makes sense to me.
Seyed
WebFOCUS 8.0.09 App Studio 8009 Linux Kernel-2.6 DBMS: Oracle 11g all output (Excel, HTML, AHTML, PDF)