pivottable
"Fred" wrote ...
I have vba code that creates a pivottable with an
external informix database as the source.
Part of the SQL statement is listed below:
SELECT loc_id, ........
invt_mthend_qty*invt_avg_cost
FROM informix.grp_name grp_name......
WHERE item.item_grp_nbr = grp_name.grp_nbr ......
When the code attempts to calculate the pivottable, the field
"inv_total.invt_mthend_qty*invt_avg_cost" is
inconsistently displayed as
inv_total.invt_mthend_qty*invt_avg_cost
OR EXPR.
Any thoughts on why the reference is not consistent?
Consider this:
SELECT ColumnA * ColumnB
It is an expression using two columns, rather than a column in its own
right. It doesn't have a name but needs one: each column in the
results set must have a unique name so it can be identified and
distinguished. You haven't given it a name so the data provider has
done it for you, I guess by using the expression text or EXPR, EXPR1,
EXPR2 etc when the expression is too long or not unique.
Why the naming convention is inconsistent, I cannot speculate.
However, if you give the expression an explicit name, I assume it will
always be consistent. To give the expression a name, use an alias. You
did so with your table names
FROM informix.grp_name grp_name
which can be more clearly written as
FROM informix.grp_name AS grp_name
Hence, use something like:
SELECT invt_mthend_qty*invt_avg_cost AS total_mth_yield
using a meaningful alias, of course.
Jamie.
--
|