0

My loop is given me twice the results. As I only want it one time. What should I do?

 for i in 1..length(newStudentNumber) loop
    character := SUBSTR(newStudentNumber, i, 1);
    newStudentNumber := newStudentNumber || case character
     when 'A' then '16'
     when 'B' then '17'
     when 'C' then '18'
     when 'D' then '19'
     when 'E' then '20'
     when 'F' then '21'
     when 'G' then '22'
     when 'H' then '23'
     when 'I' then '24'
     when 'J' then '25'
     when 'K' then '26'
     when 'L' then '27'
     when 'M' then '28'
     when 'N' then '29'
     when 'O' then '30'
     when 'P' then '31'
     when 'Q' then '32'
     when 'R' then '33'
     when 'S' then '34'
     when 'T' then '35'
     when 'U' then '36'
     when 'V' then '37'
     when 'W' then '38'
     when 'X' then '39'
     when 'Y' then '40'
     when 'Z' then '41'
     else character
    end;
  end loop;
  dbms_output.put_line(newStudentNumber);

I hope I am not too vague with this question

Thanks in advance

Maxime de Lange
  • 287
  • 2
  • 7
  • 21
  • You have nine calls to dbms_output in the function and one in the anonymous block. You have ten values displayed. What is the problem? – Alex Poole May 16 '15 at 18:44
  • The results I get are like this: TUENL1234567893536202927123456789 while I would like that only: 3536202927123456789. Because TUENL1234567893 is a repeat from another part (I think or I suggest). I only want the awnser one time. Or do I something funny that TUENL1234567893536202927123456789 is one result? I hope you know what I am trying to say. cheers – Maxime de Lange May 16 '15 at 18:54
  • I don't want to create spam. I filtered some outputs and results. My problem lies in the loop. somehow when that executes I get double results (same result behind the other one) – Maxime de Lange May 16 '15 at 19:01

1 Answers1

1

Inside your loop you are appending the possibly-converted character to the same string variable you are looping over, newStudentNumber. If you don't want both the old and new value then you need a second variable which you populate in the loop.

  -- new variable, which has to be declared
  convertedStudentNumber := null;
  for i in 1..length(newStudentNumber) loop
    character := SUBSTR(newStudentNumber, i, 1);
    convertedStudentNumber := convertedStudentNumber || case character
    ...
    else character
    end;
  end loop;
  dbms_output.put_line(convertedStudentNumber);

Now the two variables have different values, one starting TUE..., the other starting 353620....

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Ye perfect mate. When I see it. It is pretty obvious. But thanks for the help, tough! I mark your awnser as the right given one – Maxime de Lange May 16 '15 at 19:17
  • Got one question left Alex. Is it possible for me to use this loop to generate letters out of numbers. So basicaly reversing this loop. And if I want to do that, do I need to adjust my loop? Cheers – Maxime de Lange May 16 '15 at 21:35
  • @MaximedeLange - not sure what you mean. You want to convert letters to numbers, and numbers to letters? You can do that in the case, sure, you just need to define the translation - 1->A, or whatever. – Alex Poole May 16 '15 at 21:38
  • I need to create an ISIN with a checksum. So I need to convert all to numbers like the loop above and after a bunch of other thing I need to convert the same numbers again in to letters again. I tried the above loop for my issue, but it does not work. Strange, tough. You'd say it would be exactly the same (but then change "when '16' then 'A' ")? sorry for my bad english – Maxime de Lange May 16 '15 at 22:11
  • once again. not trying to create spam ^^. I got it worked. spent too many hours yesterday. I could not even think anymore. but it's fixed now – Maxime de Lange May 17 '15 at 09:47