I am trying to limit using a where/if test against our DB2 UDB v8 database in our test environment.

I have coded the following define:

DEFINE FILE AUPPR001_PPM_130_01_TBL01
EFF_MO/M=POL_EFFECTIVE_DT;
END

POL_EFFECTIVE_DT as a smart date with a format of YYMD

The where condition is as follows:

WHERE ( EFF_MO EQ '7' OR '07' );

We are selecting this way since we may want all years, but only records with an effective month of 7.

However, when I perform a SQL trace, it shows that the where test cannot be passed.

13.55.08 BR (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : EFF_YR
13.55.08 BR (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : EFF_MO
13.55.08 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
13.55.08 BR (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
13.55.08 AE SELECT T1."POL_NB",T1."POL_EFFECTIVE_DT" FROM
13.55.08 AE AUPPR001.PPM_130_01_TBL01 T1 FOR FETCH ONLY;

Is there a way for me to pass the month condition directly to DB2?

Sharon

Prod: WF 7.1.4 Dev Studio/BID/Rcaster/Rlibrary/Managed Reporting/LINUX/DB2 UDB 8, SQL Server
Test: WF 7.6.1 Dev Studio/BID/Rcaster/Rlibrary/Managed Reporting/LINUX/DB2 UDB 8, SQL Server
Dev: WF 7.6.1 Dev Studio/BID/Rcaster/Rlibrary/Managed Reporting/LINUX/DB2 UDB 8, SQL Sever

WebFOCUS does not/cannot pass these types of defined fields to SQL. I don't know if this will work, but have you tried

WHERE field LIKE 'mask'

WHERE POL_EFFECTIVE_DT LIKE '____07__'

(the underscore indicates that any character in that position is acceptable)

quote:

13.55.08 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
13.55.08 BR (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED

don't worry about this message it is because you are using print and not sum. As far as the other message if there is not field that has the month in it alone then you may want to see of SQL Pass thru will work.