SQL Language (MOSC)

MOSC Banner

Using a calced value for later calculations all in the same SQL

edited Aug 25, 2010 5:53AM in SQL Language (MOSC) 6 commentsAnswered
I need to write a SQL which does several calulcations.  Most calculations can be done using the result of an earlier calculation.  I'd like to find a way of using that earlier calculation rather than do the whole calculation over and over for each column.

My simplifed example is:

select (NVL(CTLA.QUANTITY_INVOICED,CTLA.QUANTITY_CREDITED) * (select 1/conversion_rate
        from mtl_uom_class_conversions
        where from_uom_code = CTLA.UOM_CODE
        and to_uom_code = 'LT'
        and inventory_item_id = MSI.INVENTORY_ITEM_ID) ) CONVERSION_QTY
       ,CONVERSION_QTY * cic.item_cost CONV_ITEM_COST
       ,CONVERSION_QTY * cic.material_cost CONV_MAT_COST

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center