10 Replies Latest reply on Mar 28, 2013 12:04 PM by ascheffer

    Minimise the SQL code

    982895
      Hi Gurus,
      I want to minimise the code, the below is a part of procedure which contains almost 2000 of lines. So, taking part of the code and tryng to minimise.
      If it is possible to minimise the below code. Please help me
      IF IV_INDENTCODE IS NOT NULL THEN
          BEGIN
          
            SELECT COUNT(1)
              INTO V_COUNT
              FROM STOCKTRANSACTION ST
             WHERE ST.INDENTCODE = IV_INDENTCODE
               AND ST.TRANSACTIONCODE = 4;
          EXCEPTION
            WHEN OTHERS THEN
              V_COUNT := NULL;
           END;
        
          BEGIN
          
            SELECT ST.RETURNCODE
              INTO V_RETURNCODE
              FROM STOCKTRANSACTION ST
             WHERE ST.INDENTCODE = IV_INDENTCODE
               AND ST.TRANSACTIONCODE = 4;
          EXCEPTION
            WHEN OTHERS THEN
              V_RETURNCODE := NULL;
          END;
        END IF;
      
        IF IV_ISSUECODE IS NOT NULL THEN
          BEGIN
            SELECT COUNT(*)
              INTO V_COUNT
              FROM STOCKTRANSACTION ST
             WHERE ST.ISSUECODE = IV_ISSUECODE
               AND ST.TRANSACTIONCODE = 4;
          EXCEPTION
            WHEN OTHERS THEN
              V_COUNT := NULL;
          END;
          BEGIN
          
            SELECT ST.RETURNCODE
              INTO V_RETURNCODE
              FROM STOCKTRANSACTION ST
             WHERE ST.ISSUECODE = IV_ISSUECODE
               AND ST.TRANSACTIONCODE = 4;
          EXCEPTION
            WHEN OTHERS THEN
              V_RETURNCODE := NULL;
          END;
        END IF;
        
      Thanks in advance,
        • 1. Re: Minimise the SQL code
          Nitesh.
          I think by seeing this code i can just suggest as exception blocks can be removed or else you have to atleast post that particular procedure ..



          BEGIN
          IF iv_indentcode IS NOT NULL
          THEN
          BEGIN
          SELECT COUNT (1)
          INTO v_count
          FROM stocktransaction st
          WHERE st.indentcode = iv_indentcode AND st.transactioncode = 4;

          SELECT st.returncode
          INTO v_returncode
          FROM stocktransaction st
          WHERE st.indentcode = iv_indentcode AND st.transactioncode = 4;
          EXCEPTION
          WHEN OTHERS
          THEN
          v_returncode := NULL;
          END;
          END IF;

          IF iv_issuecode IS NOT NULL
          THEN
          BEGIN
          SELECT COUNT (*)
          INTO v_count
          FROM stocktransaction st
          WHERE st.issuecode = iv_issuecode AND st.transactioncode = 4;

          SELECT st.returncode
          INTO v_returncode
          FROM stocktransaction st
          WHERE st.issuecode = iv_issuecode AND st.transactioncode = 4;
          EXCEPTION
          WHEN OTHERS
          THEN
          v_returncode := NULL;
          END;
          END IF;
          END;


          I think if there is no record found then count will take 0 automatically so no need of exception handler for checking whether data's exist or not and common exception handler is enough ..


          Regards..
          • 2. Re: Minimise the SQL code
            Purvesh K
            I think below is a way to achieve:
            IF IV_INDENTCODE IS NOT NULL THEN
                BEGIN
                
                  SELECT ST.RETURNCODE, COUNT(*)         --> COUNT(*) is no better than COUNT(1). They are same
                    INTO V_RETURNCODE, V_COUNT
                    FROM STOCKTRANSACTION ST
                   WHERE ST.INDENTCODE = IV_INDENTCODE
                     AND ST.TRANSACTIONCODE = 4 group by ST.RETURNCODE;
                EXCEPTION
                  WHEN OTHERS THEN
                    V_COUNT := NULL; V_RETURNCODE := null;
                 END;
              
               END IF;
             
              IF IV_ISSUECODE IS NOT NULL THEN
                BEGIN
                  SELECT ST.RETURNCODE, COUNT(*)
                    INTO V_RETURNCODE, V_COUNT
                    FROM STOCKTRANSACTION ST
                   WHERE ST.ISSUECODE = IV_ISSUECODE
                     AND ST.TRANSACTIONCODE = 4 group by ST.RETURNCODE;
                EXCEPTION
                  WHEN OTHERS THEN
                    V_COUNT := NULL; V_RETURNCODE := null;
                END;
            
              END IF;
            If you have to combine the Two queries, into One, then Dynamic SQL is a way to do it; but that definitely is not recommended for such trivial matters. I could not think of another way to combine the two queries and you conditionally use the columns.
            1 person found this helpful
            • 3. Re: Minimise the SQL code
              Marwim
              Hello,

              this code seems horrible to me.

              First: Why do you need an exception handler for the COUNT queries? What exception could possibly be thrown?

              Second: Why do you count and not use it? If count = 0, then you don't have to execute the returncode query because you will get NO_DATA_FOUND. If count > 1, then you dont' have to execute the returncode query because you will get TOO_MANY ROWS. So why not simply execute the returncode query?

              You should tell us what this procedure is supposed to do, then we might be able to show a better solution.

              Regards
              Marcus
              • 4. Re: Minimise the SQL code
                Marwim
                      SELECT ST.RETURNCODE, COUNT(*)  
                        INTO V_RETURNCODE, V_COUNT
                        FROM STOCKTRANSACTION ST
                       WHERE ST.INDENTCODE = IV_INDENTCODE
                         AND ST.TRANSACTIONCODE = 4 group by ST.RETURNCODE;
                This is not the same logic. You might get an error because you have two different returncodes, while a simple count would return 2 instead of an exception
                • 5. Re: Minimise the SQL code
                  982895
                  Hi Marwin,

                  Yes this code is really Horrible. I am not able to undrstand also. The count mostly return 1 only.. Actually this procedure jus populated the data in the UI based on the given INPUT. If I post the whole Procedure, You will get confused and don understand also.. anyways I will post if I need any help again..

                  Thanks
                  • 6. Re: Minimise the SQL code
                    Purvesh K
                    Marwim wrote:
                          SELECT ST.RETURNCODE, COUNT(*)  
                    INTO V_RETURNCODE, V_COUNT
                    FROM STOCKTRANSACTION ST
                    WHERE ST.INDENTCODE = IV_INDENTCODE
                    AND ST.TRANSACTIONCODE = 4 group by ST.RETURNCODE;
                    This is not the same logic. You might get an error because you have two different returncodes, while a simple count would return 2 instead of an exception
                    Yes, looking at the Original Query, where some column is filtered for value 4, and is still fetched into a Scalar variable, hinted that perhaps there exists only one value for the filter combination. And hence, a Grouped count should also give the same results.

                    But if that is not the scenario, then OP needs to put more details for us to think and give any suggestion.
                    • 7. Re: Minimise the SQL code
                      Karthick2003
                      See if this works for you
                      select count(decode(indentcode, iv_indentcode, indentcode, null)) indentcode_count
                           , max  (decode(indentcode, iv_indentcode, returncode, null)) indentcode_returncode
                           , count(decode(issuecode , iv_issuecode , issuecode , null)) issuecode_count
                           , max  (decode(issuecode , iv_issuecode , returncode, null)) issuecode_returncode
                        from stocktransaction 
                       where transactioncode = 4;
                      • 8. Re: Minimise the SQL code
                        982895
                        Hi Karthick, Can You please explain me the code which u gave.
                        • 9. Re: Minimise the SQL code
                          Karthick2003
                          where transactioncode = 4;
                          One common thing in all your SELECT is the TRANSACTIONCODE condition. So first i filtered your table for that.
                          count(decode(indentcode, iv_indentcode, indentcode, null)) indentcode_count
                          count(decode(issuecode , iv_issuecode , issuecode , null)) issuecode_count
                          Then i manipulated the select list. COUNT does not count NULL value so i moved the where conditon of your query into a DECODE statement and did the COUNT for both the column INDENTCODE nnd ISSUECODE with your condition.
                          max (decode(indentcode, iv_indentcode, returncode, null)) indentcode_returncode
                          max (decode(issuecode , iv_issuecode , returncode, null)) issuecode_returncode
                          And based on your query i assumed that you are expecing a unique RETURNCODE for the filter condition. So i use MAX along with decode and got the RETURNCODE.

                          But still there are few issue like if a condition like "where issuecode = iv_issuecode" return more than 1 row then in your code the RETURNCODE will be set as NULL. But in my SQL it will be set to the maximum RETURNCODE.
                          1 person found this helpful
                          • 10. Re: Minimise the SQL code
                            ascheffer
                            select nullif( count(*), 0 )
                                 , case when count(*) = 1 then nvl( iv_issuecode, iv_indentcode ) end
                            into v_count, v_returncode
                            from stocktransaction 
                            where transactioncode = 4
                            and (  issuecode = iv_issuecode
                                or ( iv_issuecode is null
                                   and indentcode  = iv_indentcode 
                                   )
                                );