If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Assistance with trunc calculation in FM

Started by jaymoore1756, 27 Apr 2015 05:09:04 AM

Previous topic - Next topic

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.  :o

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)