When I go to Home › Administer › Site configuration > Views Calc > Fields and create a new calculation as follows, it saves properly. However, when I use the calculation as a field in an actual view, a SQL error message is reported indicating that it cannot find that column in the database. Upon further research, I can tell that the inserted column name for the calculation is using %node_data_field_.field__value when it should be using %node_data_field_value.field__value

For example, I have a field calculation called ViewsCalc:ROI as follows:
%node_data_field_pain.field_pain_value / %node_data_field_cost.field_value_cost

The fields are copied and pasted from the Field Substitutions list. The calculation saves fine. When I add the ViewsCalc:ROI field to my view, it comes back with the following error:
user warning: Unknown column 'node_data_field_pain.field_pain_value' in 'field list' query: SELECT DISTINCT(node.nid) AS nid, node.title AS node_title, node_data_field_value.field_value_value AS node_data_field_value_field_value_value, node_data_field_value.nid AS node_data_field_value_nid, node.type AS node_type, node_data_field_value.field_pain_value AS node_data_field_value_field_pain_value, node_data_field_value.field_cost_value AS node_data_field_value_field_cost_value, draggableviews_structure0.value AS draggableviews_structure0_value, (node_data_field_pain.field_pain_value / node_data_field_cost.field_value_cost) AS cid1 FROM node node LEFT JOIN og_ancestry og_ancestry ON node.nid = og_ancestry.nid LEFT JOIN content_type_userstory node_data_field_value ON node.vid = node_data_field_value.vid LEFT JOIN draggableviews_structure draggableviews_structure0 ON node.nid = draggableviews_structure0.nid AND (draggableviews_structure0.delta = 0 AND draggableviews_structure0.vid = 5) WHERE (node.status <> 0) AND (node.type in ('userstory')) AND (og_ancestry.group_nid = 5) ORDER BY draggableviews_structure0_value ASC in /httpdocs/sites/all/modules/views/includes/view.inc on line 735.

It seems to me that the field called node_data_field_value.field_pain_value works fine but node_data_field_pain.field_pain_value does not. (Note that the first one that begins with node_data_field_*value* and the second begins with node_data_field_*pain*

Comments

karens’s picture

karens commented

This comes from the optimizations that have been added to Views. The field tables get combined if they share the same table and the table name for the second field is changed. I have to find a way to work around this to force views to leave our tables alone or swap the new table name into the formula.

karens’s picture

karens commented
Title: Views Calc Field has mislabeled CCK fields » The query uses the wrong table names

Renaming for clarity.

finke77’s picture

finke77 commented

Hello,

I've got a similar problem.

SELECT node.nid AS nid,
   node_data_field_profile_nachname.field_profile_nachname_value AS node_data_field_profile_nachname_field_profile_nachname_value,
   node.type AS node_type,
   node.vid AS node_vid,
   node_data_field_profile_nachname.field_profile_vorname_value AS node_data_field_profile_nachname_field_profile_vorname_value,
   node_data_field_profile_nachname.field_profile_geburtstag_value AS node_data_field_profile_nachname_field_profile_geburtstag_value,
   (node_data_field_profile_geburtstag.field_profile_geburtstag_value ) AS cid1
 FROM node node 
 LEFT JOIN content_type_profile node_data_field_profile_status_value_0 ON node.vid = node_data_field_profile_status_value_0.vid AND node_data_field_profile_status_value_0.field_profile_status_value = 'aktiv'
 LEFT JOIN content_type_profile node_data_field_profile_nachname ON node.vid = node_data_field_profile_nachname.vid
 WHERE (node.type in ('profile')) AND (node_data_field_profile_status_value_0.field_profile_status_value = 'aktiv')

Error message:

user warning: Unknown column 'node_data_field_profile_geburtstag.field_profile_geburtstag_valu' in 'field list' query: SELECT node.nid AS nid, node_data_field_profile_nachname.field_profile_nachname_value AS node_data_field_profile_nachname_field_profile_nachname_value, node.type AS node_type, node.vid AS node_vid, node_data_field_profile_nachname.field_profile_vorname_value AS node_data_field_profile_nachname_field_profile_vorname_value, node_data_field_profile_nachname.field_profile_geburtstag_value AS node_data_field_profile_nachname_field_profile_geburtstag_value, (node_data_field_profile_geburtstag.field_profile_geburtstag_value ) AS cid1 FROM node node LEFT JOIN content_type_profile node_data_field_profile_status_value_0 ON node.vid = node_data_field_profile_status_value_0.vid AND node_data_field_profile_status_value_0.field_profile_status_value = 'aktiv' LEFT JOIN content_type_profile node_data_field_profile_nachname ON node.vid = node_data_field_profile_nachname.vid WHERE (node.type in ('profile')) AND (node_data_field_profile_status_value_0.field_profile_status_value = 'aktiv') in /var/www/drupal-test/html/sites/all/modules/views/includes/view.inc on line 735.

Here I have the conflict between node_data_field_profile_geburtstag <=> node_data_field_profile_nachname

When I copy the query, past it into phpmyadmin and replace "node_data_field_profile_geburtstag" with "node_data_field_profile_nachname" the query works.

By the way, why is "node_data_field_profile_nachname" used as the table alias and not "content_type_profile"?

Translation of some names: geburtstag = birthday, vorname = first name, nachname = last name

Best regards,

Christian

karens’s picture

karens commented
Status: Active » Fixed

This should now be fixed in -dev.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.