[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Query problem - why cant I query my date field?

Posted on 2004-03-23
Medium Priority
Last Modified: 2012-06-21

Oracle 9i, forms 6i. Basically I have a booking system where I am using date_time field formatted to 'DY DD-MON-YYYY, HH24:MI'. I have discovered that I cannot effectively query my forms, I type in a query date exactly in the format above, for example, and when I execute, it just returns all records. I do not understand why it does this, it is recognising the date format as if i enter in MAR for example, it will show an error telling me I havent entered the date according to the format mask. A bit of info:  I change the session date format on opening the relevant forms (as I need the date format above including the time) and then change it back to DD-MON-YYYY on form close

I am using a tab canvas for each different type of 'lesson' and there is a where clause on the data block for each tab page that specifies each lesson type. The query only actions on the current ab page, and does return the correct records for each tab page type, but as described above when querying, it simply returns all records.

I need some way of allowing the user to specify the date, they cant navigate through all the lesson records each time they wish to make a booking. Please help!
Question by:emmy21

Expert Comment

ID: 10661090
Is your date item a 'Database item'?  If not, Forms will not include that item when executing the query.

Also, do you have special code that alters the 'default' query condition (usually in PRE-QUERY trigger)

Author Comment

ID: 10661218
Yes, each page is based on the lesson block, so I have ended up with lesson block, lesson1 block, lesson2 block where I have the need for 3 different lesson types on different tab pages.

There is no Pre-Query trigger, no fiddling at all apart from the where clauses attached to each block on each page to filter the lesson types onto each tab page.

Expert Comment

ID: 10663035
Just to slimdown the error situation to this field... have you checked if the querying works ok for all other fields? When we do special processings, most of the time our mind tends to look at this spl processing only. In your case the special processing is the date format and so you might look only the date format errors. If you check and confirm all other fields query the records properly, then we are certain that this is because of the datetime format error.

We will take it from there...
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Expert Comment

ID: 10663094
To go through the steps :

1. Form is put into enter query mode.
2. User enters date in format specified by format mask on item
3. Form does Execute query - creates a SELECT .... FROM ...WHERE...
4. Query sent to Server and result returned from server

If you don't go into enter-query mode under step 1, you will by default, be in insert mode.
If you try to execute a query while in insert mode, you would probably get a prompt asking you to save data (though if there's any code to clear block or somesuch, this may not happen).

That's the first place I would look for a problem.

Second place is the query sent to the server. You can use :SYSTEM_LAST_QUERY to see what that was and if the predicate on the date was included.
Alternatively, you could do a trace (ALTER SESSION SET SQL_TRACE=TRUE)  and look at the server trace logs.

LVL 48

Assisted Solution

schwertner earned 750 total points
ID: 10665034
When you use dates for query you should be very carefull about the time component.
If you are looking for date comparison only you have to trim the time component stored in every date.

FROM   ...
WHERE trim(date_column) = trim(:date_item);


FROM   ...
WHERE TO_CHAR(date_column,'YYYYMMDD') = TO_CHAR(:date_item,'YYYYMMDD') ;

To compare dates with time component is not the usual case.
LVL 13

Expert Comment

ID: 10665558
If you are receiving all the records, then hopefully, you haven't used "Where Clause" property of the block.

Author Comment

ID: 10670108
ok, basically I have got a LOV working so that the user can select a master (lesson) record and the detail records (bookings, if any) will appear for that lesson.

I have included the date_time in the LOV as TO_CHAR for display purposes only showing the date and the time - and where I was going wrong was the conversion to pass the selected value back to the form for the query, so I have passed the original date_time through the LOV as well without a conversion, and this works.

 The problem is, I have a master detail form which I want the user to be able to query via a LOV (as they might not know a lesson exists before they look to see what is available) and typing in the date and time is a little longwinded but - I also need the LESSON master block to be protected from inserts etc. Yet the LOV seems to act as an insert even in query mode, and not regarded the same as if the user physically types in the date_time into the field. Or maybe my design should be that i create lessons on the master detail form in the first place, and so both blocks are updateable??

Thanks for your help
LVL 13

Accepted Solution

riazpk earned 750 total points
ID: 10671218
I, once had the smae issu. The simple solution is:

Create a non-database block (say block1) and create an item (say item1)  in it (non-database). Attach LOV to it.

Set both the master & detail blocks proporties (insert allowed, delete allowed, update allowed) to false.

In the Key-Next-Item trigger of Item1, use:

LVL 13

Expert Comment

ID: 10672597
And if you can tell me your email address, i will send you sample form(emp-dept blocks) doing the same.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question