trunc and months_between in obiee

ORA-00666

Hello,

I need to create logical column with formula:

TRUNC(MONTHS_BETWEEN(LAST_DAY(TO_DATE(DateNumber,'yyyymmdd')),BirthDay))- Age*12

this part of formula I already did, but i don't know if its right:

LAST_DAY(TO_DATE(DateNumber,'yyyymmdd'))

into:

TIMESTAMPADD(SQL_TSI_DAY , TO_DATETIME(DateNumber ,'yyyymmdd'), 

                       DateNumber)

How can i change TRUNC(MONTHS_BETWEEN(...)) in OBIEE?

Gianni Ceresa

Hi,

TRUNC = FLOOR : your trunc is to remove the decimal part of the number, so FLOOR is going to do the same thing

MONTHS_BETWEEN = TIMESTAMPDIFF(SQL_TSI_MONTH, <date1>, <date2>)

And I'm almost sure that your translation of LAST_DAY isn't returning you the right value ....

To get the last day of a date in OBIEE you are going to use TIMESTAMPADD to move from your date, you don't know how many days the months has, so you generally find the 1st day of the month, you add 1 month and subtract 1 day.

TIMESTAMPADD(SQL_TSI_DAY, 1 - DAYOFMONTH("Time"."T00 Calendar Date"), "Time"."T00 Calendar Date")) for example gives you the 1st day of the month, so if you add 1 month (same function using the SQL_TSI_MONTH parameter) and subtract 1 day you get the last day of the month of your date.

ORA-00666

As I understood I changed my formula:

           FLOOR(TIMESTAMPDIFF(SQL_TSI_MONTH ,

                                                      TIMESTAMPADD(SQL_TSI_MONTH ,

                                                                                    DAYOFMONTH(TO_DATETIME(DateNumber,'yyyymmdd')),

                                                                                    TO_DATETIME(DateNumber,'yyyymmdd') + 1),

                                                       BirthDay)

                         ) - Age * 12

But I get a warning: Function cast is called with an incompatible type

Gianni Ceresa

Did you test the pieces of the formula independently starting by the one returning you the last day of the month?

What is "DateNumber"? Is it a sysdate or something like that? Don't you have a real date next to it instead of having to transform your number in a date?

The formula you wrote here has no chance to give you something correct ....

Let's make it simpler: can you write in words what you are trying to calculate? (ideally with just an example of the expected result based on provided values of DateNumber, BirthDay and Age.

ORA-00666

I need to calculate how many months person was old. For example, if i need to calculate my months old when date_id (DateNumber) was 20101001, my birth date is 1981.10.08 and my age was then (date_id = 20101001) 29 years, so i was 0 months old.

select birth_date, date_id, age,

TRUNC(MONTHS_BETWEEN(LAST_DAY(TO_DATE(date_id,'yyyymmdd')),birth_date))-age*12 AS months_old

from table

BIRTH_DATE	DATE_ID	AGE	MONTHS_OLD
1981.10.08	20101001	29	0
1983.10.29	20101001	27	0
1961.01.29	20101001	49	9
1952.03.27	20101001	58	7
1962.08.19	20101001	48	2
1984.07.11	20101001	26	3
1976.03.27	20101001	34	7
1958.03.15	20101001	52	7
1938.10.09	20101001	72	0
1973.03.14	20101001	37	7
1965.08.18	20101001	45	2
1955.01.03	20101001	55	9
1961.03.13	20101001	49	7
1957.10.03	20101001	53	0

Gianni Ceresa

Will not looks nice but ...

Try with something like:

TIMESTAMPDIFF(SQL_TSI_MONTH,  TIMESTAMPADD( SQL_TSI_DAY,  1 - DAYOFMONTH(birth_date), TIMESTAMPADD(SQL_TSI_YEAR, age, birth_date) ) , TO_DATETIME(DateNumber,'YYYYMMDD') )

I imagine it will return only integers but in case you end up with a number with a decimal part add a FLOOR all around and it's supposed to work ...

It's similar to your logic but the other way round:

- take the birth date

- add the number of years based on age

=> last birthday date

- get the first day of the month of the birthday

- get the number of months between the first day of the month of last birthday and the date number

=> your value (if with decimals, doesn't seem to be the case at least on 12c, add a FLOOR all around).

Using this it makes less calculations on the date than replicating exactly your current logic.

ORA-00666

With suggested formula i get "function cast is called with an incompatible type". I think it's something because date formats. Maybe you know what's the problem?

Gianni Ceresa

What is the type of your columns DateNumber, age and birth_date ? (the OBIEE type)

And can these columns be null ?

ORA-00666

DateNumber is double

and birth_date is datetime

and both columns can be null

Gianni Ceresa

You must manage the null : like adding a CASE WHEN all around to not execute that formula if any of the parameters are null, or manage it by setting a default value if null.

ORA-00666

case when  age is null or  birth_date is null

or    DateNumber   is null

then 0 else

TIMESTAMPDIFF(SQL_TSI_MONTH,  TIMESTAMPADD( SQL_TSI_DAY,  1 - DAYOFMONTH(birth_date), TIMESTAMPADD(SQL_TSI_YEAR, age, birth_date)) , TO_DATETIME(DateNumber,'YYYYMMDD'))

end

With this I get "function cast is called with an incompatible type"

ifnull(

TIMESTAMPDIFF(SQL_TSI_MONTH,  TIMESTAMPADD( SQL_TSI_DAY,  1 - DAYOFMONTH(birth_date), TIMESTAMPADD(SQL_TSI_YEAR, age, birth_date)) , TO_DATETIME(DateNumber,'YYYYMMDD'))

,0)

Same - "function cast is called with an incompatible type"

TIMESTAMPDIFF(SQL_TSI_MONTH,  TIMESTAMPADD( SQL_TSI_DAY,  1 - DAYOFMONTH(ifnull(birth_date, 0)), TIMESTAMPADD(SQL_TSI_YEAR, ifnull(age, 0), ifnull(birth_date, 0))) , TO_DATETIME(ifnull(DateNumber, 0),'YYYYMMDD'))

Gives: "function Calendar Extract is called with an incompatible type"