Home » Developer & Programmer » Forms » Data Block DEFAULT_WHERE property issue
Data Block DEFAULT_WHERE property issue [message #300048] Thu, 14 February 2008 01:52 Go to next message
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 #300699 is a reply to message #300048] Sun, 17 February 2008 21:54 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If the 'select' does not contain the fields associated with either of the detail blocks then I would assume that the 'select' is for the master block. You can verify this by adding a non-displayed database field to the master that is unique to that block. The existance of this unique field should be reflected in the 'select' statement.

I believe that you either have default values on the two fields 'id_here' and 'value_there', or you actually placed these data values in then via the master fields on the canvas.

David
Re: Data Block DEFAULT_WHERE property issue [message #300720 is a reply to message #300699] Sun, 17 February 2008 23:10 Go to previous messageGo to next message
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 #300736 is a reply to message #300720] Sun, 17 February 2008 23:52 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
1) Add a different 'extra' field to each block.
2) Run form.
3) Look at 'last_query'.
4) You will now know to which statement you are looking as each has a different 'extra' field.

David
Re: Data Block DEFAULT_WHERE property issue [message #300780 is a reply to message #300736] Mon, 18 February 2008 02:10 Go to previous messageGo to next message
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 Sad

[Updated on: Mon, 18 February 2008 02:12]

Report message to a moderator

Re: Data Block DEFAULT_WHERE property issue [message #300954 is a reply to message #300780] Mon, 18 February 2008 17:51 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Didier,

You have way too many words in your posts and too little information. ALL THREE blocks are 'data blocks'.

What I want to know is, is the query from the 'master' block or a 'detail' block.

I expect that the query is from one of the two 'detail' blocks. The fields in brackets are generated by the 'relations' that has been build between the master and detail block. To change these fields you need to change the 'relations'.

David
Re: Data Block DEFAULT_WHERE property issue [message #300976 is a reply to message #300954] Mon, 18 February 2008 21:44 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
It's all happening in the details blocks in fact. It's just as I suspected then, it's the relationship between them that is doing that.
Ok I'll try to look further in this direction. But maybe you could give me some clues on that if you've even faced such an issue. I mean, what properties of the relationship might I play with or in what triggers should I look for this behaviour?

P.S: My intention was not to write a novel about that problem but rather to try to be as clear as possible and to give as accurate a picture of the situation as possible so nothing might be overlooked . We all know how debugging can be daunting especially when you're not doing it on site.
Cheers anyway mate Laughing
Re: Data Block DEFAULT_WHERE property issue [message #300981 is a reply to message #300976] Mon, 18 February 2008 22:07 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
If this might help, here's the config for the relationships:

Functional

Delete Record Behaviour: Isolated
Prevent Masterless Op. : No
Coordination
Deferred : yes
Auto Query : No

This stands for both details blocks.

Now I also have those extra triggers added along with the relationships:

ON-CLEAR-DETAILS (in Triggers at the top-most level)
ON-POPULATE-DETAILS (in Triggers under the master data block)


regards,
Didier
Re: Data Block DEFAULT_WHERE property issue [message #300982 is a reply to message #300976] Mon, 18 February 2008 22:07 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
You can play with ALL the properties of the 'relations' (you can define/redefine the 'join condition', make the 'Master Deletes' Cascading, Isolated, or Non-Isolated, make the Coordination Deferred, and even Prevent Masterless Operation) but only at design time in the Forms Builder.

David
icon14.gif  Re: Data Block DEFAULT_WHERE property issue -CASE CLOSED :) [message #301352 is a reply to message #300982] Wed, 20 February 2008 03:26 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hi again,

This time I thing I've got it and i'm gonna put a bullet in my head! Some details data blocks' fields have a binding with their master counterparts via the 'Copy Value from Item' property. I've just removed these from on one detail block and launched the query; guess what...it works. So stupid a thing - though now I have to ensure that the master fields are actually available for subsequent entries in the details blocks.

yohooo! Cool

"When the code's in debug ma'
Drop it like it's hot (*3)
When bug they just nag at 'ya
Park it like it's hot (*3)
And if a DEFAULT_WHERE get a attitude
Pop it like it's hot (*3)
I got the rolly on my arm and I'm pouring Chandon And I roll the best weed cause I got it going on"
(courtesy of the Dogg Father yo!)

- Im pole-dancing with delight right now Razz

cheers DJ, u sure rocked on that (though I made it on my own).

[Updated on: Wed, 20 February 2008 03:27]

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 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: Error while running OC4J instance
Next Topic: How to control the opening form multiple times
Goto Forum:
  


Current Time: Fri Dec 09 09:52:35 CST 2016

Total time taken to generate the page: 0.22924 seconds