When referencing another calculated field inside the formula for a calculated field, display the other field's name and not the other field's code.
Example:
I have a calculated field "MA" attached to the Inventory table defined as:
Inventory.Qty_Sold_Roll_YTD_Demand / 12
I go to create another calculated field "MA Cost". It will look like this when the MA field is selected and then is multiplied by the cost:
(Inventory.Qty_Sold_Roll_YTD_Demand / 12) * Inventory.Unit_Cost
Propose that it look like this in the Calculated Field editor:
[MA] * Inventory.Unit_Cost
The SQL display would not be changed.
You can see how in more complex formulas the use of the shorter column names would be much easier to read/debug...especially if some of those reference calculated fields were defined with extensive case statements, etc.
Another real-world example:
If able to reference other calculated fields by name:
if (TVIM_Target > (AVAIL + OPO)) {Round ((TVIM_Target - (AVAIL + OPO)) / Mult,0) * Mult} else {0}
TOAD Currently:
CASE WHEN CASE WHEN AL2.FLAG_OVRSZ_SPEC_HNDLG != '' OR AL2.FLAG_TRUCK != '' THEN AL4.QTY_REORDER_TO_POINT WHEN 3.33 * ROUND(CASE WHEN ROUND((TRUNC(SYSDATE) - AL2.DATE_HISTORY)/30.42,1) < 12 THEN (AL1.SN_QTY_ROLL_YTD_SALES_RETAIL + AL1.SN_QTY_ROLL_YTD_SALE_MAIL_ORDR + AL1.SN_QTY_ROLL_YTD_SALE_WHOLESALE - AL1.SN_QTY_ROLL_YTD_SALE_CANCELLED - AL1.SN_QTY_ROLL_YTD_SALES_REFUSED - AL1.SN_QTY_ROLL_YTD_SALES_RETURNED )/ ROUND((TRUNC(SYSDATE) - AL2.DATE_HISTORY)/30.42,1) ELSE ( AL1.SN_QTY_ROLL_YTD_SALES_RETAIL + AL1.SN_QTY_ROLL_YTD_SALE_MAIL_ORDR + AL1.SN_QTY_ROLL_YTD_SALE_WHOLESALE - AL1.SN_QTY_ROLL_YTD_SALE_CANCELLED - AL1.SN_QTY_ROLL_YTD_SALES_REFUSED - AL1.SN_QTY_ROLL_YTD_SALES_RETURNED )/12 END,1) > AL4.QTY_REORDER_TO_POINT THEN ROUND( 3.33 * ROUND(CASE WHEN ROUND((TRUNC(SYSDATE) - AL2.DATE_HISTORY)/30.42,1) < 12 THEN (AL1.SN_QTY_ROLL_YTD_SALES_RETAIL + AL1.SN_QTY_ROLL_YTD_SALE_MAIL_ORDR + AL1.SN_QTY_ROLL_YTD_SALE_WHOLESALE - AL1.SN_QTY_ROLL_YTD_SALE_CANCELLED - AL1.SN_QTY_ROLL_YTD_SALES_REFUSED - AL1.SN_QTY_ROLL_YTD_SALES_RETURNED )/ ROUND((TRUNC(SYSDATE) - AL2.DATE_HISTORY)/30.42,1) ELSE ( AL1.SN_QTY_ROLL_YTD_SALES_RETAIL + AL1.SN_QTY_ROLL_YTD_SALE_MAIL_ORDR + AL1.SN_QTY_ROLL_YTD_SALE_WHOLESALE - AL1.SN_QTY_ROLL_YTD_SALE_CANCELLED - AL1.SN_QTY_ROLL_YTD_SALES_REFUSED - AL1.SN_QTY_ROLL_YTD_SALES_RETURNED )/12 END,1) ,0) ELSE AL4.QTY_REORDER_TO_POINT END > ( AL1.SN_QTY_AVAILABLE_TO_SELL + AL1.SN_QTY_OPEN_PO ) THEN ROUND (( CASE WHEN AL2.FLAG_OVRSZ_SPEC_HNDLG != '' OR AL2.FLAG_TRUCK != '' THEN AL4.QTY_REORDER_TO_POINT WHEN 3.33 * ROUND(CASE WHEN ROUND((TRUNC(SYSDATE) - AL2.DATE_HISTORY)/30.42,1) < 12 THEN (AL1.SN_QTY_ROLL_YTD_SALES_RETAIL + AL1.SN_QTY_ROLL_YTD_SALE_MAIL_ORDR + AL1.SN_QTY_ROLL_YTD_SALE_WHOLESALE - AL1.SN_QTY_ROLL_YTD_SALE_CANCELLED - AL1.SN_QTY_ROLL_YTD_SALES_REFUSED - AL1.SN_QTY_ROLL_YTD_SALES_RETURNED )/ ROUND((TRUNC(SYSDATE) - AL2.DATE_HISTORY)/30.42,1) ELSE ( AL1.SN_QTY_ROLL_YTD_SALES_RETAIL + AL1.SN_QTY_ROLL_YTD_SALE_MAIL_ORDR + AL1.SN_QTY_ROLL_YTD_SALE_WHOLESALE - AL1.SN_QTY_ROLL_YTD_SALE_CANCELLED - AL1.SN_QTY_ROLL_YTD_SALES_REFUSED - AL1.SN_QTY_ROLL_YTD_SALES_RETURNED )/12 END,1) > AL4.QTY_REORDER_TO_POINT THEN ROUND( 3.33 * ROUND(CASE WHEN ROUND((TRUNC(SYSDATE) - AL2.DATE_HISTORY)/30.42,1) < 12 THEN (AL1.SN_QTY_ROLL_YTD_SALES_RETAIL + AL1.SN_QTY_ROLL_YTD_SALE_MAIL_ORDR + AL1.SN_QTY_ROLL_YTD_SALE_WHOLESALE - AL1.SN_QTY_ROLL_YTD_SALE_CANCELLED - AL1.SN_QTY_ROLL_YTD_SALES_REFUSED - AL1.SN_QTY_ROLL_YTD_SALES_RETURNED )/ ROUND((TRUNC(SYSDATE) - AL2.DATE_HISTORY)/30.42,1) ELSE ( AL1.SN_QTY_ROLL_YTD_SALES_RETAIL + AL1.SN_QTY_ROLL_YTD_SALE_MAIL_ORDR + AL1.SN_QTY_ROLL_YTD_SALE_WHOLESALE - AL1.SN_QTY_ROLL_YTD_SALE_CANCELLED - AL1.SN_QTY_ROLL_YTD_SALES_REFUSED - AL1.SN_QTY_ROLL_YTD_SALES_RETURNED )/12 END,1) ,0) ELSE AL4.QTY_REORDER_TO_POINT END - ( AL1.SN_QTY_AVAILABLE_TO_SELL + AL1.SN_QTY_OPEN_PO ))/ CASE WHEN AL2.QTY_OF_PURCH_MULT > AL2.QTY_OF_SALES_MULT THEN AL2.QTY_OF_PURCH_MULT ELSE AL2.QTY_OF_SALES_MULT END ,0) * CASE WHEN AL2.QTY_OF_PURCH_MULT > AL2.QTY_OF_SALES_MULT THEN AL2.QTY_OF_PURCH_MULT ELSE AL2.QTY_OF_SALES_MULT END ELSE 0 END
1 Like
I worked with this some more and formatted it with tabs/spacing to make it more readable. This gave me an idea... Case statements get the ability to expand/collapse the code with the little buttons in the left margin. This is helpful, but what if something similar could be done when referencing the other calculated field? The expand/collapse functionality would be good, or perhaps the name of the calculated column would be visible and formatted in such a way as to indicate that double-clicking the text would cause it to expand. Something like that would be helpful. Thanks! 
What about double-clicking the name of the referenced calculated column, this would display the code of that column in a new floating window. The code could be viewed, but not modified. Any modifications would need to be done back in that calculated column's original window.
1 Like
Just spoke with Toad Maven Greg Martin, and he has us exploring Transform & Cleanse to help with this. Will report back soon.
I wanted to not only vote up this idea, but also say I have experienced the same issues when developing new calculated fields. It creates some very confusing logic to not be able to look at a calculated field in the Build functionality and see what calculated field is being referenced. It would make troubleshooting issues with a calculation much faster as you do not have to backtrack and view each calculation to see which is using that formula. Any updates on this in the backlog of ideas would be appreciated.
1 Like
After running the query we sent the results to Transform & Cleanse. We created a new column and used the existing calculated columns to create a new formula...much easier to comprehend and track, but same functionality in a query is still desired.
Some database languages have this type of functionality, SQL server for example have the ability to create a Cross apply and Common table expression (CTE). Some languages enable this by default. But it would be nice if TOAD data point gave this as an internal option as well for languages that do not support such a feature.