Assistance with trunc calculation in FM

[jaymoore1756]

I am trying to create this calculation in FM ...

trunc(nvl([Presentation layer].[Trouble].[ENERGIZED_DATETIME],sysdate) - [Presentation layer].[Trouble].[CREATION_DATETIME],4)

when I do this I am getting a parsing error

QE-DEF-0260 Parsing error before or near position: 71 of: "trunc(nvl([Presentation layer].[Trouble].[ENERGIZED_DATETIME],sysdate)"

This is an oracle database.

Any Suggestions or help would be appreciated

Thanks

[Lynn]

I haven't used Oracle in a while, but isn't NVL for strings rather than for dates? I think you should test just the NVL portion of your expression to see if that is the issue. You might try a COALESCE function instead.

[bdbits]

nvl is essentially an Oracle-ism for COALESCE. It works with non-string datatypes.

I am not really sure what is wrong with the expression. Does it work in PL/SQL or whatever straight-to-Oracle query tool you have?

[jaymoore1756]

Yes I am using Toad as the straight -to- Oracle query tool and it works the SQL works fine.

[bdbits]

Well then I don't know. 

If you want a workaround instead of calling support, I would probably try to use COALESCE instead of nvl() and casting to date datatypes instead of trunc(). It should not matter for the most part, but maybe it will work with more standard SQL.

One other thing that is probably not it, but... select menu item Project > Project Function List... and click on Define Quality of Service. Under Vendor Specific Functions, make sure there is a green checkmark next to Oracle. Most people would never change this, but since it is not working I thought it worth mentioning just in case.

[jaymoore1756]

I am still trying to get this to work ... Oracle is checked in the vendor functions. And I am trying to get the following to work

trunc (nvl([Development Layer].[HIS_Location_Dev].[ENERGIZED_DATETIME] ,{sysdate} ) - [Development Layer].[HIS_Location_Dev].[CREATION_DATETIME] ,4)

I get the error  ...

XQE-PLN-0299 the expression 'nvl("HIS_LOCATION"."ENERGIZED_DATETIME",sysdate) - "HIS_LOCATION"."CREATION_DATETIME"'is an argument to the database function 'trunc', but is not supported by the database.

thoughts ?

[krishdw85]

Try to use like this way...

round (trunc (


nvl (nvl (nvl([Development Layer].[HIS_Location_Dev].[ENERGIZED_DATETIME],[Development Layer].[HIS_Location_Dev].[ENERGIZED_DATETIME])-{sysdate} ) - [Development Layer].[HIS_Location_Dev].[CREATION_DATETIME]) ),4)