Can I reference one calculated field in another?

Toad Data Point & Toad Intelligence Central
1

Example:

I am trying to convert from Hyperion to something other than Hyperion. Corporate has decided that TDP is a viable alternative.

I have one calculated field “F2_Address” that just pulls the first four characters of an overall address field, attached to the “Units” table.

I have a second calculated field “F2_Vertex_Check” that looks at “F2_Address” to see if that value is ‘8002’. There’s also a 3rd calculated field that references the 2nd, and a 4th that references the 3rd.

Other than duplicating the code of “F2_Address” inside “F2_Vertex_Check”, is there a way to reference “F2_Address” by its Field Alias?

I’ve tried F2_Address, Units.F2_Address, Units!F2_Address, Units@F2_Address. I can’t think of any other esoteric form to reference it. And, it won’t get past the compiler when I try to execute the SQL.

2

One way is to use a subquery to “prep” all the calculated fields from a table and then reference the field from the subquery. It is still a workaround though.

3

If you are using query builder, you can use the alias name of the calculated field, but the sql will still insert the code of the 1st calculation. Sql cannot use the calculated field until it is calculated. So you would have to use inner and outer queries to use a calculated field.

4

I use calculated fields in the result sets in Hyperion and Access all the time.

The current problem is that I can’t build calculated fields in the RESULTS (i.e., the backend) for TOAD. I can only build calculated fields in the query (the frontend). This creates problems where we’ve got multi-step calculations that tell us different things, and we have A LOT of these. I don’t want to have to copy the result set into Excel or Access to do anything useful with it; that defeats the purpose. I also don’t want to be required to duplicate the code over and over again, just so that I can create a workaround.

5

You have a couple of choices due to sql limitations. Nest the calculations in the field (can get quite messy with several levels and complex calculations, which is probably why you don’t want to do that) or nest subqueries:

simple but can get messy

SELECT HumanaMember.RPT_PE,

SUBSTRING (HumanaMember.GK_CTRCT, 1, 11) AS ProvID,

RTRIM (SUBSTRING (HumanaMember.GK_CTRCT, 1, 11)) AS TrimmedProvID

RTRIM (SUBSTRING (HumanaMember.GK_CTRCT, 1, 11)) + HumanaMember.RPT_PE ProvReportPeriod

FROM dwABQHP.stage.HumanaMember HumanaMember

or

A lot neater but may have performance issues with a ton of data

select Sub2.RPT_PE, Sub2.ProvID, sub2.TrimmedProvID, Sub2.TrimmedProvID + Sub2.RPT_PE ProvReportPeriod

from

(Select Sub1.RPT_PE, Sub1.ProvID, RTRIM(Sub1.ProvID) AS TrimmedProvID

From

(SELECT HumanaMember.RPT_PE,

SUBSTRING (HumanaMember.GK_CTRCT, 1, 11) AS ProvID,

FROM dwABQHP.stage.HumanaMember HumanaMember

) Sub1

)Sub2

A third option would be to look into Toad Reports (It starts out with a sql query and then you can add calculated fields but I don’t know what the capabilities are, only used it once).

Fourth select into an Excel template file (already has the calculated fields in it, you just query the raw data). Best to put the final calculations on one sheet (1st tab) and the raw data on a 2nd sheet (with a static name). The first sheet uses formulas that reference the raw data fields on the 2nd sheet or other sheets/fields for multiple nested calculations. In the select to file you can tell which Excel sheet to clear and update with the query results, just make sure you don’t flag the file overwrite option. There is also an option for running a macro which can allow you to do a whole ton of stuff.

6

Once you have your query ready, run it, and then send it to a Toad Pivot Grid. You can add calculated fields in the pivot. Hope that works for you. :slight_smile:

7

You can also send the results with the single calculated field (right click anywhere in the result columns) to the “Clean and Transform” and perform calculations on the calculated fields there. After adding the calculated fields you can save that and later run or schedule as needed as it will still contain the SQL.

So depending on the preferred output, level, stacking and complexity of the calculations you can:

-use subqueries, maybe even adding subqueries in subqueries in the SQL statement

-develop the SQL with just the first step calculations and send the results to:Pivot, Dimensional View or Clean and Transform, to do further calculation on calculations.

-if the first step calculation is something you might use as basis for different reports you can think of creating a local Toad View and use that view as table in those reports.

8

We offer adding calculated fields to grid in the Data Cleansing module. here you can manipulate data without having to use SQL