In my query am using the derived column values for other coulumns only once.
is used to calculate the next column TOT_FACT_DATA.
So, using inline view or a subquery or normal select with calculating the fields again ..doesnt make much difference.. please correct me if am wrong.
If you are using it WITH clause, you might need to look at the syntax as well. It's a different syntax when we use WITH clause for INSERT queries.
Other way of doing it would be, to have this in-line view with the required data in the FROM clause itself. Something like,
NSERT INTO tbl_fact_effort_lvl_data
( ............... )
,dummy.TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA
FROM tbl_fact tf,
(( SELECT lov_num_val TOT_YEARLY_PROD_HOURS
WHERE lov_type = 'FREQUENCY'
) * (SELECT SUM(pph_task) TOT_YEARLY_PROD_HOURS
WHERE report_id = ria.report_id )) dummy
LEFT JOIN ......... ;
So, here I want to use column alias TOT_YEARLY_PROD_HOURS as another column to derive another column value TOT_FACT_DATA.
In the SELECT clause there IS NO column named TOT_YEARLY_PROD_HOURS. That is just a name you are giving to the column in the result set.
Your query is equivalent to trying to do this:
select 1 myNum, myNum + 2 myNewNum from dual
ORA-00904: "MYNUM": invalid identifier
You can't use aliases like that.
insert into tbl_fact_effort_lvl_data
x.tot_yearly_prod_hours * tf.fac_value tot_fact_data,
from tbl_fact tf,
trp.sum_pph_task * trd.lov_num_val tot_yearly_prod_hours
from (select report_id,sum(pph_task) sum_pph_task
group by report_id
where lov_type = 'FREQUENCY'
on ria.report_id = x.report_id
That is exactly what Aparna is trying to say. She want's to use it that way but unable to do so because it obviously not allowed. If it's allowed there is no meaning to her question in the first place because it would be allowed then.
She wants us to find a way to do achieve this.
Why are you telling me this? OP is the one you should address your remarks to.
And OP already has a way to get that value; it was posted at the start of the thread. If you want the value multiple times you just have to create it multiple times.
As I said above this is similar to what OP is trying to do
select sal, sal * 1.1 newSal, newSal * 1.2 desiredSal from emp
And this is what works
select sal, sal * 1.1 newSal, (sal * 1.1) * 1.2 desiredSal from emp
Just repeat the first expression in the second expression. I didn't point that out since it should have been obvious to OP and everyone else that the first expression can be repeated.