5 Replies Latest reply: Oct 19, 2013 5:47 AM by Andreas Weiden RSS

    Subquery not allowed in this context

    LuKKa

      Dear All

      I am using Forms 10G.

       

      I am trying to write a simple query in When_validate_item:-

       

       

      if :block1.item1 = '1'  and :block1..item2  not in (Select col1 from table1 )
      then
        Message  ('ERROR');
        raise form_trigger_failure;
      end if;
      
      

       

      Here I am getting the below error :-

       

      Error 405 - Subquery not allowed in this context

       

      If I remove the inner select statement (Select col1 from table1) and if I am giving hardcoded value then it is worlking fine .

      What could be the problem ?

        • 1. Re: Subquery not allowed in this context
          Andreas Weiden

          You cannot mix up SQL and PL/SQL this way. You have to separately execute the SQL into a variable and then check the result.

          • 2. Re: Subquery not allowed in this context
            LuKKa

            Hi,

            Can you please give an example ?

            I have tried many way but did not get the correct code .

            Because that select statement is returns many rows , so not able to handle .

            • 3. Re: Subquery not allowed in this context
              Andreas Weiden

              Something like

               

              if :block1.item1 = '1'  then
                declare
                  nDummy NUMBER;
                begin
                  Select 1
                    into nDummy
              
                    from table1
                   where col1=:block1..item2;  
                exception
                  when no_data_found then
                   Message  ('ERROR');  
                  raise form_trigger_failure; 
                end;
              end if; 
              

               

              • 4. Re: Subquery not allowed in this context
                LuKKa

                I canot use this because I want to pass multiple value in where condition .

                Ok let me explain you this way :-

                 

                The below query is working fine :-

                if :block1.item1 = '1'  and :block1..item2  not in  ('A','B','C','D')  
                then  
                  Message  ('ERROR');  
                  raise form_trigger_failure;  
                end if; 
                

                 


                But if I wan to write the same in select statement then it is not working .please see the NOT IN part in the in the first line .


                if :block1.item1 = '1'  and :block1..item2  not in (Select col1 from table1 )   
                then  
                  Message  ('ERROR');  
                  raise form_trigger_failure;  
                end if; 
                


                So this select how can I write , because it is returning multiple records and I cant write select in IF statement .

                • 5. Re: Subquery not allowed in this context
                  Andreas Weiden

                  I canot use this because I want to pass multiple value in where condition

                   

                  No, you want to find out if your block1.item2 is one of the values from your select. That's exactly what my select does. It checks if its finds a record matching your value, and if it does not, it goes to the error-part (the exception-handler)