Data Block DEFAULT_WHERE property issue [message #300048] |
Thu, 14 February 2008 01:52  |
 |
didiera
Messages: 134 Registered: August 2007 Location: Mauritius
|
Senior Member |
|
|
Hello,
I have a question here with DEFAULT_WHERE property acting strange at runtime. I have a master and 2 details blocks (sales and refunds). They all are linked to a single table. I use the master data block as a query source (the 2 detail blocks having the same fields as the master plus a couple of specific fields on top).
Anyway my problem is that when i query the table, i use a set of criteria on the fly, passed in the details blocks as DEFAULT_WHERE values respectively. I guarantee you there is no problem on the string generated and passed to that property but what I have noticed when debugging at runtime (via LAST_QUERY system variable) is that on top of my 'where-condition', I get a bunch of braces-enclosed fields which in fact are the ones from my master data block. An example below:
SELECT ROWID,ID_HERE,VALUE_THERE WHERE TYPE_CODE = 'S' AND HIST IS NULL AND (ID_HERE = 1) AND (VALUE_THERE = 200)
...now what is this ? "AND (ID_HERE = 1) AND (VALUE_THERE = 200)" while I only had the first part in the where clause : "TYPE_CODE = 'S' AND HIST IS NULL"
If this is due to the master-detail relationship (which I suspect), could you tell me how to suppress itplz.
thx
Didier
|
|
|
|
Re: Data Block DEFAULT_WHERE property issue [message #300720 is a reply to message #300699] |
Sun, 17 February 2008 23:10   |
 |
didiera
Messages: 134 Registered: August 2007 Location: Mauritius
|
Senior Member |
|
|
Thanks for the reply David, though I'm not getting your point here. Anyway let me add the info below, maybe it could clear out a few stuffs.
The form is quite a simple one and is laid out the following way:
We have a single table to which all the 3 data blocks are linked. Say that the table is meant to store sales and refund data, with a field that distinguishes sales records from refunds records. In any cases, you have a bunch of common fields pertaining to both that I use in the master data block for querying. Also, there is a business rule that states that whenever i query a record for modification and save it, the record should be duplicated in the table and its default HIST field flagged - thus notifying that it is a modified one.
So all this boils down to having a master data block set to contain common fields and a pair of data blocks for sales and refunds records respectively. I thinks you can figure out what it looks like from there. So maybe you would have for example in the master data block doc_number, doc_date, client_name and in the sales and refunds data blocks : mode_of_payment, payment_amount.
My 'Query' button is meant to change the DEFAUT_WHERE values of both sales and refunds data blocks to refect these records that would match the doc_number and doc_date, I don't want the query to include any other fields on the master data block with the records in the table. It's true that I have set relationships between the master data block and the sales and refunds block and I'm pretty sure it's all firing from there. Oracle Forms has included some generic triggers when I laid out these relationships; do you think I should scan these code also? Maybe the solution is over there?
thanks anyway
Didier
[Updated on: Sun, 17 February 2008 23:17] Report message to a moderator
|
|
|
|
Re: Data Block DEFAULT_WHERE property issue [message #300780 is a reply to message #300736] |
Mon, 18 February 2008 02:10   |
 |
didiera
Messages: 134 Registered: August 2007 Location: Mauritius
|
Senior Member |
|
|
Ok David, got your point and did what you said. I understood that you meant to provide me a means of knowing wether the LAST_QUERY I was looking at was actually the one related to the data block in question. In fact I did that and I can assure you that it is and that i'm still facing the problem. I have posted a screenshot of the Stack Trace at the moment when the Execute_query is invoked on data block. As an element of comparison, below is the value of the LAST_QUERY system variable:
LAST_QUERY = SELECT ROWID,FPTP_CODE,FPTP_VALUE,AGTN,SIDT,SRCI,ETCA,PERI,SALE_RFND,CUTP,DATE_ETCA,USID,USID_REFE,CTDT,HIST
FROM ETCA WHERE SALE_RFND = 'R' AND PERI = 2004051 AND HIST IS NULL AND (SRCI='923-IT') AND (ETCA=1407)
AND (PERI=2004051) AND (CUTP='EUR') AND (DATE_ETCA=TO_DATE('06-02-2008','DD-MM-YYYY') AND (USID='EDGAR')
AND (CTDT=TO_DATE('18-02-2008','DD-MM-YYYY')))
Notice the fields enclosed in braces...they are the ones messing with my data block and I believe they have been added due to the relationship from the master data block. Otherwise the Stack Trace shows that the default_where is correct and contains no extra fields whatsoever - hence should come up with the desired results (variables WHERECONDITIONSALES and WHERECONDITIONREFUND).
Hope that will help a bit more ... I'm desperate
[Updated on: Mon, 18 February 2008 02:12] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Data Block DEFAULT_WHERE property issue -CASE CLOSED :) [message #301500 is a reply to message #301352] |
Wed, 20 February 2008 20:50  |
 |
djmartin
Messages: 10181 Registered: March 2005 Location: Surges Bay TAS Australia
|
Senior Member Account Moderator |
|
|
I don't agree with your approach. I believe that the basis for your master-detail form and the intention of performing queries that do not confirm to that relationship is inappropriate. A master-detail is a master-detail. If you wish to do non-detail queries then write and use a different form.
David
|
|
|