Query problem - why cant I query my date field?

Posted on 2004-03-23
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...
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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 47

Assisted Solution

schwertner earned 250 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 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
data lookup in Oracle - need suggestions 55 113
SQL query question 8 78
what privileges needed for S2 for this function (Oracle 12c)? 3 22
Oracle encryption 12 31
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

777 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