title: Oracle SQL Calculated Column - How do I reference a calculated column in another calculation in the same query?
body: <p>I want to use a calculated column in another calculation, within the same query.  But Oracle SQL does not seem to let me do this, to my knowledge.  These seems like a simple data maneuver, that i can easily do in Python, but Oracle SQL it seems challenging.</p>
  
  <p>For example:</p>
  
  <p><strong>table</strong></p>
  
  <p>date | value1 | value2</p>
  
  <p><strong>Desired Query Functionality</strong></p>
  
  <pre><code>SELECT
  date,
  value1,
  value2,
  (value2 * value1) AS value_calculated
  (value_calculated * 100) AS value_calculated_2
  (value_calculated_2 * 1000) AS value_calculated_3
  
  FROM  table
  </code></pre>
  
  <p>If this is not possible, I think Oracle should build in this into their software.  </p>
  
  <p>In other Stack Overflow questions/responses, CROSS APPLY has come up.  This is not working for me, since I have 10-20 calculated columns I need to create (all of which reference the other calculated columns).  </p>
  
  <p>Is there a simple PL/SQL function i can create to designate these as objects to use in other column calculations?</p>
  
  <p><strong>UPDATE TO QUESTION:</strong></p>
  
  <p>Here is the full code of what I'm doing, using Oracle Cloud Financials 19C</p>
  
  <pre><code>WITH segment_desc AS(
  SELECT DISTINCT
  flex_value,
  description
  FROM
  fnd_flex_values_vl
  WHERE description NOT IN('Default')
  
  ),
  balances_calc AS(
  SELECT
  gl_balances.period_name,
  gl_ledgers.ledger_id,
  gl_ledgers.name,
  gl_ledgers.currency_code||'-'||gl_balances.currency_code 
  func_ent_curr_pair,
  gl_code_combinations.code_combination_id,
  gl_code_combinations.segment1,
  gl_code_combinations.segment2,
  segment_desc.description center_description,
  gl_code_combinations.segment3,
  gl_code_combinations.segment4,
  gl_code_combinations.segment5,
  
  gl_balances.currency_code ent_curr_cd,
  -- balance logic swap functional/entered if ent=func
  CASE
      WHEN gl_balances.currency_code = gl_ledgers.currency_code
          THEN NVL(gl_balances.begin_balance_dr_beq,0) - 
  NVL(gl_balances.begin_balance_cr_beq,0) -- return functional "beq"
                  ELSE NVL(gl_balances.begin_balance_dr,0) - 
  NVL(gl_balances.begin_balance_cr,0) -- return entered "balances_cr/dr"
                      END
  ent_beg_balance,
  
  -- activity logic swap functional/entered if func=ent
  CASE
      WHEN gl_balances.currency_code = gl_ledgers.currency_code
      THEN NVL(gl_balances.period_net_dr_beq,0) - 
  NVL(gl_balances.period_net_cr_beq,0)   -- return functional "beq"
                  ELSE NVL(gl_balances.period_net_dr,0) - 
  NVL(gl_balances.period_net_cr,0) -- return entered "balances_cr/dr"
                      END
  ent_activity,
  
  gl_ledgers.currency_code func_curr_cd,
  
  NVL(gl_balances.begin_balance_dr_beq,0) - 
  NVL(gl_balances.begin_balance_cr_beq,0) func_beg_balance,
  NVL(gl_balances.period_net_dr_beq,0) - 
  NVL(gl_balances.period_net_cr_beq,0) func_activity,
  
  -- if statement currency equals
  CASE
      WHEN gl_balances.currency_code = gl_ledgers.currency_code
          THEN 'TRUE'
                  ELSE 'FALSE'
                      END
  entered_equals_functional,
  gl_balances.translated_flag
  
  FROM
  gl_balances, gl_code_combinations, gl_ledgers, segment_desc
  WHERE
  1 = 1
  AND gl_code_combinations.chart_of_accounts_id = '2001'
  AND (gl_balances.translated_flag IN ('N','R') OR 
  gl_balances.translated_flag IS NULL)
  AND gl_balances.code_combination_id = 
  gl_code_combinations.code_combination_id
  AND gl_ledgers.ledger_id = gl_balances.ledger_id
  AND  segment_desc.flex_value (+) = gl_code_combinations.segment2
  
  ),
  
  
  balances_calc_test AS(
  SELECT 
  balances_calc.period_name,
  balances_calc.segment1,
  balances_calc.segment2,
  balances_calc.segment3,
  balances_calc.segment4,
  **calc_column.func_end_balance,**
  **calc_column_2.custom_calc**
  
  FROM balances_calc
  
  **CROSS APPLY(SELECT func_beg_balance + func_activity AS func_end_balance 
  FROM balances_calc) calc_column**
  **CROSS APPLY(SELECT func_end_balance - ent_activity AS custom_calc FROM 
  balances_calc) calc_column_2**
  )
  
  SELECT * FROM balances_calc_test
  </code></pre>
  
  <p><strong>This returns errors in the query</strong></p>
  
  <p>I get repeating values in the <strong>calc_column.func_end_balance</strong> column.</p>
  
  <p>Should I use a WHERE statement to join? </p>
  
  <p>Again, I think there needs to be an easier solution to having derived column calculations.  Python Pandas can do this in 2 seconds.</p>