COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: ccbarbeau on 17 Feb 2011 02:58:53 PM

Title: Date casting in Framework Manager
Post by: ccbarbeau on 17 Feb 2011 02:58:53 PM
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!
Title: Re: Date casting in Framework Manager
Post by: blom0344 on 18 Feb 2011 04:59:37 AM
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
Title: Re: Date casting in Framework Manager
Post by: ccbarbeau on 18 Feb 2011 03:45:41 PM
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.
Title: Re: Date casting in Framework Manager
Post by: blom0344 on 21 Feb 2011 05:15:43 AM
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
Title: Re: Date casting in Framework Manager
Post by: ccbarbeau on 21 Feb 2011 07:45:00 AM
Would anyone else have any suggestions?
Title: Re: Date casting in Framework Manager
Post by: blom0344 on 21 Feb 2011 08:43:10 AM
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)
Title: Re: Date casting in Framework Manager
Post by: ccbarbeau on 21 Feb 2011 08:49:04 AM
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.
Title: Re: Date casting in Framework Manager
Post by: blom0344 on 21 Feb 2011 09:11:52 AM
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?
Title: Re: Date casting in Framework Manager
Post by: ccbarbeau on 21 Feb 2011 09:54:21 AM
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?
Title: Re: Date casting in Framework Manager
Post by: blom0344 on 21 Feb 2011 01:04:55 PM
for format YYYYMMDD   /  YYMMDD  and dates after 2000-01-01:



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
Title: Re: Date casting in Framework Manager
Post by: ccbarbeau on 24 Feb 2011 02:34:48 PM
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!!
Title: Re: Date casting in Framework Manager
Post by: Lynn on 24 Feb 2011 02:54:43 PM
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.


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') )
Title: Re: Date casting in Framework Manager
Post by: blom0344 on 25 Feb 2011 07:44:44 AM
Quote from: Lynn on 24 Feb 2011 02:54:43 PM
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..
Title: Re: Date casting in Framework Manager
Post by: ccbarbeau on 25 Feb 2011 09:18:31 AM
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.
Title: Re: Date casting in Framework Manager
Post by: Lynn on 25 Feb 2011 09:50:23 AM
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).


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. */#
Title: RESOLVED:Date casting in Framework Manager
Post by: ccbarbeau on 08 Mar 2011 01:35:21 PM
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