This discussion is archived
10 Replies Latest reply: Mar 28, 2013 5:04 AM by ascheffer RSS

Minimise the SQL code

982895 Newbie
Currently Being Moderated
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. Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.
  • 3. Re: Minimise the SQL code
    Marwim Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
          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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    Karthick_Arp Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Karthick, Can You please explain me the code which u gave.
  • 9. Re: Minimise the SQL code
    Karthick_Arp Guru
    Currently Being Moderated
    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.
  • 10. Re: Minimise the SQL code
    ascheffer Expert
    Currently Being Moderated
    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 
           )
        );

Legend

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