PL/SQL Help
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: PL/SQL Help

  1. #1
    Join Date
    Jan 2002
    Posts
    111

    PL/SQL Help

    Hi All


    I am trying to pull all the odd and even numbers from right to Left.

    The Number is 00 1 0072053 60 3716796.

    Below is the logic that i am trying to achieve.


    1.From the right to left, assign the odd/even position to each digit. Start with odd position.
    2.Sum all digits in odd position and multiply the result by 3.
    3.Sum all digits in even position.
    4.Get the total for the two steps above and divide the result by 10.
    5.The check digit is the number which adds the remainder to 10.


    can anyone pleasehelp me to achieve this. I need to Deliver this.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I might be getting too old for this... couldn't put together what I understood is the requirement and the described logic.

    Would you mind in showing what the result is supposed to look like?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2002
    Posts
    111
    00 1 0072053 60 3716796

    The result of the above number should be single digit number which i am printing.

    For example 5

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    wow... still can't follow but, if the problem involves searching a string and doing math with the pieces you may want to research/use functions below

    substr() ... will allow you to pick a piece of the string.
    lenght() ... will tell you the total lenght of the string.
    to_number() ... will convert a char into a number
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    I'm guessing that this is what you want. Although I got a check sum of 7.
    I deserve at least half of the credit for helping you with your homework!

    PAVB, I don't think that you are too old. Since I work at a university
    I can take classes. So I end up in computer classes with people half
    my age. I would say that they are too young.

    Code:
    SYS@devdb AS SYSDBA> DECLARE
      2  	v_string  VARCHAR2(2000) :=  REPLACE('001 0072053 60 3716796', ' ', '');
      3  	i	  PLS_INTEGER;
      4  	digitSum  PLS_INTEGER := 0;
      5  BEGIN
      6  	i := 1;
      7  	WHILE i < length(v_string)
      8  	LOOP
      9  	   digitSum := digitSum + substr(v_string, i, 1);
     10  	   i := i + 2;
     11  	END LOOP;
     12  
     13  	digitSum := digitSum * 3;
     14  
     15  	i := 0;
     16  	WHILE i < length(v_string)
     17  	LOOP
     18  	   digitSum := digitSum + substr(v_string, i, 1);
     19  	   i := i + 2;
     20  	END LOOP;
     21  
     22  	DBMS_OUTPUT.PUT_LINE('The check digit is: ' || MOD(digitSum, 10));
     23  END;
     24  /
    The check digit is: 7
    
    PL/SQL procedure successfully completed.
    this space intentionally left blank

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    So it was a check-digit calculation! now I can see it LOL

    Man, that was one of the best inventions of the 70's, second only to punching an "*" on column 80 -to signal some exception LMAO
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Quote Originally Posted by PAVB View Post
    So it was a check-digit calculation! now I can see it LOL

    Man, that was one of the best inventions of the 70's, second only to punching an "*" on column 80 -to signal some exception LMAO
    For my money adding a divide by zero fault checking would have been the best invention of the 70's. When I was in high school we had a mini computer with teletype terminals. A disgruntled student would occasionally initiate a divide by zero error, in order to crash the computer, then leave. The people who maintained the computer would spend the next hour trying to recover everything. I suppose it was funny the first few times he did it. Fortunately I was doing all my work on the Atari 800 with a basic cartridge and a cassette tape drive. Not that I am old or anything...
    this space intentionally left blank

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gandolf989 View Post
    Not that I am old or anything...
    I know, I know... other people are too young; that's the problem
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Apr 2006
    Posts
    377
    An option using SQL could be:

    Code:
    SQL> with t as (
      2       select replace('00 1 0072053 60 3716796',' ') digits from dual)
      3    select mod((odd+even),10) check_digit
      4    from (select
      5       sum(regexp_substr(digits, '.', 1, level*2-1))*3 odd,
      6       sum(regexp_substr(digits, '.', 1, level*2)) even
      7       from t
      8       connect by level <= (length(replace(digits, ' '))+1)/2);
    
    CHECK_DIGIT
    -----------
              5
    Last edited by ebrian; 06-27-2009 at 04:06 PM.

  10. #10
    Join Date
    Jan 2002
    Posts
    111
    EBRAIN,

    Can ou please help me in putting the SQL into PL/SQL...It will be a great help

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width