pivottable

[Guest]

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?

Thanks

Fred

 

[Jamie Collins]

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.

--

 

[Guest]

Thankyou Jamie.

I successfully made the changes with the AS statement.

I have modified the code a couple of times using
inv_total.invt_mthend_qty*invt_avg_cost or EXPR as the field, when the code
would error out.

Hopefully the AS statement resolves it and I don't have to guess if it will
work this time or the next.