| query field based on detail block but on main block canvas [message #441881] |
Wed, 03 February 2010 10:43  |
nastyjillu
Messages: 211
Registered: February 2009
|
Senior Member |
|
|
Hi,
i have got problem in querying a field which is in detail block but it is present on canvas besides main block fields.
i have 2 blocks. block A and block B.
block B is child to block A. but both doesnt look like master-detail blocks in the layout.
block A is based on tabA, block B is based on tabB
Primary key in block A is db_id and this is foreign key in block B as wdb_id.
now there is a field called eqip_no on block B based on tabB which is present besides fields on block A on the canvas.
now i can query the fields based on block A but not able to query equip_no field though query allowed of equip_no is 'yes'.
when iam in enter query mode(hit f7), iam able to navigate in block A fields but cant navigate in equip_no though key navigate allowed is 'yes'.
end users want to query in equip_no field.
solution i thought is i will create a non database item in block A namaed dsp_eqip_no .
and will write a key-exeqry trigger on this non database item so that it populates block A fields depending on the value entered in dsp_equip_no.
in post-query trigger on block A i will write code such that it gets value from tabB for given db_id of tabA and display in dsp_eqip_no.
am i right??
thanks
jillu
|
|
|
|
| Re: query field based on detail block but on main block canvas [message #441884 is a reply to message #441881] |
Wed, 03 February 2010 10:56  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'd base the block on a view or procedure that joins the two tables together.
Enter-query mode is block specific. Whatever block you are in when you enter enter-query mode you are stuck in until you execute or cancel the query.
As for your alternative, it'll only work if equip_no is duplicated in both tables, and I'm sure it isn't otherwise you wouldn't be in this position in the first place.
To query a table based on data in a different table a view or procedure is your only real option.
|
|
|
|
| Re: query field based on detail block but on main block canvas [message #441885 is a reply to message #441884] |
Wed, 03 February 2010 11:04 |
nastyjillu
Messages: 211
Registered: February 2009
|
Senior Member |
|
|
if i write a view based on 2 blocks then do i have to create a non database item in block A which displays equip_no values??
is there other way which doesnt require view on 2 tables??
was my solution correct?? can i create dsp_equip_no on block A which is non databse and in key-exeqry of that item write code to populate main block items depending on the value passed in dsp_equip_no??
is it possible to query in non databse fields??
|
|
|
|
| Re: query field based on detail block but on main block canvas [message #441886 is a reply to message #441885] |
Wed, 03 February 2010 11:19 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
nastyjillu wrote on Wed, 03 February 2010 17:04if i write a view based on 2 blocks then do i have to create a non database item in block A which displays equip_no values??
Views are based on tables not blocks. You'd write a view that joins the two tables then have a single block based on that. At which point you can have a database item in the block for equip_no because that column would exist in the view.
Quote:
is there other way which doesnt require view on 2 tables??
As I already said - a procedure. If the join between the tables is simple enough a view is probably easier, it depends on if you want to be able to insert/update data and if you can create an updatable view.
Quote:
was my solution correct??
Not as far as I can see.
Quote:
can i create dsp_equip_no on block A which is non databse and in key-exeqry of that item write code to populate main block items depending on the value passed in dsp_equip_no??
Unless you want to override all of the default processing I really don't see how that's going to work. You'd have to write code to populate the block(s) using a cursor and that would mean that you can't:
a) use enter-query mode
b) modify the data - unless you write special code that overrides oracle forms default update processing, because if you populate the block that way oracle will assume they are new records and it will try to insert rather than update when you click save.
And if you find yourself overriding default processing to that extent you should just stop and base the block on a procedure instead - it'll be easier.
Quote:
is it possible to query in non databse fields??
Not really no, unless you code it all yourself as stated above.
All enter-query mode does is take the values you've entered and append them to a where clause to query the table or view the block is based on. If the item doesn't correspond to a column in the table/view then there is nothing forms can do with it.
|
|
|
|
| Re: query field based on detail block but on main block canvas [message #441902 is a reply to message #441886] |
Wed, 03 February 2010 14:56 |
nastyjillu
Messages: 211
Registered: February 2009
|
Senior Member |
|
|
if i create view joining 2 tables, what if i want to insert or update data?
do i need to create instead of trigger on that view for inserts and updates??
creating view might change the present application which my team might not agree.
i might be repeating the same thing, but key-exeqry trigger on dsp_equip_no doesnt work??
in key-exeqry trigger on dsp_equip_no field i will write below example query:
select wdb_id into :blockA.db_id
from tabB
where equip_no=:blockA.dsp_equip_no;
query might be in cursor
i was thinking about solution other than creating view or procedure.
thanks
jillu
|
|
|
|
|
|
| Re: query field based on detail block but on main block canvas [message #441906 is a reply to message #441903] |
Wed, 03 February 2010 17:17 |
nastyjillu
Messages: 211
Registered: February 2009
|
Senior Member |
|
|
i observed something in the same form.
there is a non database field dsp_id_per on block A. and it is queriable.
in pre-query trigger of block A, there is a default where condition which takes input given in enter query mode for dsp_id_per.
and in post-query trigger, dsp_id_per field is being populated.
i can apply the same to dsp_equip_no.
thanks jillu
|
|
|
|
| Re: query field based on detail block but on main block canvas [message #441909 is a reply to message #441902] |
Wed, 03 February 2010 18:50 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
nastyjillu wrote on Wed, 03 February 2010 20:56if i create view joining 2 tables, what if i want to insert or update data?
do i need to create instead of trigger on that view for inserts and updates??
If the view itself is updateable it should just work, otherwise you'll need instead of triggers.
Quote:
i might be repeating the same thing, but key-exeqry trigger on dsp_equip_no doesnt work??
in key-exeqry trigger on dsp_equip_no field i will write below example query:
select wdb_id into :blockA.db_id
from tabB
where equip_no=:blockA.dsp_equip_no;
And what happens if that select returns more than one row? Which is bound to happen sooner or later if equip_no is not unique.
Quote:
on a canvas few fields belong to block A and one field belong to block B.
when i am able to query fields of block A, then why am i not able to query block B field?
I already told you that:
cookiemonster wrote
Enter-query mode is block specific. Whatever block you are in when you enter enter-query mode you are stuck in until you execute or cancel the query.
If you were in block B when you went into enter-query mode then you'd be stuck in that block until you execute the query instead. However it generally doesn't make sense to go into enter-query mode in the child block, especially since it's always restricted by the parent anyway.
nastyjillu wrote
there is a non database field dsp_id_per on block A. and it is queriable.
in pre-query trigger of block A, there is a default where condition which takes input given in enter query mode for dsp_id_per.
and in post-query trigger, dsp_id_per field is being populated.
i can apply the same to dsp_equip_no.
I would be interested to see the actual code, cause I can't think of anyway you can guarantee something like that is going to work.
|
|
|
|
| Re: query field based on detail block but on main block canvas [message #441912 is a reply to message #441909] |
Wed, 03 February 2010 21:07 |
nastyjillu
Messages: 211
Registered: February 2009
|
Senior Member |
|
|
my view cant be updatable as it will be based on more than one table.
regarding my solution about pre-insert and post-insert triggers,
i might not be able to insert values in dsp_equip_no fields if i follow that procedure.
because id_per is non database item in block A but there is another item in block A which is database item and it is being used in populating id_per field which is not the case with dsp_equip_no.
oh man. i am struck here
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
