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,

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

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

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.

Geri,

What is the format of your date field?

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.

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?).

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 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.

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,