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.

This should be passed to db2 as a where clause.

WHERE POL_EFFECTIVE_DT FROM '01/07/2006' TO '31/07/2006'

NB

1. You'll have to put in some dm logic to find the end of month - plenty of functions will do this.

2. You'll have to use whatever format your smartdate is I have used UK dates dd/mm/yyyy

3. No guarantee it will be quicker unless POL_EFFECTIVE_DT is contained within index in db2!

quote:

WHERE POL_EFFECTIVE_DT FROM '01/07/2006' TO '31/07/2007'

Except she only wants data for the months of july 2006 and july 2007, not for aug 2006 to june 2007 which you would get with this where statement. The LIKE would probably be your best best. Only other option would be to create a DB2 view of this table where the date components are broken out. We have done this in several instances where we couldn't get anything else to work. Only requires a little more work from a DBA (which is sometimes hard to come by)

Darin

You got to my post seconds before I corrected typo.

The like clause you suggested will not pass wf syntax checking.

To get a clause that scans for 2005/6/7 etc would require a bit more dm (a loop for each year) but the output would be like this.

WHERE POL_EFFECTIVE_DT GE '01/07/2005' AND POL_EFFECTIVE_DT LE '31/07/2005'
OR POL_EFFECTIVE_DT GE '01/07/2006' AND POL_EFFECTIVE_DT LE '31/07/2006'
OR POL_EFFECTIVE_DT GE '01/07/2007' AND POL_EFFECTIVE_DT LE '31/07/2007'

Again the effort of doing this may not be worth the performance gain but the selection criteria WILL be passed to DB2, I guarantee.

Thank you for the posts. I haven't been able to get any of the suggestions to pass to the database other than passing a full date with a between clause. I will open a case with tech support. Sharon

Would it be possible for the DBA to make a change to the table? Simply add a column with the Month as an alpha field.