Toad World® Forums

Display Calculated Field's Name instead of Code

#1

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.

#2

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

#3

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! :slight_smile:

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.

#4

Just spoke with Toad Maven Greg Martin, and he has us exploring Transform & Cleanse to help with this. Will report back soon. :slight_smile: