Edit Formula issues in Answers

user3507711

Hi,

I am using the below case statement in the Edit Column formula at the Analysis level of particular column and it is throwing the error as follows:

[nQSError: 10058] A general error has occurred.

[nQSError: 43113] Message returned from OBIS.

[nQSError: 27002] Near : Syntax error [nQSError: 26012] . (HY000)

COUNT(DISTINCT CASE WHEN "DATE_Table"."DATE_Column"
between
CAST(TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(@{PBofrom}{02-JAN-2017}) +2, @{PBofrom}{02-JAN-2017}) as DATE)
AND
CAST(TIMESTAMPADD(SQL_TSI_DAY, 8-DAYOFWEEK(MAX(@{PBoto}{17-DEC-2017})), MAX(@{PBoto}{17-DEC-2017})) as DATE) THEN "Incident"."Incident No" END)

OBIEE Version: 11.1.1.7.1

Can someone please let me know if this case statement is having any issue.

Your help is highly appreciated.

Thanks In Advance

Siva

Robert Angel

Hi,

your syntax is a mixture of dates and timestamps, this will not work, you need to be consistent.

Always explicitly cast rather than relying on implicit conversions, implicit will 'bite you'.

So where you have dates currently cast them to timestamp instead, you will have to have the right default formats 00:00:00 and you may have to concatenate similar midnight times on the end of your date field.

Thomas Dodds

+1 @Robert Angel

It's your datatypes ...

Robert Angel

The number of times I wish OBIEE had to_date to_timestamp the same as Oracle DB (I know you can use evaluate, but it is not as performant) as converting dates / timestamps is invariably painful...

Gianni Ceresa

Well ... it does have it:

TO_DATETIME('string1', 'DateTime_formatting_string')

Robert Angel

Okay, this had passed me by, a recent addition?

And is there a To_Date also?

I suspect this will also be a godsend to user3507711 in simplifying the syntax (and actually making it work which is always a bonus...)

[Deleted User]

To_DateTime is an RPD conversion as well as available (and working) in Answers since...at least 11.1.1.6.

It does NOT get rendered in the formula editor in Answer though. Still works perfectly.

No To_Date though but you can just format it down to a date.

Robert Angel

Thanks to @Christian Berg and @Gianni Ceresa

Curious why this would not be in the synyax palette, but I guess it is not alone in this respect.

[Deleted User]

Heh no not at all. No clue why this still ahsn't been fixed. Seems like a supremely small thing to implement if the functionality is there and works.

[Deleted User]

@user3507711 if the whole conversation is tl;dr for you:

to_datetime is in and documented: https://docs.oracle.com/cd/E28280_01/bi.1111/e10544/appsql.htm#BIEUG3215 https://docs.oracle.com/middleware/11119/biee/BIEUG/appsql.htm#CHDIBAAB

Bug 26428839 exists to have it in the GUI as well, but...nothing

Edit: it actually goes back to before 11.1.1.3

user3507711

Hi Robert,

Thanks a lot.

It worked for me.

Thank you everyone for all your inputs.

Thanks

Siva