1 2 Previous Next 21 Replies Latest reply: Oct 3, 2009 11:39 AM by 724211 RSS

    CASE statement in PL/SQL

    724211
      Hi PL/SQL experts,

      I'm going a bit loopy here, so could someone please point out what I'm doing wrong with this case statement:

      Test procedure is:

      CREATE OR REPLACE procedure SCOTT.postcode_validate_2 (input_post_code VARCHAR2) as


      alphabet_string VARCHAR2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvxwyz';
      number_string VARCHAR2(10) := '012345789';
      last_part_postcode VARCHAR2(3) := NULL;

      BEGIN


      IF INSTR(input_post_code,' ') = 0
      THEN
      DBMS_OUTPUT.PUT_LINE('We need a space in the postcode please');
      GOTO exit;
      ELSIF LENGTH(SUBSTR(input_post_code,instr(input_post_code,' ')+1)) > 3
      THEN
      DBMS_OUTPUT.PUT_LINE('Last part of postcode can only be 3 characters');
      GOTO exit;
      ELSE

      last_part_postcode := SUBSTR(input_post_code,instr(input_post_code,' ')+1);
      END IF;


      CASE input_post_code

      WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 2
      AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
      AND instr(number_string,substr(input_post_code,2,1)) != 0)
      THEN
      DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format A9')
      -- ELSE
      -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format A9');
      -- GOTO exit;
      -- END IF;

      WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 3
      AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
      AND instr(alphabet_string,substr(input_post_code,2,1)) != 0
      AND instr(number_string,substr(input_post_code,3,1)) != 0)
      THEN
      DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format AA9')
      -- ELSE
      -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format AA9');
      -- GOTO exit;
      -- END IF;

      WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 3
      AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
      AND instr(number_string,substr(input_post_code,2,1)) != 0
      AND instr(number_string,substr(input_post_code,3,1)) != 0)
      THEN
      DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format A99')
      -- ELSE
      -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format A99');
      -- GOTO exit;
      -- END IF;

      WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 3
      AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
      AND instr(number_string,substr(input_post_code,2,1)) != 0
      AND instr(alphabet_string,substr(input_post_code,3,1)) != 0)
      THEN
      DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format A9A')
      -- ELSE
      -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format A9A');
      -- GOTO exit;
      -- END IF;

      WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 4
      AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
      AND instr(alphabet_string,substr(input_post_code,2,1)) != 0
      AND instr(number_string,substr(input_post_code,3,1)) != 0
      AND instr(number_string,substr(input_post_code,4,1)) != 0)
      THEN
      DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format AA99')
      -- ELSE
      -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format AA99');
      -- GOTO exit;
      -- END IF;

      WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 4
      AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
      AND instr(alphabet_string,substr(input_post_code,2,1)) != 0
      AND instr(number_string,substr(input_post_code,3,1)) != 0
      AND instr(alphabet_string,substr(input_post_code,4,1)) != 0)
      THEN
      DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format AA9A')
      -- ELSE
      -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format AA9A');
      -- GOTO exit;
      -- END IF;
      END;


      -- Check last part of format, should be AA9
      IF (instr(number_string,substr(input_post_code,1,1)) != 0
      AND instr(alphabet_string,substr(input_post_code,2,1)) != 0
      AND instr(alphabet_string,substr(input_post_code,3,1)) != 0)
      THEN
      DBMS_OUTPUT.PUT_LINE('End part of the postcode is in the correct format, 9AA');
      ELSE
      DBMS_OUTPUT.PUT_LINE('End part of the postcode is in the wrong format!');
      END IF;

      <<exit>>
      DBMS_OUTPUT.PUT_LINE('Please try again');
      END;
      /

      However, I'm getting the following error:

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      37/6 PLS-00103: Encountered the symbol "WHEN" when expecting one of
      the following:
      := . ( % ;

      On a second note, can I not have the ELSE structure embedded within the case (currently commented out)?

      Thanks very much in advance.

      Dev
        • 1. Re: CASE statement in PL/SQL
          12826
          You don't have a ; after (every) DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format A9')

          And no, you cannot have the ELSE where you currently have it commented out
          • 2. Re: CASE statement in PL/SQL
            dmcghan
            Dev,

            Also, you're setting this up as a simple CASE and then using the searched CASE syntax. You'll need to pick one. A quick Google search on "oracle case" will show you some examples.

            Regards,
            Dan

            http://danielmcghan.us
            http://www.skillbuilders.com

            You can reward this reply by marking it as either Helpful or Correct ;-)
            • 3. Re: CASE statement in PL/SQL
              724771
              Fixed code:
              CREATE OR REPLACE procedure postcode_validate_2 (input_post_code VARCHAR2) as
              
              alphabet_string VARCHAR2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvxwyz';
              number_string VARCHAR2(10) := '012345789';
              last_part_postcode VARCHAR2(3) := NULL;
              
              BEGIN
              
              IF INSTR(input_post_code,' ') = 0
              THEN
              DBMS_OUTPUT.PUT_LINE('We need a space in the postcode please');
              GOTO exit;
              ELSIF LENGTH(SUBSTR(input_post_code,instr(input_post_code,' ')+1)) > 3
              THEN
              DBMS_OUTPUT.PUT_LINE('Last part of postcode can only be 3 characters');
              GOTO exit;
              ELSE
              
              last_part_postcode := SUBSTR(input_post_code,instr(input_post_code,' ')+1);
              END IF;
              
              
              CASE 
              WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 2
              AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
              AND instr(number_string,substr(input_post_code,2,1)) != 0)
              THEN
              DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format A9');
              -- ELSE
              -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format A9');
              -- GOTO exit;
              -- END IF;
              
              WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 3
              AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
              AND instr(alphabet_string,substr(input_post_code,2,1)) != 0
              AND instr(number_string,substr(input_post_code,3,1)) != 0)
              THEN
              DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format AA9');
              -- ELSE
              -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format AA9');
              -- GOTO exit;
              -- END IF;
              
              WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 3
              AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
              AND instr(number_string,substr(input_post_code,2,1)) != 0
              AND instr(number_string,substr(input_post_code,3,1)) != 0)
              THEN
              DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format A99');
              -- ELSE
              -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format A99');
              -- GOTO exit;
              -- END IF;
              
              WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 3
              AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
              AND instr(number_string,substr(input_post_code,2,1)) != 0
              AND instr(alphabet_string,substr(input_post_code,3,1)) != 0)
              THEN
              DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format A9A');
              -- ELSE
              -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format A9A');
              -- GOTO exit;
              -- END IF;
              
              WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 4
              AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
              AND instr(alphabet_string,substr(input_post_code,2,1)) != 0
              AND instr(number_string,substr(input_post_code,3,1)) != 0
              AND instr(number_string,substr(input_post_code,4,1)) != 0)
              THEN
              DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format AA99');
              -- ELSE
              -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format AA99');
              -- GOTO exit;
              -- END IF;
              
              WHEN (length(substr(input_post_code,1,instr(input_post_code,' ')-1)) = 4
              AND instr(alphabet_string,substr(input_post_code,1,1)) != 0
              AND instr(alphabet_string,substr(input_post_code,2,1)) != 0
              AND instr(number_string,substr(input_post_code,3,1)) != 0
              AND instr(alphabet_string,substr(input_post_code,4,1)) != 0)
              THEN
              DBMS_OUTPUT.PUT_LINE('We have a valid postcode in the format AA9A');
              -- ELSE
              -- DBMS_OUTPUT.PUT_LINE('Sorry but that is an incorrect postcode! Format AA9A');
              -- GOTO exit;
              -- END IF;
              END CASE;
              
              -- Check last part of format, should be AA9
              IF (instr(number_string,substr(input_post_code,1,1)) != 0
              AND instr(alphabet_string,substr(input_post_code,2,1)) != 0
              AND instr(alphabet_string,substr(input_post_code,3,1)) != 0)
              THEN
              DBMS_OUTPUT.PUT_LINE('End part of the postcode is in the correct format, 9AA');
              ELSE
              DBMS_OUTPUT.PUT_LINE('End part of the postcode is in the wrong format!');
              END IF;
              
              <<exit>>
              DBMS_OUTPUT.PUT_LINE('Please try again');
              END;
              /
              As VG2 pointed out you were missing some semicolons.

              Also, CASE input_post_code was replaced with just CASE since you are doing a searched case.

              Also, END at the end of the case statement was replaced with END CASE.
              • 4. Re: CASE statement in PL/SQL
                BluShadow
                Dan McGhan wrote:
                You can reward this reply by marking it as either Helpful or Correct ;-)
                He could, but I know I'd never award point to anyone who asks for them.
                • 5. Re: CASE statement in PL/SQL
                  724211
                  Hi,

                  Correct me if I'm wrong, but from what I understand, you don't need semi-colons within the case statment. If you add these, then you get a different error:

                  LINE/COL ERROR
                  -------- -----------------------------------------------------------------
                  75/16 PLS-00103: Encountered the symbol ";" when expecting one of the
                  following:
                  begin case declare exit for goto if loop mod null pragma
                  raise return select update while with <an identifier>
                  <a double-quoted delimited-identifier> <a bind variable> <<
                  close current delete fetch lock insert open rollback
                  savepoint set sql execute commit forall merge pipe
                  The symbol "exit" was substituted for ";" to continue.
                  • 6. Re: CASE statement in PL/SQL
                    724771
                    You do need the semicolons, and by adding them you have corrected the first error and exposed another. See the full list of fixes in my previous post.
                    • 7. Re: CASE statement in PL/SQL
                      12826
                      The initial error was only one problem with your procedure. The other has to do with how you are using the CASE statement.
                      The code seems a bit long for what you are trying to do. Can you outline what your requirements are?
                      • 8. Re: CASE statement in PL/SQL
                        dmcghan
                        Hello BluShadow,

                        That's actually an automated signature for the Oracle forums. It's really just a friendly reminder for those that may forget or don't know any better.

                        Regards,
                        Dan
                        • 9. Re: CASE statement in PL/SQL
                          BluShadow
                          Dan McGhan wrote:
                          Hello BluShadow,

                          That's actually an automated signature for the Oracle forums. It's really just a friendly reminder for those that may forget or don't know any better.

                          Regards,
                          Dan
                          Unfortunately it looks like begging. ?:|

                          The fact is that if people are mindful of awarding points then they will, if they're not, they won't.

                          If your post offers a good solution and they're mindful to offer points you'll get them.

                          The problem with "reminders" like that is that it entices newbies to start asking for points for their answers, even when they've posted some one line piece of rubbish that doesn't work, and unfortunately, the OP's often don't know better and think they have to mark the answer as correct even when it's not and by then, it's too late to reverse.

                          Most of the people on here with lots of points have never asked for them, they just answer the posts and get points when they do and don't when they don't. (to be honest, most of us don't care).

                          ;)
                          • 10. Re: CASE statement in PL/SQL
                            dmcghan
                            BluShadow,

                            What is your name? I feel silly calling you "BluShadow" ;)

                            I suppose I added that to the signature when others were doing it, but when I think about it, I agree with you. I've removed that line from my signature.

                            Regards,
                            Dan

                            http://danielmcghan.us
                            http://www.skillbuilders.com
                            • 11. Re: CASE statement in PL/SQL
                              BluShadow
                              If you're trying to check the validity of UK postcodes it's easier to use regular expressions e.g.
                              ,case when regexp_like(outcode, '^([A-PR-UWYZ]([0-9]([0-9]|[A-HJKS-UW])?|[A-HK-Y][0-9]([0-9]|[ABEHMNPRVWXY])?)|GIR)$')
                                    then 'valid'
                                    else 'invalid'
                               end as outcode_valid
                              ,case when regexp_like(incode, '^[0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]$')
                                    then 'valid'
                                    else 'invalid'
                               end as incode_valid
                              This example takes the seperate outcode (left side of the postcode) and incode (right side of the postcode) and validates each part

                              It can be adapted to check a single string full postcode if required.
                              • 12. Re: CASE statement in PL/SQL
                                BluShadow
                                Dan McGhan wrote:
                                BluShadow,

                                What is your name? I feel silly calling you "BluShadow" ;)
                                You can call me Blu like most do on here. ;)
                                I suppose I added that to the signature when others were doing it, but when I think about it, I agree with you. I've removed that line from my signature.

                                Regards,
                                Dan
                                Wisdom prevails. ;)
                                • 13. Re: CASE statement in PL/SQL
                                  Hoek
                                  Well, you can always call him 'Blu', ofcourse, if that makes you feel less silly ;)
                                  Glad you got his point and agreed on it, btw.

                                  edit, totally offtopic

                                  Blu always reminds me of The Stig from a 'certain car program'.
                                  "Some say he never sleeps without a processor fan next to his bed, others say he can type 1024 characters a second while singing 'I will survive', all we know he's called: BluShadow"

                                  Edited by: hoek on Oct 2, 2009 4:07 PM

                                  Edited by: hoek on Oct 2, 2009 4:15 PM forgot to type 'without'
                                  • 14. Re: CASE statement in PL/SQL
                                    BluShadow
                                    hoek wrote:
                                    Blu always reminds me of The Stig from a 'certain car program'.
                                    "Some say he never sleeps a processor fan next to his bed, others say he can type 1024 characters a second while singing 'I will survive', all we know he's called: BluShadow"
                                    LOL! :D

                                    I think that's just you saying that. ;)
                                    1 2 Previous Next