This discussion is archived
8 Replies Latest reply: Jul 31, 2013 1:58 PM by rp0428 RSS

Derived Column data as New Column in SELECT

Aparna16 Newbie
Currently Being Moderated

Hello,

I have a INSERT query which is happening with a SELECT query.

 

===================================================

INSERT INTO tbl_fact_effort_lvl_data

                              (  ...............       )

                        SELECT ria.report_id

                              ,report_status

                              :

                              :

                             ,((SELECT lov_num_val

                                   FROM tbl_reference_data

                                WHERE lov_type = 'FREQUENCY'

                                 ) * (SELECT SUM(pph_task)

                                         FROM tbl_ri_process

                                      WHERE report_id = ria.report_id )) TOT_YEARLY_PROD_HOURS

                  

                              ,TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA

 

                              ,location_id                                                   

                          

                        FROM tbl_fact tf

      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.

 

Thanks !!

Aparna

  • 1. Re: Derived Column data as New Column in SELECT
    Purvesh K Guru
    Currently Being Moderated

    Use it as an In-line View or a Sub Query factoring With clause.

  • 2. Re: Derived Column data as New Column in SELECT
    Aparna16 Newbie
    Currently Being Moderated

    Thanks Purvesh.

    In my query am using the derived column values for other coulumns only once.

    ex. TOT_YEARLY_PROD_HOURS

    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.

     

  • 3. Re: Derived Column data as New Column in SELECT
    Purvesh K Guru
    Currently Being Moderated

    Yes, it should not make any difference when you are selecting the data from Inline view. However, to certify it, only you can do so as you have access to the tables, data and the exact query.

  • 4. Re: Derived Column data as New Column in SELECT
    Ishan Journeyer
    Currently Being Moderated

    Aparna,

     

    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

                                  (  ...............       )

                            SELECT ria.report_id

                                  ,report_status

                                  :

                                  :

                                 ,dummy.TOT_YEARLY_PROD_HOURS

                                   ,dummy.TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA

                                  ,location_id                                                  

                             

                            FROM tbl_fact tf,

                                       ((     SELECT lov_num_val                     TOT_YEARLY_PROD_HOURS

                                              FROM tbl_reference_data

                                             WHERE lov_type = 'FREQUENCY'

                                          ) * (SELECT SUM(pph_task) TOT_YEARLY_PROD_HOURS

                                             FROM tbl_ri_process

                                          WHERE report_id = ria.report_id )) dummy

     

          LEFT JOIN ......... ;

     

     

    Thanks,

    Ishan

  • 5. Re: Derived Column data as New Column in SELECT
    rp0428 Guru
    Currently Being Moderated

    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.

  • 6. Re: Derived Column data as New Column in SELECT
    Ishan Journeyer
    Currently Being Moderated

    RP,

     

    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.

     

     

    Thanks,

    Ishan

  • 7. Re: Derived Column data as New Column in SELECT
    Etbin Guru
    Currently Being Moderated

    Maybe

     

    insert into tbl_fact_effort_lvl_data

    select ria.report_id,

           report_status,

           ...

           x.tot_yearly_prod_hours,

           x.tot_yearly_prod_hours * tf.fac_value tot_fact_data,

           location_id                                                  

      from tbl_fact tf,

           left join

           ... ria

        on ...

           left join

           (select trp.report_id,

                   trp.sum_pph_task * trd.lov_num_val tot_yearly_prod_hours

              from (select report_id,sum(pph_task) sum_pph_task

                      from tbl_ri_process

                     group by report_id

                   ) trp

                   cross join

                   (select lov_num_val

                      from tbl_reference_data

                     where lov_type = 'FREQUENCY'

                   ) trd    

           ) x

        on ria.report_id = x.report_id

     

    Regards

     

    Etbin

  • 8. Re: Derived Column data as New Column in SELECT
    rp0428 Guru
    Currently Being Moderated

     

    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

     

     

    ORA-00904: "NEWSAL": invalid identifier

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points