Passing Date to WHERE Clause

February 09, 2008, 12:12 AM

All please I need some help with this code. I am trying to pass a date to a variable and use that variable to a where clause to return a specific amount of data to that date and I keep getting this error message (FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: 30-DEC-07.
Follow is an example of my code.
Any help will be greatly appreciated.

-DEFAULT &IDNUM='700000'
-DEFAULT &PACKNUM='BR110';
-DEFAULT &SDATE='30-DEC-07';

TABLE FILE TEST
SUM W_1
W_2
W_3
W_4
W_5
COMPUTE MTH/D12.1 = W_1 + W_2 + W_3 + W_4 + W_5;
BY MON_DTE
BY IDNUM
BY PACKNUM
ON TABLE COLUMN-TOTAL
WHERE (IDNUM EQ '&IDNUM') AND (PACKNUM EQ '&PACKNUM') AND (SDATE EQ '&SDATE')
END

0 ERROR AT OR NEAR LINE 25 IN PROCEDURE MEMFEX FOCEXEC *
(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: 30-DEC-07
0 ERROR AT OR NEAR LINE 25 IN PROCEDURE MEMFEX FOCEXEC *
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT

I even trying this version of the code and I keep getting the same error message. I really does not know where to go to fix that problem.
Thank you in advance.

-DEFAULT &IDNUM='700000'
-DEFAULT &PACKNUM='BR110';
-DEFAULT &SDATE='30-DEC-07';

SET SQLENGINE=SQLORA
SQL SQLORA SET SERVER testserver;
SQL SQLORA EX testschema.testserverpkg.spdate; (this return 30-DEC-07)

TABLE FILE SQLOUT
PRINT
myDate
ON TABLE HOLD FORMAT ALPHA AS end_month
END
-RUN
-READ end_month &SDATE.A9

TABLE FILE TEST
SUM W_1
W_2
W_3
W_4
W_5
COMPUTE MTH/D12.1 = W_1 + W_2 + W_3 + W_4 + W_5;
BY MON_DTE
BY IDNUM
BY PACKNUM
ON TABLE COLUMN-TOTAL
WHERE (IDNUM EQ '&IDNUM') AND (PACKNUM EQ '&PACKNUM') AND (SDATE EQ '&SDATE')
END

0 ERROR AT OR NEAR LINE 25 IN PROCEDURE MEMFEX FOCEXEC *
(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: 30-DEC-07
0 ERROR AT OR NEAR LINE 25 IN PROCEDURE MEMFEX FOCEXEC *
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT

February 09, 2008, 05:24 AM

FrankDutch

Please post your master of the TEST database

I think the problem is in the field format of SDATE.
Your amper field is a string (30-dec-07).
If your field SDATE looks like YYMD than the test value should look the same (20071230).

February 09, 2008, 10:32 AM

Leah

quote:
Originally posted by WIL:
All please I need some help with this code. I am trying to pass a date to a variable and use that variable to a where clause to return a specific amount of data to that date and I keep getting this error message (FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: 30-DEC-07.

I've never passes dates in that format for 'smart' dates, and as Frank says what is the format of SDATE as you call it in your master. Also if IDNUM and/or PACKNUM are numeric you need to remove the quotes around the parameters
Any help will be greatly appreciated.

-DEFAULT &IDNUM='700000'
-DEFAULT &PACKNUM='BR110';
-DEFAULT &SDATE='30-DEC-07';

TABLE FILE TEST
SUM W_1
W_2
W_3
W_4
W_5
COMPUTE MTH/D12.1 = W_1 + W_2 + W_3 + W_4 + W_5;
BY MON_DTE
BY IDNUM
BY PACKNUM
ON TABLE COLUMN-TOTAL
WHERE (IDNUM EQ '&IDNUM') AND (PACKNUM EQ '&PACKNUM') AND (SDATE EQ '&SDATE')
END

0 ERROR AT OR NEAR LINE 25 IN PROCEDURE MEMFEX FOCEXEC *
(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: 30-DEC-07
0 ERROR AT OR NEAR LINE 25 IN PROCEDURE MEMFEX FOCEXEC *
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT

I even trying this version of the code and I keep getting the same error message. I really does not know where to go to fix that problem.
Thank you in advance.

-DEFAULT &IDNUM='700000'
-DEFAULT &PACKNUM='BR110';
-DEFAULT &SDATE='30-DEC-07';

SET SQLENGINE=SQLORA
SQL SQLORA SET SERVER testserver;
SQL SQLORA EX testschema.testserverpkg.spdate; (this return 30-DEC-07)

TABLE FILE SQLOUT
PRINT
myDate
ON TABLE HOLD FORMAT ALPHA AS end_month
END
-RUN
-READ end_month &SDATE.A9

TABLE FILE TEST
SUM W_1
W_2
W_3
W_4
W_5
COMPUTE MTH/D12.1 = W_1 + W_2 + W_3 + W_4 + W_5;
BY MON_DTE
BY IDNUM
BY PACKNUM
ON TABLE COLUMN-TOTAL
WHERE (IDNUM EQ '&IDNUM') AND (PACKNUM EQ '&PACKNUM') AND (SDATE EQ '&SDATE')
END

0 ERROR AT OR NEAR LINE 25 IN PROCEDURE MEMFEX FOCEXEC *
(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: 30-DEC-07
0 ERROR AT OR NEAR LINE 25 IN PROCEDURE MEMFEX FOCEXEC *
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT

February 09, 2008, 10:54 AM

FrankDutch

Leah......

increasing your karma.....LOL

February 09, 2008, 11:14 AM

jimster06

I don't know the format of SDATE but be aware of this construct for testing date values: WHERE SDATE EQ DT(&SDATE)

February 10, 2008, 10:20 PM

WIL

FrankDutch, as u mention here how the master file looks like. I will also try your guys suggestions. Thanks all for the prompt response.

FILE=TEST, SUFFIX=SQLORA ,$
SEGNAME=TEST, SEGTYPE=S0 ,$
FIELD=IDNUM ,IDNUM ,
A6 ,A6 ,INDEX=I ,MISSING=OFF,$
FIELD=PACKNUM ,PACKNUM,
A5 ,A5 ,MISSING=OFF,$

FIELD=W_1 ,W_1,
P18.1 ,P9 ,MISSING=ON ,$
FIELD=W_2 ,W_2,
P18.1 ,P9 ,MISSING=ON ,$
FIELD=W_3 ,W_3,
P18.1 ,P9 ,MISSING=ON ,$
FIELD=W_4 ,W_4,
P18.1 ,P9 ,MISSING=ON ,$
FIELD=W_5 ,W_5,
P18.1 ,P9 ,MISSING=ON ,$
FIELD=SDATE ,SDATE,
HYYMDS ,HYYMDS ,MISSING=OFF,$
FIELD=MON_DTE ,MON_DTE,
HYYMDS ,HYYMDS ,MISSING=OFF,$

February 11, 2008, 03:05 AM

FrankDutch

WIL

I would suggest to change the master here

FIELD=SDATE ,SDATE,YYMD ,DATE ,MISSING=OFF,$

Now your field is a date only. This way you will mis the time part.

February 11, 2008, 08:55 AM

PBrightwell

Even if you change your master, you are going to have to change the format of your input variable &SDATE to YYMD. If you can't change your master, you can change your where to;
WHERE SDATE GE 20071230 AND SDATE LT 20071231. (&SDATE1)

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7