13 Replies Latest reply: Apr 17, 2012 5:36 AM by 930890 RSS

    Current Query in Query mode

    930890
      Hello.

      I have the following scenario.
      When user presses f7 to enter query mode, he has a choice to immediately press f8 to execute query(retrieve all data) or to enter values in random fields and then press f8. So in first case, if user doesn't enter any values, I need to know that user didn't enter any values, so I can execute a select statement with proper where clause.
      So is it possible to get select statement that would be executed on pressing f8 in pre-query trigger and modify it or just to know if user has entered any values while in query mode ?

      Thanks in advance.
        • 1. Re: Current Query in Query mode
          InoL
          You can use the pre-query trigger. You can check if an item has a search value or not. If not, you can enter your default search value. Something like this:
          if :block.item1 is null
          and :block.item2 is null
          and :block.item3 is null
          ...
          then
              :block.item1 := <some value>;
              ...
          end if;
          • 2. Re: Current Query in Query mode
            930890
            Thanks, for your reply, however I was aware of that solution which I wanted to use as last resort, because I was avoiding writing down an if clause, that would include checking value of 20+ items. I was searching the net if oracle forms contains something like current_query, similar to system.last_query (which is useless in pre-query trigger). Oh and default_where is out of the case, because it is always empty.
            Anyway I am getting a feeling that large if clause is inevitable :).

            Thanks.
            • 3. Re: Current Query in Query mode
              InoL
              Anyway I am getting a feeling that large if clause is inevitable
              No it doesn't. You can loop over the queryable items in you block.
              • 4. Re: Current Query in Query mode
                930890
                Will use this loop solution.

                Thanks.
                • 5. Re: Current Query in Query mode
                  930890
                  I've encountered another problem.
                  When I loop through all items on data block, i don't know how to get current value of that item.

                  Here's part of my code:

                  loop
                  w_nextItem := get_item_property(w_block||w_nextItem,nextitem);
                  begin
                  w_item := find_item(w_block||w_nextItem);

                  if lower(get_item_property(w_block||w_nextItem,item_type)) <> 'button' then
                  if get_item_property(w_block||w_nextItem, database_value) is not null then
                  return true;
                  end if;
                  end if;
                  exception when others then
                  null;
                  end;
                  exit when w_lastItem = w_nextItem;
                  end loop;

                  I tried to use database_value property, but that would return value fetched from database and I need the one user entered in query mode.
                  So is it possible to get the value of an item through properties or somehow else ?

                  Thanks.
                  • 6. Re: Current Query in Query mode
                    HamidHelal
                    So is it possible to get the value of an item through properties or somehow else ?
                    Just use of following things
                    variable:=:block.column_name;--- assign the value of column_name in to a variable.
                    Hope this helps..
                    • 7. Re: Current Query in Query mode
                      930890
                      Thanks for reply, however I am aware of that, but I would like to get item value like this:

                      w_nextItem := get_item_property(w_block||w_nextItem,nextitem); --here i get name of next item

                      w_item := find_item(w_nextItem ); -- create variable of type item

                      Now i would like to get value of item stored in variable w_item.

                      Thanks.
                      • 8. Re: Current Query in Query mode
                        HamidHelal
                        927887 wrote:
                        Thanks for reply, however I am aware of that, but I would like to get item value like this:

                        w_nextItem := get_item_property(w_block||w_nextItem,nextitem); --here i get name of next item

                        w_item := find_item(w_nextItem ); -- create variable of type item

                        Now i would like to get value of item stored in variable w_item.
                        try this
                        variable:=:w_item;  -- not tested
                        
                        OR
                        variable:=':'||w_item;
                        Hope this works..

                        Edited by: HamidHelal on Apr 17, 2012 2:19 PM
                        • 9. Re: Current Query in Query mode
                          Amatu Allah Neveen Ebrahim
                          Hi

                          u can display it in a message or in a display item no ?!

                          Hope this helps...

                          Regards,

                          Amatu Allah
                          • 10. Re: Current Query in Query mode
                            930890
                            Nothing works so far, and just for note, I am using forms 6i.
                            • 11. Re: Current Query in Query mode
                              HamidHelal
                              927887 wrote:
                              Thanks for reply, however I am aware of that, but I would like to get item value like this:

                              w_nextItem := get_item_property(w_block||w_nextItem,nextitem); --here i get name of next item

                              w_item := find_item(w_nextItem ); -- create variable of type item

                              Now i would like to get value of item stored in variable w_item.
                              try this, tested
                              v_value:= get_item_property(w_item,ITEM_NAME); ---v_value is a variable with varchar2 data type. gives item name.
                              V_VALUE:=Name_In(v_value); --gives item value if any
                              Hope this works..


                              If someone's response is helpful or correct, please mark it accordingly.
                              • 12. Re: Current Query in Query mode
                                930890
                                Thanks, that worked
                                • 13. Re: Current Query in Query mode
                                  HamidHelal
                                  927887 wrote:
                                  Thanks, that worked
                                  you should make reward those, who helped you in your problem.