Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Passing Date to WHERE Clause

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Passing Date to WHERE Clause
 Login/Join
 
WIL
Member
posted
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


WebFocus5.2.1/Unix
 
Posts: 3 | Location: Orlando Florida | Registered: December 13, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
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).




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Leah
Virtuoso
posted Hide Post
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


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
FrankDutch
Virtuoso
posted Hide Post
Leah......

increasing your karma.....LOL




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
jimster06
Guru
posted Hide Post
I don't know the format of SDATE but be aware of this construct for testing date values: WHERE SDATE EQ DT(&SDATE)


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
WIL
Member
posted Hide Post
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,$


WebFocus5.2.1/Unix
 
Posts: 3 | Location: Orlando Florida | Registered: December 13, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
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.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
PBrightwell
Master
posted Hide Post
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)


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Passing Date to WHERE Clause

Copyright © 1996-2020 Information Builders