Author Topic: Assistance with trunc calculation in FM  (Read 2358 times)

Offline jaymoore1756

  • Community Leader
  • *****
  • Join Date: Dec 2012
  • Posts: 94
  • Forum Citizenship: +1/-0
Assistance with trunc calculation in FM
« on: 27 Apr 2015 05:09:04 am »
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

Online Lynn

  • Statesman
  • ******
  • Join Date: Apr 2008
  • Posts: 2,684
  • Forum Citizenship: +340/-1
Re: Assistance with trunc calculation in FM
« Reply #1 on: 28 Apr 2015 04:46:27 am »
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.

Offline bdbits

  • Super Moderator
  • Statesman
  • ******
  • Join Date: Feb 2010
  • Posts: 1,821
  • Forum Citizenship: +106/-0
Re: Assistance with trunc calculation in FM
« Reply #2 on: 28 Apr 2015 05:36:40 pm »
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?

Offline jaymoore1756

  • Community Leader
  • *****
  • Join Date: Dec 2012
  • Posts: 94
  • Forum Citizenship: +1/-0
Re: Assistance with trunc calculation in FM
« Reply #3 on: 29 Apr 2015 04:15:05 am »
Yes I am using Toad as the straight -to- Oracle query tool and it works the SQL works fine.

Offline bdbits

  • Super Moderator
  • Statesman
  • ******
  • Join Date: Feb 2010
  • Posts: 1,821
  • Forum Citizenship: +106/-0
Re: Assistance with trunc calculation in FM
« Reply #4 on: 29 Apr 2015 04:17:44 pm »
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.

Offline jaymoore1756

  • Community Leader
  • *****
  • Join Date: Dec 2012
  • Posts: 94
  • Forum Citizenship: +1/-0
Re: Assistance with trunc calculation in FM
« Reply #5 on: 14 May 2015 11:09:14 am »
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 ?

Offline krishdw85

  • Community Leader
  • *****
  • Join Date: Mar 2010
  • Posts: 108
  • Forum Citizenship: +0/-4
Re: Assistance with trunc calculation in FM
« Reply #6 on: 08 Sep 2015 04:46:27 am »
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)

 


       
Twittear