View calculated field gives error: ORA-00904 invalid identifier

John-Arne Stokkan shared this question 4 months 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

Comments (2)

photo
1

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

photo
2

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

photo
1

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

photo
1

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

photo
1

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.

photo
1

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

photo
1

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

photo
1

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

photo
1

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!

photo
1

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

photo