Home » Developer & Programmer » Forms » Query the record based on date (Oracle forms 9i. ,9.0.2.0, windows xp sp2)
Query the record based on date [message #401181] Sat, 02 May 2009 01:00 Go to next message
honey_28112007
Messages: 10
Registered: March 2009
Junior Member
I have a order table having 4 columns
cid
oid
Date_of_order and
delivery_date.
I have a block based on it. I want to query the block so that it can give me orders between two dates i've entered. Like i want the orders placed between two dates e.g. 1-apr-2009 and 30-apr-2009.
What should i do..? thx in advance
Re: Query the record based on date [message #401208 is a reply to message #401181] Sat, 02 May 2009 10:54 Go to previous messageGo to next message
klat
Messages: 87
Registered: May 2009
Location: Mumbai
Member

hi,


Set DEFAULT_WHERE (SET_BLOCK_PROPERTY) dynamically and execute query.
eg: set_block_property('<block_name>',default_where,date_of_order between '01-apr-2009' and '30-apr-2009')

or you can set the 'WHERE Clause' of the block's property

Re: Query the record based on date [message #401215 is a reply to message #401208] Sat, 02 May 2009 13:22 Go to previous messageGo to next message
honey_28112007
Messages: 10
Registered: March 2009
Junior Member
I tried but its not working......By the thax for reply...
any more suggestion plz.....
Re: Query the record based on date [message #401217 is a reply to message #401181] Sat, 02 May 2009 14:02 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
honey_28112007
I want to query the block so that it can give me orders between two dates i've entered
Where did you enter those dates? Which items? Did you create, for example, a control block which contains two DATE items? If so, then @klat's DEFAULT_WHERE suggestion *should* work - only if you know how to properly use it. Saying that "it's not working" is useless. Why is it not working? Was there any error message? If so, which one? If not, how does your statement look like? To me, it seems that you don't know how to properly use this functionality and not that it doesn't work. You aren't the very first one who uses (or is supposed to use) the DEFAULT_WHERE and, believe me, it works.




Basically, you don't have to do anything: Forms is capable of querying data the way you have described by using "query variables". Here's how it works:

  • Run the form; enter query mode
  • Instead of specifying value, enter a query variable into an item. It starts with a colon (:) sign and a name (any name would be OK. For example ":a" or ":my_date" etc.). This is an example based on Scott's EMP table:

    ./fa/6130/0/

  • Now press EXECUTE QUERY. A "Query/Where" window will open. Enter your search criteria in there, just as you'd put it into a SELECT's WHERE clause. For example:

    ./fa/6131/0/

  • Finally, click the <OK> button. Records you're interested in will be displayed:

    ./fa/6132/0/

As you've seen, that's really nothing much (just if you know how to use it).

Just as an add-on: you may use as many variables as you wish. For example, if you wanted to select employees who were hired between dates from a previous example and whose names contain letter "A", you might have done that as follows:
- enter ":A" into the ENAME item
- enter ":B" into the HIREDATE item
- WHERE clause would be
:a like '%A%' and
:b between to_date('01.03.1981', 'dd.mm.yyyy')
       and to_date('01.09.1981', 'dd.mm.yyyy') 


So, yes - there usually is more than just one way to solve the problem. Though, you should learn how to properly ask the question, explain what you have, what you did and how the system responded. Without all of these, we will probably not be able to help much.
  • Attachment: frm_q_2.PNG
    (Size: 5.14KB, Downloaded 273 times)
  • Attachment: frm_q_1.PNG
    (Size: 1.59KB, Downloaded 260 times)
  • Attachment: frm_q_3.PNG
    (Size: 2.78KB, Downloaded 265 times)
Re: Query the record based on date [message #401250 is a reply to message #401217] Sun, 03 May 2009 05:15 Go to previous messageGo to next message
honey_28112007
Messages: 10
Registered: March 2009
Junior Member
Thank you very much........
Next time i will keep everything in my mind u said..and explain my problem more clearly..
Re: Query the record based on date [message #401253 is a reply to message #401217] Sun, 03 May 2009 05:29 Go to previous messageGo to next message
honey_28112007
Messages: 10
Registered: March 2009
Junior Member
yes i have taken two text item of date data type in control block..In that case how would i query the orders for those two days..
Re: Query the record based on date [message #401263 is a reply to message #401253] Sun, 03 May 2009 09:06 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How? By doing some research on your own. You don't expect each and every problem you have to be explained in details, do you? @klat has provided keywords; you can do the rest.

Once you produce a solution which doesn't work as you'd want it to, feel free to come back, show us what you did, explain why it doesn't fit your requirements and someone will provide help.
Re: Query the record based on date [message #401407 is a reply to message #401263] Mon, 04 May 2009 12:16 Go to previous messageGo to next message
honey_28112007
Messages: 10
Registered: March 2009
Junior Member
Very Very.. Thank you to all u senior member....I did it..
Re: Query the record based on date [message #401411 is a reply to message #401407] Mon, 04 May 2009 15:13 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad if you did. Would you mind to share your solution with the rest of the community, so that other people might learn something from it?
Previous Topic: forms 6i export data
Next Topic: keypress erases everything
Goto Forum:
  


Current Time: Sat Dec 03 18:08:34 CST 2016

Total time taken to generate the page: 0.07348 seconds