New Topic

Community Topic Question View Builder

Category View Builder

Product Version 8.0

View calculated field gives error: ORA-00904 invalid identifier

John-Arne Stokkan shared this question ago

Answered

I´m trying to add a calculated field adding number of days to a daytime field.

I opened the calculated field editor and selected freehand SQL

I used the syntax "table name"."daytime" + "table name"."days"

I tried using the view aliases and database table name. When validating I get ORA-00904 invalid identifier.

When I create a virtual table and using sql:

SELECT

"T1"."OBJECT_ID",

"T1"."DAYTIME",

"T1"."REMAINING_RATE_INSTANT",

"T1"."DAYTIME" + "T1"."REMAINING_RATE_INSTANT" AS ADD_DAYTIME

FROM "ANALYTICS"."DV_XCH_TANK_STATUS_HISTORY" "T1"

"T1"."DAYTIME" + "T1"."REMAINING_RATE_INSTANT"

This calculates the new date. Is there anything I am missing with the calculate field editor?

Thanks

Files:

The same question

Replies (2)

John-Arne Stokkan ●

Actually I found the solution elsewhere in the forum. It was solved by (1) setting the table as mandatory and (2) using the name as found in the oracle db and using the table alias in the yf view....

Reply URL

Jared Holtgrewe ●

Hi John,

Thank you for reaching out with this and for updating us on your progress and success in finding a solution.

Please let me know if you need any information regarding freehand SQL calculated fields or have any further questions.

Thanks,

Jared

Reply URL

John-Arne Stokkan ●

Thanks Jared. Actually I ran into a related issue. After I managed to create two calculated fields as dates I wanted to find the time difference between them, however I wasn´t able to do so in the calculation engine as the calculation editor say the sql is valid but upon save "Invalid data types, TIMESTAMP and TIMESTAMP for arithmetic operation MINUS" for Date1 - Date2. The date functions only gives the option of dates found in the tables. Any way around this? Thanks

URL

Jared Holtgrewe ●

Hi John,

What are the field types of Date1 and Date2? It is possible that Yellowfin is running into an issue attempting to run a minus operation between two different field types.

If Yellowfin is displaying a message it should be logging more detailed information within the logs.

Would it be possible to get a compressed copy of the logs folder located at <Yellowfin>/appserver/logs? This copy might be too large to attach to the community, if that is the case please find use of our FTP. With our FTP you can annonymously upload files to us for our review. If you use the FTP please let me know when you upload a file, please let me know when and what the file is called.

Regards,

Jared

URL

John-Arne Stokkan ●

Thanks for your reply, Jared. Both fields are timestamp. I managed a workaround where I created a new calculated field in the view using the source of date1 and date2

Date1 - Date2 i.e.

(daytime 1 + elapsed days) - (daytime 2 + elapsed days)

Giving me a metric in days. This worked, although it would more neat to create the time calculated fields Date1 and Date2 first and use them further in calculations.

URL

Jared Holtgrewe ●

Hi John,

I have been attempting to replicate what you are doing but am finding difficulty in doing so.

Could you provide some screenshots of your calculations? This should help me get an idea of the functions taking place within Yellowfin and discern whether we are facing defective behavior.

If needed I can always make this a private ticket in order to protect any sensitive information.

Regards,

Jared

URL

Jared Holtgrewe ●

Hi John,

I hope things are going well.

Just wanted to check-in and see how it's all going. Was there anything you were needing from me to help get this resolved?

Regards,

Jared

URL

Jared Holtgrewe ●

Hi John,

I hope things are going well over there.

Just wanted to let you know I'll be marking this as answered. This will remain viewable by the community and yourself. If you have any further questions regarding this please feel free to leave them here.

thanks,

Jared

URL

John-Arne Stokkan ●

Hi Jared. Thank you for the follow up. Sorry for the late reply, actually been busy creating content in yf! The fix and the workaround has worked well. Hoping for more time based calculations being available in future versions of yf. Thanks!

URL

Jared Holtgrewe ●

Hi John,

Glad to hear you are having success in creating content within Yellowfin. If you feel anything is ever missing from Yellowfin, you can create an Idea post that can be voted on by the community to be added as an enhancement to the product.

Please feel free to reach out with any other questions or issues in the future!

Thanks,

Jared

URL