Calculated Field - "Unknown column"

Nick Eddy shared this problem 14 months ago
Resolved

Hi team,


This has happened a couple of times to me:


When creating a Freehand SQL calculated field, validation fails with the following message: “Unknown column 'X' in 'field list”

I have looked up the correct field name in the view source information and tried using this - no luck

I have selected the same field using the ‘Simple’ calculated field option - this works, but then copy and pasting that simple code as a Freehand SQL statement fails.

Any ideas?

Regards,

Nick

Comments (10)

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

photo
1

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

7279d15757d6ca7d97da8db53cf3a9bf

LEIGHTON O'BRIEN

Intelligence Delivered

Tel +61 3 9804 2200

www.leightonobrien.com

2ae94317dc7bdbf2acf1adbb77a10de0858c7b3a537aa79e8e78d47999006445be8eaf430e3ad30b717318c7844ea8b2 

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

photo
1

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