13 Replies Latest reply: Nov 19, 2013 10:26 PM by NuhaArif RSS

    default where clause

    NuhaArif

      i am passing dynamic where clause to a detail block from the main control block by pressing a button, the problem is different types of criterias are not comming together, like i may choose any value of machine or if its null it should bring all the machines combined with status which can be either opened,closed,inspected and i added them as elements of list and if the selected item has status with value All  it shoud bring all the status for all the machines.but the problem is its not satisfying the criteria properly and when i choose all nothing is displayed in the detail block.

       

      {code}

       

      declare
      cnt number;
      var varchar2(32000);
      VAAL VARCHAR2(32500);
      begin
      cnt := Get_List_Element_Count('IP_REP_INFO.T_LIST_IN');
      if cnt >= 1
      then
      var := null;
      for i in 1..cnt loop
        var := var||','||''''||Get_List_Element_Value('IP_REP_INFO.T_LIST_IN',i)||'''';

      end loop;
      Set_Block_Property('OV_JOB_MAINT', DEFAULT_WHERE,
          'WHERE (1=1 AND JOB_MACH IS NULL) OR (1=0 OR JOB_MACH in ('||substr(var, 2)||'))  AND (1 = 1 and :ORDER_STATUS IN ('||'''ALL'''||') AND
         STATUS IN (''OPENED'',''CLOSED'',''INSPECTION'') ) or ( 1=0 OR STATUS = :ORDER_STATUS)');
      GO_BLOCK('OV_JOB_MAINT');
      EXECUTE_QUERY;
      GO_ITEM('JOB_DT');

       

      end;

       

      {\code}

       


        • 1. Re: default where clause
          Priyasagi

          Hi NuhaArif,

           

          What is :order_status and var?

          If :order_status and var are the field of your current control_block then make it as global or create parameter list for sending these variables to another block.

           

          Global Variable in Oracle Forms 6i

          • 2. Re: default where clause
            NuhaArif

            thanks priyasagi , but still i have to use it in where clause ,the problem is how can i pass both the conditions.Can you please suggest me with an example.

            • 3. Re: default where clause
              Priyasagi

              Hi NuhaArif,

               

              :global.var:=null;

              :global.ord_stat:=:order_status;

               

              for i in 1..cnt loop

              :global.var := :global.var||','||''''||Get_List_Element_Value('IP_REP_INFO.T_LIST_IN',i)||'''';

              end loop;

               

              Set_Block_Property('OV_JOB_MAINT', DEFAULT_WHERE,

              '(1=1 AND JOB_MACH IS NULL) OR (1=0 OR JOB_MACH in ('||substr(:global.var, 2)||'))  AND (1 = 1 and :global.ord_stat IN ('||'''ALL'''||') AND

              STATUS IN (''OPENED'',''CLOSED'',''INSPECTION'') ) or ( 1=0 OR STATUS = :global.ord_stat)');

              GO_BLOCK('OV_JOB_MAINT');

              EXECUTE_QUERY;

              GO_ITEM('JOB_DT');

               

              Please do the above corrections and let me know the result.

              • 4. Re: default where clause
                NuhaArif

                i declared the two global variables as suggested by you in when new form instance.

                 

                {code}

                :global.var:=null;

                :global.ord_stat:=:order_status;

                then on the button i wrote the code as given by you, but the problem is first condition is not getting satisfiled.the :global.ord is getting the value , suppose if i want all the open orders for a particular machine , then the filter is not working.the code written on when-button-pressed is as below.

                 

                declare
                cnt number;
                var varchar2(32000);
                VAAL VARCHAR2(32500);

                 

                begin
                  :global.ord_stat:=:order_status;
                cnt := Get_List_Element_Count('IP_REP_INFO.T_LIST_IN');
                if cnt >= 1
                then
                var := null;
                for i in 1..cnt loop
                -- var := var||','||''''||Get_List_Element_Value('IP_REP_INFO.T_LIST_IN',i)||'''';
                :global.var := :global.var||','||''''||Get_List_Element_Value('IP_REP_INFO.T_LIST_IN',i)||'''';

                end loop;

                Set_Block_Property('OV_JOB_MAINT', DEFAULT_WHERE,

                '(1=1 AND JOB_MACH IS NULL) OR (1=0 OR JOB_MACH in ('||substr(:global.var, 2)||'))  AND (1 = 1 and :global.ord_stat IN ('||'''ALL'''||') AND

                STATUS IN (''OPENED'',''CLOSED'',''INSPECTION'') ) or ( 1=0 OR STATUS = :global.ord_stat)');

                    GO_BLOCK('OV_JOB_MAINT');

                EXECUTE_QUERY;

                GO_ITEM('JOB_DT');

                 

                end if;

                end;

                {/code}

                 

                its not satisfying both the conditions together, for example , if i pass value of order_status as OPENED and select the machine as 'PMCN001' , it will not work.I am wondering where the problem lies in where clause.

                • 5. Re: default where clause
                  Priyasagi

                  Hi Arif,

                   

                  What is meant by 1=1, 1=0? Why you unnecessarily checking two constant values?

                  First of all you check the output of the 'where clause' by sql.  If you are getting desired output at sql, then you try same in form.

                  Or give table structure and sample data that is helpful to us to give you proper suggestion.

                  • 6. Re: default where clause
                    CraigB

                    First, when setting the DEFAULT_WHERE property of a block, I like to assign the dynamic where clause to a variable so I can output it to the screen or look at the value through the Forms Debugger to ensure it is formatted correctly.

                     

                    Second, have you perfected your WHERE clause in SQLPlus or SQL Developer to ensure it is returning exaclty what you want?  To me, this is a critical first step that should be completed first before trying to create the dynamic where clause through code.

                     

                    Third, it is not necessary to include the 'WHERE' keyword in your code because Forms will automatically do this for you.  Will it work?  Yes, but your query ends up looking like this when Forms executes the query.

                     

                    SELECT {...list of your columns here...}
                    WHERE ( WHERE 'Your Dynamic Where Clause' )
                    
                    
                    
                    

                     

                    See my comments in your code sample...

                    
                    Set_Block_Property('OV_JOB_MAINT', DEFAULT_WHERE,
                        'WHERE (1=1 -- This is not needed, just evaluate JOB_MACH IS NULL
                         AND JOB_MACH IS NULL)
                          OR (1=0 -- I don't understand why you want a FALSE test here...
                              OR JOB_MACH in ('||substr(var, 2)||')
                                 -- Have you outputted the value of VAR to the screen to ensure
                                 -- it is formatted correctly?
                             )
                         AND (1 = 1 -- Again, this is not needed
                              and :ORDER_STATUS IN ('||'''ALL'''||')
                         AND STATUS IN (''OPENED'',''CLOSED'',''INSPECTION'') )
                          or ( 1=0 -- Again, why do you need a FALSE test here?
                               OR STATUS = :ORDER_STATUS)'
                         );
                    
                    
                    
                    
                     

                     

                    Regarding...

                    if the selected item has status with value All  it shoud bring all the status for all the machines.but the problem is its not satisfying the criteria properly and when i choose all nothing is displayed in the detail block.

                     

                    Do you have an Order Status with the value of "ALL" in your table?

                    My guess is that you don't because each record would have to record its actual status as well as the ALL status.  Based on this assumption, I recommend you alter your SQL so that it looks for the actual status as well as perform an IN comparison.

                     

                     

                      'AND status IN ('||DECODE(:ORDER_STATUS,'ALL','''OPENED'',''CLOSED'',''INSPECTION'' ',''''||:ORDER_STATUS)||''')'
                    
                    
                    
                    

                     

                    The addition of the DECODE enables you to query on all statuses when the :ORDER_STATUS = ALL or it returns the status listed in the :ORDER_STATUS field.

                     

                    Hope this helps.

                    Craig...

                    • 7. Re: default where clause
                      NuhaArif

                      thanks priyasagi and craigb ,for the advice i tested my queries in sql first using various conditions and then now i will test them on form by putting the values.

                      • 8. Re: default where clause
                        NuhaArif

                        thanks craigb,the general idea is user may input the parameter value of VAR , if he leaves it blank, then query should consider all the records so i put 1=1 and it should be :var instead as it will be inputted from the user.But my problem is i need to combine both or all of these possibilities together.

                         

                        {code}

                         

                        • Set_Block_Property('OV_JOB_MAINT', DEFAULT_WHERE,  
                        •     'WHERE (1=1      AND   VAR IS NULL)  
                        •       OR (OR JOB_MACH in ('||substr(var, 2)||')  
                        •                     )   -- The value of output is correct.
                        •      AND (1 = 1 -- this is needed because user may choose the order_status and if he chooses ALL which is not a table value as rightly pointed by you and i have modified it with your decode statement
                        •           and :ORDER_STATUS IN ('||'''ALL'''||')  
                        •      AND STATUS IN (''OPENED'',''CLOSED'',''INSPECTION'') )  
                        •       or ( 1=0 -- Again, why do you need a FALSE test here?  
                        •            OR STATUS = :ORDER_STATUS)'  
                        •      ); 

                         

                        {/code}

                        • 9. Re: default where clause
                          Marwim

                          Hello,

                           

                          CraigB suggested to put the where clause into a variable and to show it, e.g. as a message. This way you can see what your code did. Usually it is quite easy to find errors this way.

                           

                          On the other hand you can simplify your where clause when you create it dynamically into a variable.

                           

                          IF :Order_status IS NULL THEN

                            v_where := ...;

                          ELSE

                            v_where := ...;

                          END IF;

                           

                          Set_Block_Property('OV_JOB_MAINT', DEFAULT_WHERE, v_where);


                          Regards

                          Marcus

                          • 10. Re: default where clause
                            Priyasagi

                            Hi Arif,

                             

                            Try this

                             

                            Set_Block_Property('OV_JOB_MAINT', DEFAULT_WHERE,  
                                 'decode(nvl(:global.var,''X''),''X'',''X'',job_mach) in (decode(nvl(:global.var,''X''),''X'',''X'',substr(:global.var, 2)))
                                 and decode(:global.ord_stat,''ALL'',''ALL'',status) = decode(:global.ord_stat,''ALL'',''ALL'',:global.ord_stat)'
                            );

                            • 11. Re: default where clause
                              CraigB

                              ...the general idea is user may input the parameter value of VAR , if he leaves it blank, then query should consider all the records...

                              Since you are building your where clause, simply perform your conditional logic in your code and build the where clause accordingly.  For example:

                               

                               

                              DECLARE
                                   v_where   VARCHAR2(1000);
                              BEGIN
                                   IF ( :VAR IS NOT NULL ) THEN
                                        v_where := v_where || '"your column that matches VAR" = '''||:var||'''';
                                   END IF;
                              
                                   IF ( :ORDER_STATUS = 'ALL' ) THEN
                                        IF ( v_where IS NOT NULL ) THEN
                                             v_where := v_where || ' AND status IN (''OPENED'',''CLOSED'',''INSPECTION'') ';
                                        ELSE
                                             v_where := 'status IN (''OPENED'',''CLOSED'',''INSPECTION'') ';
                                        END IF;
                                   ELSE
                                        IF ( v_where IS NOT NULL ) THEN
                                             v_where := v_where || ' AND status = :ORDER_STATUS ';
                                        ELSE
                                             v_where :=  ' status = :ORDER_STATUS ';
                                        END IF;
                                    END IF;
                               
                                   -- Now you should have a valid Where Clause, Assign it to your
                                   -- BLOCK and execute the query.
                                   Set_Block_Property('OV_JOB_MAINT', DEFAULT_WHERE, v_where);
                                   GO_BLOCK('OV_JOB_MAINT');
                                   Execute_Query;
                              END;
                              
                              
                              
                              

                              There is no need to try and put everything into your WHERE clause since you are building it dynamically.  Just include those elements that you need based on the user input.

                               

                              Craig...

                              • 12. Re: default where clause
                                NuhaArif

                                thanks craigB, wonderful idea by this i can add more possible conditions as when required.I will check this code.

                                • 13. Re: default where clause
                                  NuhaArif

                                  Thanks priyasagi , i tried your code but its failing to bring the records.I will try to work on if - else condition otherwise and check.