jeudi 25 juin 2020

How to compare two varibales in IF statement?

I am trying to execute the below query.

I am trying to do this comparison using IF statement. I am expecting my result to like the values which are not in v_anwen_id should be selected from v_anwen2_id. so when v_anwen2_id is not equal to v_anwen_id then to execute the insert statement. But i am not getting the " v_anwen2_id is not equal to v_anwen_id " value. ( i wanted it to be like when a is not equal to b then execute insert statement. but still it is inserting the values which are in b as well). I am not sure if it is the problem with my if statement or with the cursor.

 IF v_anwen2_id <> v_anwen_id

Below is my query.

SET serveroutput ON; 
DECLARE 
    v_client_id  NUMBER(38);
    v_roles_id      NUMBER(38);
    v_old_roles     NUMBER(38);
    v_rights_id     NUMBER(38);
    v_user1_id       NUMBER(38);
    v_anwen_id       NUMBER(38);
    v_anwen2_id   NUMBER(38);
    v_client1_id    NUMBER(38);
    v_abtei     NUMBER(38);
    

CURSOR c_abtei (var01 NUMBER) IS
     SELECT abteil,client_id FROM ty_join_anwen_abteil WHERE anwen = var01;
     
CURSOR c_anwen (var02 NUMBER) IS
     SELECT anwen FROM ty_join_anwen_abteil WHERE abteil = var02;
CURSOR c_roles (var03 NUMBER) IS
     SELECT roles FROM ty_join_rolesn_righte WHERE right = var03; 
     
CURSOR c_users (var04 NUMBER) IS
     SELECT anwen FROM ty_join_anwen_rolesn WHERE roles = var04;  
     
BEGIN 



    
 --  SELECT id INTO v_roles_id FROM ty_sd_roles WHERE description ='cod_Test';
    SELECT id INTO v_rights_id FROM ty_sd_berightigung WHERE description ='failure_note';
      
  --  INSERT INTO ty_join_rolesn_righte (role,right) 
--    VALUES  (v_roles_id,v_rights_id);
    
    SELECT ID INTO v_user1_id FROM ty_sd_person WHERE email = 'jens@gmail.com';         
    OPEN c_abteil(v_user1_id); 
      LOOP 
        FETCH c_abteil INTO v_abteil,v_client1_id;
        EXIT WHEN c_abteil%NOTFOUND;
        
          OPEN c_anwen(v_abteil); 
             LOOP 
                FETCH c_anwen INTO v_anwen_id; 
                EXIT WHEN c_anwen%NOTFOUND;
      --  INSERT INTO ty_join_anwen_rolesn (anwen,roles,client_id) VALUES (v_anwen_id,v_roles_id,v_client1_id);
     END LOOP; 
          CLOSE c_anwen;
      END LOOP; 
    CLOSE c_abteil;
    OPEN c_roles(v_rights_id);
      LOOP 
        FETCH c_roles INTO v_old_roles;
        EXIT WHEN c_roles%NOTFOUND;
        
          OPEN c_users(v_old_roles); 
             LOOP 
                FETCH c_users INTO v_anwen2_id; 
                EXIT WHEN c_users%NOTFOUND;
                IF v_anwen2_id <> v_anwen_id
                THEN
                dbms_output.put_line(v_anwen2_id);
             --   INSERT INTO ty_join_anwen_rolesn (anwen,roles,client_id) VALUES (v_anwen2_id,v_roles_id,v_client1_id);
                END IF;
             --   delete from ty_join_roles_right where roles = v_old_roles;
             END LOOP; 
          CLOSE c_users;
      END LOOP; 
    CLOSE c_roles;
            

END; 
/

Aucun commentaire:

Enregistrer un commentaire