We are testing reports that were created in 8203 in our 8207...

[Guest]

The following code ran ok for me in 8.2.07.24:

DEFINE FILE GGSALES

DATEYYMD/YYMD = '&YYMD';

END

TABLE FILE GGSALES

SUM UNITS

BY REGION

BY DATEYYMD/MDYY

END

 

Does it work for you Could there be something else going on

[Debra Waybright]

david.beagan:

 

DEFINE FILE GGSALES DATEYYMD/YYMD = &YYMD; END TABLE FILE GGSALES SUM UNITS BY REGION BY DATEYYMD/MDYY END

 

 

I get an error that it cant find GGSALES. Must not have installed that yet.

[David Beagan]

You can install it as outlined in this post:

[sOLVED] Sample Data - WebFOCUS Developers - myibi

Or you could try this example if you dont want to bother with ggsales:

DEFINE FILE systable

DATEYYMD/YYMD = '&YYMD';

END

TABLE FILE systable

PRINT NAME

BY TYPE

BY DATEYYMD/MDYY

END

[Debra Waybright]

We are testing reports that were created in 8203 in our 8207 test environment. Reports that have date fields with a format are giving FOC282 RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD error.

For example, I have this:

BY Policy_Effective_Date/MDYY

I checked, and the format of that field in the table is YYMD. Did the syntax change with the new version Do I have to create a define field in order to change the output format now

Thanks,

Deb

[Debra Waybright]

david.beagan:

 

DEFINE FILE systable DATEYYMD/YYMD = &YYMD; END TABLE FILE systable PRINT NAME BY TYPE BY DATEYYMD/MDYY END

 

 

That code works. Mine is coming from passthrough SQL but the FF shows the field format is YYMD, so why does the /MDYY give me a format error

[Debra Waybright]

It occurred to me this morning that I did the FF in our production environment not the test environment. So I did the FF in our production version (8203) and that gives a YYMD format HOWEVER, in the test environment (8207) it gives A10V. Why would it change between 8203 and 8207 Just to give me headaches, I guess.

At least now I see why it is throwing a Format error. One more thing I have to explain to the users. Im already being asked if maybe we should postpone the upgrade again.

[Toby Mills]

Hi Deb

I wonder if sometime in the past, somebody manually updated that master to be YYMD instead of A10. Or maybe the back end changed and it used to be a YYMD and changed to A10. Either way, it makes me wonder if some manual change took place.

(make sure you have a backup of your existing master/acx before regenerating so you can compare the results).

If you regenerate the master file in 8203, does it make it YYMD or does it also now think that its an A10 Something feels suspicious to me.

My point being - maybe this particular issue isnt upgrade related - it may have been more coding related.

[Debra Waybright]

It is SQL passthrough - there is no masterfile. . Both environments are pointing to the same database. Yes it is suspicious. Is there a setting somewhere that tells WF to pull date fields from SQL as a certain format It still gives the format error if I change the SQL to cast the field as a date.

[Toby Mills]

Hi Deb - thats interesting

I dont think we can control the kind of format that the date with settings, but maybe theres something Im not aware of based on your particular adapter.

What database is this coming from (like Oracle or SQL Server ) and in the table definition, whats the format of the field there (like smalldatetime, varchar, char etc) Wondering about this fields native format inside the db.

Make sure you compare its internal format between the 2 databases. There could be some database setting that says to treat that type of field differently between prod and dev

[Debra Waybright]

We currently have both environments pointing to the exact same SQL Server database. Thought if they both pointed to production then we can rule out bad data as causing issues in the upgrade test environment. The fields are defined as date.

[Martin Yergeau]

I did have found the same behavior using 8204 vs 8207.

Running the same SQL passthrough against the same DB from the two versions process the date in different format.

Seems to be an SQL adaptor change in 8207

[Toby Mills]

Thanks Martin and Deb.

In my humble opinion, this falls under a Upward Compatibility issue that should be address. IBIs internal abbreviation for this is a UC. The date format typically comes across as a date time stamp sort of thing (or so I thought).

I suggest opening a case with IBI to bring this up. It sounds like both of you have encountered the same thing.

If we could find a system table with a format date field in it, that would be helpful in making a reproduction fex to upload to techsupport.

If you make a master file for it - I presume the 8203/4 master will have a different answer than the 8207 version. Thats another area that should be fixed. The A10V just sounds wrong to me for a default format.

If either of you open a case (its good if you both do - puts more emphasis on the same problem) - try to replicate the whole issue solely inside the Reporting Server. Maybe just make your sample fex in ibiappsbaseapp. The reason for this is to really try to aim your case at the Reporting Server group. This is in their area.

If you get a repro that hits system tables, itll likely be easy for them to repro between environments.

I guess the repro would just be your sql pass through statement followed by a FF to show the list of hold formats.

[Drew DeBaecke]

Is this solved Or is Davids link from August 9 causing a false flag

[Debra Waybright]

@drew.debaecke Not really. What was causing the error was solved in that we figured out there really is a format difference. Im planning to open a case today so Im going to change this to CASE OPENED from SOLVED.

[Debra Waybright]

Just realized I didn't update this with the solution. In my passthru SQL I have to add a CAST to DATETIME so that the /MDYY works in the TABLE statement. For example:

SELECT CAST(MyDate as DATETIME) as DateField

...

TABLE FILE SQLOUT

PRINT DateField/MDYY