Hi,
In MFD I have the DEFINES below.

-**********************
DEFINE TODAYS/YYMD='&YYMD';,ACCESS_PROPERTY=(INTERNAL),$
DEFINE TODAYS_30/YYMD=DATEADD(TODAYS,'D',30);,$
DEFINE TYPE_PERIOD/A50V=IF BTYPE EQ 'X' THEN 'X' ELSE IF REQ_DATE LE TODAYS THEN 'PAST_DUE' ELSE IF REQ_DATE LE TODAYS_30 THEN 'BO_30' ELSE 'BO';
TITLE='TYPE_PERIOD', $
-********************

FIELDNAME=REQ_DATE, ALIAS=REQUIRED_DATE, USAGE=YYMD, ACTUAL=DATE, $

-****************************


When I check SQL trace I get the following warnings .
I am not sure what to change so that the it converts to SQL properly and Aggregation gets done.

(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2565) THE OBJECT OF BY/ACROSS CANNOT BE CONVERTED TO SQL
(FOC2566) DEFINE TODAYS_30 CANNOT BE CONVERTED TO SQL
(FOC2576) COMBINATION OF PARAMETERS OF FUNCTION DATEADD CANNOT BE CONVERTED TO SQL

-***
I have to put these DEfine/conditions in my MFD so that the InfoAssist users can simply use these fields.

thanks

This message has been edited. Last edited by: BM,

I've never done this, but try using the function DB_EXPR to insert a SQL expression.

Check the manual: WebFOCUS Language Documentation > Using Functions > Data Source and Decoding Functions > DB_EXPR: Inserting an SQL Expression Into a Request.

There's also SQL.

Check the manual: Creating Reports With WebFOCUS Language > Creating Temporary Fields > Defining a Virtual Field > Passing Function Calls Directly to a Relational Engine Using SQL.Function Syntax

I got TODAYS_30 to work as you see below. I know I am not duplicating your situation exactly, but maybe this will help you solve the problem:

DEFINE FILE IBISAMP/CAR
  TODAYS_30/YYMD=DATEADD('&YYMD','D',30);,$ 
END

TABLE FILE IBISAMP/CAR
SUM
     CAR.BODY.SALES
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR
BY  CAR.CARREC.MODEL
BY  TODAYS_30
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END

Somehow TODAYS and TODAYS_30 are confusing the WebFOCUS engine.

Run this code:

DEFINE FILE IBISAMP/CAR
  TODAYS_30/YYMD=DATEADD('&YYMD','D',30);,$ 
END

TABLE FILE IBISAMP/CAR
SUM
     CAR.BODY.SALES
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR
BY  CAR.CARREC.MODEL
BY  TODAYS
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END

There is no TODAYS defined, but when I use TODAYS in a BY statement, the report runs but TODAYS_30 shows up on the report instead of TODAYS.

Also, if you try to define both a TODAYS and a TODAYS_30, it will cause an error.

Doh!

Remove the comma/dollar signs in the DEFINE and all is well.

DEFINE FILE IBISAMP/CAR
  TODAYS/YYMD='&YYMD';
  TODAYS_30/YYMD=DATEADD('&YYMD','D',30);
END

TABLE FILE IBISAMP/CAR
SUM
     CAR.BODY.SALES
BY  CAR.ORIGIN.COUNTRY
BY  CAR.COMP.CAR
BY  CAR.CARREC.MODEL
BY  TODAYS
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END

This is not a master file, I guess.

thanks All..DB_EXPR did the trick