[CLOSED][S1009] Value '0000-00-00' Message

We have database tables we connect to using MySQL. Within these tables some date fields contain the value of 0000/00/00 which is legitimate. If I create a join to one of the table with the 0000/00/00 date value, I receive the following message.

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: S1009
: (0) [S1009] Value '0000-00-00' can not be represented as java.sql.Date
(FOC1407) SQL FETCH CURSOR ERROR. : PROJECT

The WHERE statements have nothing to do with the date and the date is not being pulled into the report. What is the solution to be able to link the tables?

The fex is below.

-* File temprpt.fex
JOIN CLEAR *
JOIN
PROJECT.PROJECT.ID IN PROJECT TO MULTIPLE PROJECT_TASK.PROJECT_TASK.PROJECT_ID
IN PROJECT_TASK TAG J35 AS J35
END
TABLE FILE PROJECT
SUM
PLANNED_HOURS
BY PROJECT.PROJECT.ID AS 'PROJID'
BY CUSTOM_105
BY CUSTOM_39
WHERE PROJECT_STAGE_ID EQ '4' OR '7' OR '8';
WHERE DELETED EQ '' OR ' ';
WHERE CUSTOM_3 NE ''
OR CUSTOM_10 NE ''
OR CUSTOM_28 NE '';
ON TABLE NOTOTAL
ON TABLE HOLD AS TEST01
END

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

Prod: WebFOCUS 7.7.05 OS:Linux; Upgrading to: WebFOCUS 8.1.05 OS:Windows; Outputs: HTML, PDF, Excel; Adapters: SAP, MySQL, Oracle incl Report Caster

Posts: 102 | Location: Cincinnati, Oh USA area | Registered: November 02, 2006

Guru

posted

Hide Post

Can you even table file off of that one table with the 0000/00/00 format?

WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs

Posts: 402 | Location: Upland, IN | Registered: June 08, 2012

Ignored post by J posted

Show Post

Geri

Platinum Member

posted

Hide Post

I am not sure what you mean by "table file off of that one table with the 0000/00/00 format"?

Prod: WebFOCUS 7.7.05 OS:Linux; Upgrading to: WebFOCUS 8.1.05 OS:Windows; Outputs: HTML, PDF, Excel; Adapters: SAP, MySQL, Oracle incl Report Caster

Posts: 102 | Location: Cincinnati, Oh USA area | Registered: November 02, 2006

Ignored post by Geri posted

Show Post

Geri

Platinum Member

posted

Hide Post

I can pulldata on the tables if I enter a where of WHERE THISDATE NE '0000/00/00'; However, I would need to enter that criteria for each date field and that will likley remove records I need to see. For example, there might be a dte field that says 'CHANGED_DATE' The date might be 0000/00/00 if no changes have been made.

Prod: WebFOCUS 7.7.05 OS:Linux; Upgrading to: WebFOCUS 8.1.05 OS:Windows; Outputs: HTML, PDF, Excel; Adapters: SAP, MySQL, Oracle incl Report Caster

Posts: 102 | Location: Cincinnati, Oh USA area | Registered: November 02, 2006

Ignored post by Geri posted

Show Post

Danny-SRL

Virtuoso

posted

Hide Post

Geri,

What is the format of your date field?

Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

Show Post

Waz

Expert

posted

Hide Post

Yes, what is the format of the field.

Iff this is a Date field, then I think this is not a valid date, and any program that expects a date will probably not like it.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Show Post

Wep5622

Virtuoso

posted

Hide Post

quote:
Originally posted by Geri:
We have database tables we connect to using MySQL. Within these tables some date fields contain the value of 0000/00/00 which is legitimate.

No, it is not. Your "mistake" stems from the fact that MySQL allows users to enter values like that. Blame MySQL, it's one of their many mistakes. If you ever get to choose between open source databases, pick PostgreSQL, you'll be much happier.

I don't know what your date '0000/00/00' is supposed to mean, but that looks a lot like it means "unknown" which (in relational databases) is normally conveyed by the value NULL.

The best way to fix this is to fix your database design, but if you can't do that (I assume the database belongs to some 3rd party application you have no control over) you can probably change that TABLE request into an SQL call and convert that particular date value to NULL or whatever you deem appropriate using a CASE statement (or does MySQL still only support the non-standard IF?).

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010

Ignored post by Wep5622 posted

Show Post

Tony A

Expert

posted

Hide Post

Alternatively you could try an old method that we used to employ back in the 90's against MVS/DB2 (if it still works! and no guarantee that it will now or forwards!).

Look at the .mas for your data source and in particular the column in which you are interested (your date column) and note what the USAGE and ACTUAL values are.

If the ACTUAL format is DATE (or the "newer" HYYMDS - talking 90's stuff

) then you could declare an additional column using an ACTUAL of A26 and apply your predicate to that.

For instance, say your column declaration in your .mas is -

FIELDNAME=ORDER_DATE, ALIAS=ORDER_DATE, USAGE=YYMD, ACTUAL=DATE, $

You could add a line so that your line above looks like this -

FIELDNAME=ORDER_DATE, ALIAS=ORDER_DATE, USAGE=YYMD, ACTUAL=DATE, $
FIELDNAME=ORDER_DATE_A26, ALIAS=ORDER_DATE, USAGE=A26, ACTUAL=A26, $

Note that, because your data source is an RDBMS, you can re-declare the same column with an alternate name (and format) providing that the ALIAS remains unchanged - e.g. the ALIAS for an RDBMS MUST be the RDBMS column name.

Good luck.

T

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

Geri

Platinum Member

posted

Hide Post

In the master file of PROJECT, several date fields contain 0000/00/00. The field is yyyymd. I do not have control over the source database.

FIELDNAME=CUSTOM_105, ALIAS=custom_105, USAGE=YYMD, ACTUAL=DATE, $

When I added this to the master file, the message is different.

FIELDNAME=CUSTOM_105_A26, ALIAS=CUSTOM_105, USAGE=A26, ACTUAL=A26, $

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: S1009
: (0) [S1009] Value '0000-00-00' can not be represented as java.sql.Date
(FOC1407) SQL FETCH CURSOR ERROR. : PROJECT

I limited my fex to only 2 fields for this research.

TABLE FILE PROJECT
PRINT
CUSTOM_105
BY PROJECT.PROJECT.ID
WHERE PROJECT.PROJECT.ID EQ '15501' OR '15502';
ON TABLE NOTOTAL
ON TABLE HOLD AS TEST01
END

The reason this field contains 0000/00/00 is that the field was created with the intent that users will populate the field. Not all have been populated. This table actually contains 5 fields like this.

Prod: WebFOCUS 7.7.05 OS:Linux; Upgrading to: WebFOCUS 8.1.05 OS:Windows; Outputs: HTML, PDF, Excel; Adapters: SAP, MySQL, Oracle incl Report Caster

Posts: 102 | Location: Cincinnati, Oh USA area | Registered: November 02, 2006

Ignored post by Geri posted

Show Post

TexasStingray

Master

posted

Hide Post

I would try this.

  
In the Master File Description set the actual field to this.
FIELDNAME=MYSQLDATE, ALIAS=ORDER_DATE, USAGE=A10, ACTUAL=A10, ACCESS_PROPERTY=(INTERNAL),$

Then at the end of the segment set create a define 
DEFINE ORDER_DATE/YYMD MISSING ON = IF MYSQLDATE EQ '0000/00/00' THEN MISSING ELSE MYSQLDATE;

You many need to experiment with the actual conversion syntax. I would do it in the DEFINE FILE and then once you get it working move the define into the MFD.

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

Scott