Calculated Field - "Unknown column"

Hi Nick,

I see that GradeName is sourced from a database view rather than a table, so I was just wondering whether that column name in the db view is actually an alias for the original column in the db table?

regards,

David

Hi Dave,

None are straight field reference - all use an alias.

This is from view:

CAST(`sr`.`PumpID` AS CHAR (10) CHARSET UTF8) AS `PumpId`,

CAST(`sr`.`NozzleID` AS CHAR (10) CHARSET UTF8) AS `NozzleId`,

COALESCE(`gt1`.`TranslatedGradeName`, `gt2`.`TranslatedGradeName`, `Grades`.`GradeName`) AS `GradeName`,

`htts`.`TankString` AS `TankString`, (httts is also view)

We are using MySQL if that makes a difference.

Regards,

Nick

Hi Nick,

thanks for the extract. I created a simple replication of the database view in MySQL, these are what I thought to be the important points:

I made sure I used the COALESCE function and a column alias and then created a YF view and then a report, then created a freehand SQL calculated field using that alias but unfortunately the validation worked.

So now I'm wondering whether the "bug" (because it sounds like a bug) is specific to a particular build, so with this in mind could you please tell which build of 7.4 you're using?

Also, I noticed from your screenshots that you are using the + operator as a concatenation operator, as far as I know this doesn't work in MySQL, but definitely works in SQL Server, could you please confirm which database you are using.

thanks,

David

Hi Dave,

Please find version info attached.

You are correct, I typically use mySQL concat() function to join string fields in freeform calculated field, in this case I just copy and pasted as per operators available in the Simple calc field builder to test.

Regards,

Nick

Hi Nick,

thanks for that, although unfortunately I still can't replicate the issue in that build. I notice you said that the issue has happened a couple of times...does that mean it doesn't happen every time you create a freehand SQL calculated field that refers to a column from a db view?

regards,

David

Hi Dave,

Yes I do use calculated fields regularly with concat() without issue, then on some occasions this error occurs.

When I get some time I will have a look at the config db to check the field mappings - perhaps there is some corruption/misalignment of field templates?

Similar to when changes to a view cause a field reference in a freeform calculated field to change to 'Unknown Field'.

Regards,

Nick

Hi Nick,

yes, I'm in agreement with you about your theory about the field templates being out of synch or something.

It will be interesting to hear what your investigation reveals!

regards,

David

Hi Nick,

Just wanted to reach out on Dave's behalf as he has left the company; were you still experiencing this issue?

Thanks,

Eric

Hi Eric,

Haven’t experienced it for a while – but then again haven’t attempted to replicate it.

Kind Regards

Nick Eddy

Business Intelligence Developer

LEIGHTON O'BRIEN

Intelligence Delivered

Tel +61 3 9804 2200

www.leightonobrien.com

Disclaimer: The above information is confidential to the addressee and may be privileged. Unauthorised access and use is prohibited. 

Internet communications are not secure and therefore this company does not accept legal responsibility for the contents of this message. 

If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted in reliance on it is prohibited and may be unlawful

From: Support Queue [mailto:support@Yellowfin.bi]

Sent: Thursday, 6 June 2019 4:55 AM

To: Nick Eddy <nickeddy@leightonobrien.com>

Subject: New Comment in "Calculated Field - "Unknown column""

Hi Nick,

Thanks for the reply here, I'm going to go ahead and mark this as Resolved for now; feel welcome to re-open this with a response if you experience further related issues.

Best Regards,

Eric