Date casting in Framework Manager

[ccbarbeau]

I need your help, all!

I have a system where I have dates in various formats (6 digits, 8 digits, 6 characters, 8 characters). I'm trying to produce an expression that will cast all formats as dates, IE:

_make_timestamp (cast(substring([DATE FIELD],1,4) AS INTEGER),cast(substring([DATE FIELD],5,2) AS INTEGER),cast(substring([DATE FIELD],7,2) AS INTEGER))

Since I'm brand new at this, I need your help in finding (or creating) expressions for casting these types of data as date fields.

Thanks!

[blom0344]

I think you need 2 seperate expresions. One for cast a string to a date and one for casting an integer to a date.

It may be doable in one go, but then you need boolean operators like isnumeric:

CASE
WHEN
ISNUMERIC([SOMEFIELD]) = 1
THEN
<<< cast expression for numeric field >>>
ELSE
<<< cast expression for string field >>>
END

[ccbarbeau]

Thanks,

This is the expression I entered:

CASE
WHEN
ISNUMERIC([WM for iSeries].[OE Order Header].[Order Creation Date])<> 0
THEN
_make_timestamp (cast(substring([WM for iSeries].[OE Order Header].[Order Creation Date],1,4) AS INTEGER),cast(substring([WM for iSeries].[OE Order Header].[Order Creation Date],5,2) AS INTEGER),cast(substring([WM for iSeries].[OE Order Header].[Order Creation Date],7,2) AS INTEGER))
ELSE
_make_timestamp(substr(([WM for iSeries].[OE Order Header].[Order Creation Date]),1,4),substr(([WM for iSeries].[OE Order Header].[Order Creation Date]),5,2), substr(([WM for iSeries].[OE Order Header].[Order Creation Date]),7,2))
END

and when I enter it in RS it works for the first validation, then when I run the report I get the following error:

   An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.

Does the fact that I'm using iSeries make any difference?

Thanks again.

[blom0344]

Yes, you did not mention Iseries. Isnumeric is a T-sql example. So for Iseries this will not work.
Iseries SQL is a subseries from DB2 with a limited set of SQL functions

[ccbarbeau]

Would anyone else have any suggestions?

[blom0344]

Perhaps I do not entirely understand your requirement, but why not use seperate expressions for  either  numeric and string fields?  Cognos will detect the datatype when importing tables, so you will have indication which expression to use on which field. The benefit would be that it will allow the use of Cognos functions without having to resort to vendor specific functions (that need to comply with Iseries)

[ccbarbeau]

I think that the main issue is that the data is in decimal form in FM. I'm trying to find a way of casting these dates, because the expressions I usually use are not working. Since I'm completely new at this, I'm having trouble figuring out exactly what decimal for is and how to convert it.

[blom0344]

My last experience with Iseries was in 2005.  In our case the format of dates was indeed only numeric and build from the year combined with the daynumber of the year.  Instead of using functions we used a calender table to convert the Iseries format to a 'regular' date. Perhaps you have a similar issue at hand.  Can you tell HOW the numeric values look that you need to convert?

[ccbarbeau]

When I test the fields I get either a YYMMDD or YYYMMDD format. If they were all numeric, wouldn't my expression work on all of them? How had you imported your calendar table?

[blom0344]

for format YYYYMMDD   /  YYMMDD  and dates after 2000-01-01:

Code Select Expand


CASE
WHEN
[somedate_as_numeric]  > 1000000
THEN
_make_timestamp(CAST(SUBSTRING(CAST([somedate_as_numeric];VARCHAR(8));1;4);INTEGER);
CAST(SUBSTRING(CAST([somedate_as_numeric];VARCHAR(8));5;2);INTEGER);
CAST(SUBSTRING(CAST([somedate_as_numeric];VARCHAR(8));7;2);INTEGER))
WHEN
[somedate_as_numeric]  < 1000000
THEN
_make_timestamp(CAST(SUBSTRING(CAST([somedate_as_numeric];VARCHAR(6));1;2);INTEGER)+2000;
CAST(SUBSTRING(CAST([somedate_as_numeric];VARCHAR(6));3;2);INTEGER);
CAST(SUBSTRING(CAST([somedate_as_numeric];VARCHAR(6));5;2);INTEGER))
END


[ccbarbeau]

I'm telling you, this is insane!

Here is what I got when I input your expression:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-120'.
UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.
UDA-SQL-0481 An expression or operator (_make_timestamp) is not supported by the database. This operation requires local processing of the data.

ARGH!!

[Lynn]

I hate DB2 iSeries....

Here is what I use for the YYYYMMDD format in situations where the number can be zero to indicate no date. If a date is always present I'd omit the "if" altogether. No local functions here so maybe that will help. I try to always use database functions in FM. Can't really explain why. I just always have.

Code Select Expand


if ([Database].[Table].[Column] <> 0 )
then (
date(
  substr(char( [Database].[Table].[Column] ),1,4) || '-' ||
  substr(char( [Database].[Table].[Column] ),5,2) || '-' ||
  substr(char( [Database].[Table].[Column] ),7,2)
    )
)
else
( date('1900-01-01') )


[blom0344]

I hate DB2 iSeries....

Iseries is like the grumpy but utterly dependable aging uncle   ;)

Cognos 8 allows for creating a RDBMS independant model (like having 1 model for both SQL server / Oracle), which would be a reason to stay away from vendor-specific functions. In case of Iseries it is very unlikely that you would need to switch , so using DB2 functions would make sense..

[ccbarbeau]

Hi all,

So Lynn's expression worked for all but a few fields, where I get 2 new error messages:

RQP-DEF-0177 An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.
UDA-SQL-0144 An arithmetic exception was detected.
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0180 - Syntax of date, time, or timestamp value not valid.
UDA-CUR-0000 Unable to fetch the row.

And the other says it's fetching too many rows.

ARGH.

[Lynn]

oh dear  :'(

Grumpy Uncle indeed. I have some date fields where a number can be entered that does not represent a valid date. Last time I checked the calendar there is no such thing as month 16 nor is there a September 42nd. Attempting to cast to a date will result in an error.

This might be the cause of your first error, but I'm not entirely certain if that is your situation or not. Below is something that performs a (very) poor man's version of date validation.  Leap Year not included.

I would suggest doing some SQL queries against the database to see if you even have this situation or not before trying to code around it in FM. If you are faced with it ... then BEG, PLEAD, THREATEN and/or BRIBE to get the erroneous data corrected in the source! (Then tell me what method of persuasion worked and I may go back for round 2).

Code Select Expand


if (case
   when SUBSTR(char([Database].[TABLE].[DATEINTEGER]),1,4) BETWEEN '1900' AND '2100'
     then case
       WHEN SUBSTR(char([Database].[TABLE].[DATEINTEGER]),5,2) BETWEEN '01' AND '12'
         THEN CASE
           WHEN CAST(SUBSTR(char([Database].[TABLE].[DATEINTEGER]),7,2) as INTEGER)
                BETWEEN 1 AND
                CAST(EXTRACT(DAY, DATE(CHAR(INSERT(SUBSTR(char([Database].[TABLE].[DATEINTEGER]),1,6),5,0, '-')
                     ||'-01',10))+ 1 MONTH - 1 DAY)
                as INTEGER)
            then 1
            else 0
        end
      ELSE 0
    END
  ELSE 0
END = 1 )
then
(
date(
substr(char( [Database].[TABLE].[DATEINTEGER] ),1,4) || '-' ||
substr(char( [Database].[TABLE].[DATEINTEGER] ),5,2) || '-' ||
substr(char( [Database].[TABLE].[DATEINTEGER] ),7,2))
)
else  (date('1900-01-01'))

#/* The above nested case statements check if the integer is a valid date value or not.
    1 is returned if valid, 0 if invalid.
    Basic logic is to check for a valid year, then a valid month, then check if the day is between
    1 and the number of days in the month for the value being checked. */#


[ccbarbeau]

Oh my word, I found a way to work around this. Changed my data source to limited local query processing, worked like a charm... took away the 'UDA-SQL-0481 An expression or operator (_make_timestamp) is not supported by the database. This operation requires local processing of the data' error.


Thanks for all your help!  ;D