?
Solved

Query problem - why cant I query my date field?

Posted on 2004-03-23
9
Medium Priority
?
2,104 Views
Last Modified: 2012-06-21
Hi,

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!
0
Comment
Question by:emmy21
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Expert Comment

by:mudumbe
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)
0
 

Author Comment

by:emmy21
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.
0
 
LVL 3

Expert Comment

by:kannankumara
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...
0
Technology Partners: 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!

 
LVL 5

Expert Comment

by:gmyers
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.


0
 
LVL 48

Assisted Solution

by:schwertner
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.

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


or

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

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

Expert Comment

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

Author Comment

by:emmy21
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
Emma
0
 
LVL 13

Accepted Solution

by:
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:


Set_block_propoerty('Master_Block',default_where,'lesse_date=:block1.item1');
go_block('Master_Block');
execute_query;
set_block_propoerty('Detail_Block',default_where,'PK_FK_Relation_Between_Master_Detail');
go_block('Detail_Block');
execute_query;
go_item(:block1.item1);
0
 
LVL 13

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses

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