8 Replies Latest reply: Jul 31, 2013 3:58 PM by rp0428 RSS

    Derived Column data as New Column in SELECT

    Aparna16

      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

          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

            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

              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

                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

                  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

                    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

                      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

                         

                        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.