Date Function in Where Criteria

I am getting an error on this code:

DEFINE FILE PRICING
DATEX/YMD = DATEADD('080721', 'D', -30);
END
TABLE FILE PRICING
PRINT
DATE_DUE AS 'Due Date or ASAP'
RESPONSIBLE_PERSON AS 'Sales Exec'
STATUS
CATEGORY
HEADING
""
FOOTING
""
WHERE ( CATEGORY LIKE 'Propos%' ) AND ( STATUS LIKE 'Open%' ) AND (SENT_TO_DATE GE DATEX));

Error:

0 ERROR AT OR NEAR LINE 25 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Am I missing something?

Winnie

Webfocus 7.7.3

Posts: 83 | Registered: July 16, 2008

jimster06

Guru

posted

Hide Post

You might be missing something - do a search on DT(.

Make sure that the format of your argument is consisten with the format of SENT_TO_DATE

jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K

Posts: 252 | Location: USA | Registered: April 15, 2003

Ignored post by jimster06 posted

Show Post

GinnyJakes

Expert

posted

Hide Post

Also, Winnie, some data bases prefer date checks to be done with literal dates as opposed to defined fields. Doing a trace will show that the WHERE isn't passed to the RDBMS.

Instead you can do this:

-SET &DATEX=AYMD('080721'', -30,'I6YMD');
TABLE FILE PRICING
...
WHERE SENT_TO_DATE GE &DATEX
END

And Jim is correct. Since we don't know what the format of SENT_TO_DATE is, it is difficult to know exactly what format the WHERE clause should take.

Also, and I thought you had fixed this, please update your profile signature with your product suite, releases, and platforms.

Thanks.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Show Post

Leah

Virtuoso

posted

Hide Post

quote:
WHERE ( CATEGORY LIKE 'Propos%' ) AND ( STATUS LIKE 'Open%' ) AND (SENT_TO_DATE GE DATEX));

Also, ensure CATEGORY and STATUS are ALPHANUMERIC, is SET_TO_DATE a date?

Leah

Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004

Ignored post by Leah posted

Show Post

FrankDutch

Virtuoso

posted

Hide Post

I would suggest to split the where statement into 3 separate lines. Then you might better find which causes the problem.

	*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