Handling NULL date fields

May 13, 2008, 12:12 PM

I am doing a drill down report. When I drill down I am pulling back data that fits within a certain date range. However, I am having trouble handling NULL date fields. When I view the source I see the NULL field being returned as

DT( )

I tried using

SET NODATA = ' '

and then doing

CHECKEXPIRDATE = IF &PLUSCNEXTDATE EQ ' ' THEN &DATEMDYY ELSE &PLUSCNEXTDATE;

This is what is returned when I view the source

CHECKEXPIRDATE = IF DT( ) EQ ' ' THEN 05/13/2008 ELSE DT( );

I then tried the IF statement to say

CHECKEXPIRDATE = IF DT( ) EQ DT( ) THEN 05/13/2008 ELSE DT( );

But this didn't work either.

I am out ideas. Suggestions?

May 13, 2008, 12:59 PM

GinnyJakes

Please update your profile signature with your product suite, releases, and platform so that we can better help.

Also, what kind of data is your data source? Is MISSING=ON in the master for your date field?

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

May 13, 2008, 02:12 PM

Darin Lee

The NODATA setting is for display only. It has no impact on values being tested in your (assumed) DEFINE. Also, I don't know what your &PLUSNEXTDATE variable is doing so it's hard to give an exact answer.

If MISSING=ON for that field in your Master File AND the actual field in your RDBMS can be NULL , you can test it by using 'EQ MISSING'.

If the database field cannot be null, the MFD should have MISSING=OFF (the default) or no MISSING attribute at all. The value will be blank for alpha or 0 for numeric and you can test those values.

One other tip - you will want to assign a format to your defined field like MDYY. You will frequently get erros doing this sort of calculation when the field has no assigned format.

Regards,

Darin

In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat

May 14, 2008, 04:46 PM

Kymber

Thanks for your response. I ended up resolving this issue by doing a compute in the print stmt and changing it to a MDYY. This seemed to give me the results I was looking for instead of a DT( ). Thanks for your help

May 15, 2008, 11:16 AM

ira

Kymber, not sure what release you were experiencing this issue on, but we had a similar 'null value' issue in aix 5.3.3 - we ended up getting a 5.3.8 patch and all was resolved. As Ginny and others have aptly put it - inserting your environment/release would be time saving for all of us that are trying to assist. Glad your problem was fixed just the same.

Ira
aix - wf 5.3.8 (dev) 5.3.3 (dev), windows (7.6.1-test)