8 Replies Latest reply on Dec 11, 2018 1:12 PM by John_K

    Is there a better way to write the sql ?

    3380214

      Hi,

      Is there a better way to write the below sql which used in the function .

       

      CREATE OR REPLACE FUNCTION APPS.get_disposal_value (p_asset_id IN Number,

                                                     p_period IN VARCHAR2)

                                                --     p_transaction_code IN VARCHAR2,

                                                 --    p_transaction_type IN VARCHAR2)

      RETURN NUMBER

      IS

      lc_cost_addition NUMBER;

       

       

      Cursor get_code

      is

      select transaction_type_code from fa_transaction_headers where asset_id=p_asset_id; 

      BEGIN

      for rec_get_code in get_code

      loop

      IF rec_get_code.transaction_type_code ='FULL RETIREMENT' THEN

      BEGIN

      select nvl(current_cost,0) into lc_cost_addition from

                   FA_FINANCIAL_INQUIRY_COST_V

                  where

                  TRANSACTION_HEADER_ID_IN =

                      (select max(transaction_header_id) from fa_transaction_headers where transaction_header_id

                              NOT IN(select max(transaction_header_id) from fa_transaction_headers where  asset_id=p_asset_id)

                      and asset_id=p_asset_id -- 907628, 914289, 924101

                      and TRANSACTION_TYPE_CODE='ADDITION'

                      AND period_entered<=p_period

                      );

                      END;

                     

       

       

      END IF;

      end loop;

          return     nvl(lc_cost_addition,0);

      END get_disposal_value;

       

      Please do the needful ... as this is taking more than 30 minutes

       

      Thanks

        • 1. Re: Is there a better way to write the sql ?
          Stefan Jager

          Why are you looping over all your rows, only to actually do something when a certain condition is true? Add that condition to your where clause.

          Why do this inside a function? Why not straight sql?

          You use nvl twice. One of those is not needed.

           

          Just a few of my observations. But mostly: Re: 2. How do I ask a question on the forums?

          • 2. Re: Is there a better way to write the sql ?
            BEDE

            I'd say something like below.

             

            CREATE OR REPLACE FUNCTION APPS.get_disposal_value (p_asset_id IN Number,
                                                           p_period IN VARCHAR2)
                                                      --     p_transaction_code IN VARCHAR2,
                                                       --    p_transaction_type IN VARCHAR2)
            RETURN NUMBER
            IS
            lc_cost_addition NUMBER;


            BEGIN

            begin
              with rec_get_code as (
                select transaction_type_code from fa_transaction_headers where asset_id=p_asset_id
              )
              select nvl(current_cost,0) into lc_cost_addition
              from rec_get_code
              ,FA_FINANCIAL_INQUIRY_COST_V 
              where rec_get_code.transaction_type_code ='FULL RETIREMENT'
               where
                          TRANSACTION_HEADER_ID_IN =
                              (select max(transaction_header_id) from fa_transaction_headers where transaction_header_id
                                      NOT IN(select max(transaction_header_id) from fa_transaction_headers where  asset_id=p_asset_id)
                              and asset_id=p_asset_id -- 907628, 914289, 924101
                              and TRANSACTION_TYPE_CODE='ADDITION'
                              AND period_entered<=p_period
                              );
            exception
              when no_data_found then lc_cost_addition:=0;
              when too_many_rows then null; --- what to do when too many rows???

            end;
            return     nvl(lc_cost_addition,0);
            END get_disposal_value;

             

            But what is to be the dependency between what is selected from FA_FINANCIAL_INQUIRY_COST_V and rec_get_code??? There appear to be no join. How do you handle no_data_found and how do you handle too_many_rows?

             

            • 3. Re: Is there a better way to write the sql ?
              Stefan Jager

              BEDE wrote:

               

              how do you handle too_many_rows?

               

              Oh yes, I missed that one. In OP's own code he or she handles it by simply overwriting the previous result if there is more than one...

              And wouldn't the nvl cover no_data_found?

              • 4. Re: Is there a better way to write the sql ?
                BEDE

                no_data_found if encountered when calling the function in a SQL statement will make the function return null if that exception is not intercepted in the function body. If the function is called in some pl/sql block, then a no_data_found unhandled will cause the exception to be raised to the calling pl/sql block, where it should be handled somehow (how?)

                • 5. Re: Is there a better way to write the sql ?
                  Manik

                  Not sure and should not really comment without knowing the data and data objects involved in your query, but just a try is better than silently watching the thread, hence I wanted to ask you if this is what you are trying to get ?

                  <Saying so I haven't tested it by any means. Just replying may be it would trigger something in your thought process.>

                   

                  SELECT NVL (current_cost, 0)
                    FROM FA_FINANCIAL_INQUIRY_COST_V a,
                         (SELECT MAX (transaction_header_id)
                            FROM (SELECT transaction_header_id, DENSE_RANK () OVER (ORDER BY transaction_header_id DESC) rnk
                                    FROM fa_transaction_headers
                                   WHERE asset_id = p_asset_id)
                           WHERE rnk = 2 AND TRANSACTION_TYPE_CODE = 'ADDITION' AND period_entered <= p_period) b
                  WHERE a.TRANSACTION_HEADER_ID_IN = b.transaction_header_id AND b.transaction_type_code = 'FULL RETIREMENT';
                  

                   

                  Cheers,

                  Manik.

                  • 6. Re: Is there a better way to write the sql ?
                    Cookiemonster76

                    Is there only ever going to be one row in fa_transaction_headers with transaction_type_code ='FULL RETIREMENT'?

                    If there is then the loop is completely pointless - just add a check on transaction_type_code to the select into and remove the loop and cursor.

                    If there can be more than one then you're going to be running the inner select multiple times and each time you will overwrite the results from the previous time. Which is obviously wrong (especially since the cursor doesn't have an order by, if it did then the code might just be inefficient, depending on what the logic should be).

                     

                    Whatever you are trying to do can almost certainly be done in a single select statement, but we can't really tell you what that select should be since your current code doesn't actually make sense and you've not explained the requirements.

                    • 7. Re: Is there a better way to write the sql ?
                      3380214

                      Hi,

                      Almost you are correct but not sure whats the wrong in inner query ...

                      I am working on it .... Can you Please check once on the inner query.

                      Any how Thanks a lot to all and especially to you

                      • 8. Re: Is there a better way to write the sql ?
                        John_K

                        You are looping over rows - however you have no "Order By" on the cursor. So you're only ever going to get the value of the last row in "lc_cost_addition", and the last row is non-determinate - it could in theory change each time you run the query!