9 Replies Latest reply on Sep 16, 2014 1:34 PM by brunovroman

    'IN' 'Not IN' Issues in PLSQL

    kalivijay

      In our Package,  'IN' 'NOT IN' Syntax Not Working Properly .

       

      Below is the code for your reference

       

      if (v_val not in (v_val_code)) then

         v_chk:=123;

      else

         v_chk:=456;

      end if;

       

       

      Here ,

      v_val = ABCD

      v_val_code = ABCD,EDFR,ABCD,TGHY

       

      But the value of v_chk is 123 .

      If i put in instead of not in then v_chk value is 456.

       

      Please check this and let me know if any issues.

       

      Thanks

        • 1. Re: 'IN' 'Not IN' Issues in PLSQL
          BalajiSr

          In the above mentioned code, v_val_code has comma separated values and it's not being splitted before compared with v_val. That is the problem.

           

          I tried the following and it works fine.

           

          DECLARE

            v_val      VARCHAR2(100) := 'ABCD';

            v_val_code VARCHAR2(100) := 'ABCD,EDFR,ABCD,TGHY';

            v_chk      NUMBER;

          BEGIN

            BEGIN

              SELECT 123

              INTO v_chk

              FROM dual

              WHERE v_val NOT IN

                    (SELECT col1

                     FROM (SELECT regexp_substr(v_val_code, '[0-9]+|[a-z]+|[A-Z]+', 1, lvl) col1

                           FROM (SELECT v_val_code, LEVEL lvl

                                 FROM dual

                                 CONNECT BY LEVEL <= length(v_val_code) - length(REPLACE(v_val_code, ',')) + 1)) tab);

            EXCEPTION

              WHEN no_data_found THEN

                v_chk := 456;

            END;

          END;

           

          You may find a better answer if you search in the web.

           

          Thanks.

          • 2. Re: 'IN' 'Not IN' Issues in PLSQL
            Jagadekara

            v_val_code = ABCD,EDFR,ABCD,TGHY

            If v_val_code value is in same row that's why it is like that.

             

            If v_val_code

            -------------------

            ABCD

            EDFR

            ABCD

            TGHY

             

            like this then it will work.

            • 3. Re: 'IN' 'Not IN' Issues in PLSQL
              Most Wanted!!!!

              Maybe,

               

              DECLARE
                 v_val      VARCHAR2 (10) := 'ABCD';
                 lv_n_val   NUMBER;
              BEGIN
                 SELECT     CASE
                               WHEN v_val =
                                      REGEXP_SUBSTR ('ABCD,EDFR,ABCD,TGHY',
                                                     '[^,]+',
                                                     LEVEL,
                                                     LEVEL
                                                    )
                                  THEN 123
                               ELSE 456
                            END
                       INTO lv_n_val
                       FROM DUAL
                 CONNECT BY LEVEL <=
                                 LENGTH (REGEXP_REPLACE ('ABCD,EDFR,ABCD,TGHY',
                                                         '[[:alpha:]]'
                                                        )
                                        )
                               + 1;

                 DBMS_OUTPUT.put_line(lv_n_val);
              END;

               

              Regards,

              Friend

              • 4. Re: 'IN' 'Not IN' Issues in PLSQL
                kalivijay

                Hi ,

                 

                Thanks for your reply.

                 

                I got the below error when i run the query .

                 

                ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5

                 

                Thanks

                • 5. Re: 'IN' 'Not IN' Issues in PLSQL
                  2747544

                  DECLARE

                      ln_input        VARCHAR2(4000):= 'ABCD';

                      ln_input_split    VARCHAR2(4000)    :=    'ABCD,EFGH,ABC,BCDF';

                  BEGIN

                      FOR i IN(SELECT regexp_substr (ln_input_split, '[^,]+', 1, rownum) AS Inp_Split

                          FROM dual CONNECT BY level <= LENGTH (regexp_replace (ln_input_split, '[^,]+')) + 1)

                      LOOP

                     

                          IF(ln_input = i.Inp_Split)

                          THEN

                              DBMS_OUTPUT.put_line(i.Inp_Split||' -> 456 (IN)');

                          ELSE

                              DBMS_OUTPUT.put_line(i.Inp_Split||' --> 123 (NOT IN)');

                          END IF;

                   

                     

                      END LOOP;

                  END;

                  • 6. Re: 'IN' 'Not IN' Issues in PLSQL
                    2747544

                    WITH temp AS

                    (

                    SELECT regexp_substr (    'ABCD,EFGH,ABCD,BCDF', '[^,]+', 1, rownum) AS Inp_Split

                            FROM dual CONNECT BY level <= LENGTH (regexp_replace (    'ABCD,EFGH,ABC,BCDF', '[^,]+')) + 1

                    )

                    SELECT CASE WHEN 'ABCD' NOT IN (SELECT inp_split FROM temp) THEN 123   ELSE 456 END case_op FROM dual;

                    • 7. Re: 'IN' 'Not IN' Issues in PLSQL
                      ascheffer
                      declare
                        type tp_array is table of varchar2(100);
                        v_val_code tp_array;
                        v_val varchar2(100);
                        v_chk number;
                      begin
                        v_val := 'ABCD';
                        v_val_code := tp_array( 'ABCD', 'EDFR', 'ABCD', 'TGHY' );
                        if v_val member of v_val_code
                        then
                          v_chk := 123;
                        else
                          v_chk := 456;
                        end if;
                        dbms_output.put_line( v_chk );
                      end;
                      
                      • 8. Re: Re: 'IN' 'Not IN' Issues in PLSQL
                        ascheffer

                        Or

                         

                        declare
                          v_val_code varchar2(100);
                          v_val varchar2(100);
                          v_chk number;
                        begin
                          v_val := 'ABCD';
                          v_val_code := 'ABCD,EDFR,ABCD,TGHY';
                          if instr( ',' || v_val_code || ',', ',' || v_val || ',' ) > 0
                          then
                            v_chk := 123;
                          else
                            v_chk := 456;
                          end if;
                          dbms_output.put_line( v_chk );
                        end;
                        
                        • 9. Re: Re: 'IN' 'Not IN' Issues in PLSQL
                          brunovroman

                          Hello,

                           

                          you might do something like:

                           

                          SET SERVEROUTPUT ON

                           

                          DECLARE

                            v_val      VARCHAR2(10) := 'ABCD';

                            v_val_code VARCHAR2(20) := 'ABCD,EDFR,ABCD,TGHY';

                            v_chk      SIMPLE_INTEGER := 0;

                          BEGIN

                            v_chk := CASE WHEN ',' || v_val_code || ',' LIKE '%,' || v_val || ',%'

                                          THEN 456

                                          ELSE 123

                                    END;

                            DBMS_OUTPUT.PUT_LINE( TO_CHAR( v_chk ) );

                          END;

                          /

                          456

                          PL/SQL procedure successfully completed.

                           

                          Best regards,

                           

                          Bruno Vroman.