 Maintain user input's visibility [message #132611] |
Mon, 15 August 2005 03:17  |
aarontan78
Messages: 63
Registered: August 2005
|
Member |
|
|
Hi all,
I created a form which allowed user to enter input (eg. 2005) in a field to perform query.
I would like the user input (2005) to remain visible in the field after I execute query.
How should I do that?
Thank you.
Regards,
Aaron
|
|
|
|
|
|
|
|
|
|
|
|
|
|
 Re: Maintain user input's visibility [message #132733 is a reply to message #132725] |
Mon, 15 August 2005 21:25  |
aarontan78
Messages: 63
Registered: August 2005
|
Member |
|
|
David,
Okay ... item_z is 'empty' on display - correct? Please answer - yes or no.
--YES
Verify the properties for item_z. Are they different to the other fields that are displaying correctly?
--No difference
For example, is query-only set to 'true' for item_z?
--Query-only: NO
Below is the POST-QUERY:
DECLARE
CURSOR lookup_table IS
SELECT c.item_a,
a.item_b,
c.item_f,
FROM table_a,
table_c
WHERE a.item_a = c.item_a
AND c.item_c = :table_b.item_c;
BEGIN
OPEN lookup_table;
FETCH lookup_table
INTO :table_b.item_a,
:table_b.item_b,
:table_b.item_f;
CLOSE lookup_table;
END;
Please refer to attachment, thanks!!!
Regards,
Aaron
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Maintain user input's visibility [message #132916 is a reply to message #132780] |
Tue, 16 August 2005 19:01 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Use the 'exists' construct, for example,
I am working from memory, but I am assuming that the primary key of table_b is item_c plus item_d.
WHERE exists (select 'X'
from table_b b,
table_a a
where ( (b.item_e < 0 AND a.item_b = 'D')
OR (b.item_e > 0 AND a.item_b = 'C') )
and b.item_c = item_c
and b.item_d = item_d)
David
Update: I added more brackets to the logic.
[Updated on: Tue, 16 August 2005 20:13] Report message to a moderator |
|
|
|
|
|
| Re: Maintain user input's visibility [message #132923 is a reply to message #132922] |
Tue, 16 August 2005 20:11 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Not in Post-Query but in the 'default where' clause where you currently have the "WHERE ((table_b.item_e < 0 AND table_a.item_b = 'D') OR (table_b.item_e > 0 AND table_a.item_b = 'C'))" code.
'X' is 'X', just type it as I wrote it. It is 'nothing'. All you want is for the SQL to resolve either a true or false on the linkage by primary key and your other logic. Use 'exists' for 'true' and 'not exists' for selecting the 'false' returns.
David
Update: I added more brackets to the 'where' logic in the posting 2 above.
[Updated on: Tue, 16 August 2005 20:14] Report message to a moderator |
|
|
|
|
|
|
|
| DETAIL: Maintain user input's visibility [message #132936 is a reply to message #132926] |
Tue, 16 August 2005 22:40 |
aarontan78
Messages: 63
Registered: August 2005
|
Member |
|
|
Hi David,
Sorry to take up your time. Well, I'm using TOAD to test my sql statement. Below is the steps I gone through:
Table A
item_a (PK)
item_b
Table B
item_c(PK)
item_d(PK)
item_e
item_z (year: eg: 2005)
Table C
item_c(PK)
item_a(FK)
item_f
This is my EXACT QUERY in TOAD that I need to put it in form:
SELECT c.item_f, a.item_b, b.item_e
FROM table_a a, table_b b, table_c c
WHERE b.item_z BETWEEN :p_itemz1 AND :p_itemz2
AND b.item_c = c.item_c
AND c.item_a = a.item_a
AND ((b.item_e < 0 AND a.item_b = 'D') OR (b.item_e > 0 AND a.item_b = 'C'))
My steps are:
1. Create Table_B data block with item: c and e (database item) and item: a, f, b (non-database)
2. Create canvas to display the query and create p_itemz1 and p_itemz2 (non database) for user input.
3. Create a query button with WHEN-BUTTON-PRESS trigger as below:
begin
execute_query;
end;
4. Create New-Form-Instanse trigger as below:
begin
enter_query;
end;
5. Create POST-QUERY as below:
DECLARE
CURSOR lookup_table IS
SELECT c.item_a,
a.item_b,
c.item_f,
FROM table_a a,
table_b b,
table_c c
WHERE a.item_a = c.item_a
AND c.item_c = :table_b.item_c;
BEGIN
OPEN lookup_table;
FETCH lookup_table
INTO :table_b.item_a
:table_b.item_b
:table_b.item_f
CLOSE lookup_table;
END;
6. Insert ====
exists (select 'X' from table_b b, table_a a
WHERE ((b.item_e < 0 AND a.item_b = 'D') OR (b.item_e > 0 AND a.item_b = 'C'))
AND b.item_c = item_c
AND b.item_d = item_d
AND b.item_z BETWEEN :table_b.p_itemz1 AND :table_b.p_itemz2)
== into WHERE clause in Table_B data block's property.
Can you please highlight me where did I go wrong? If need to use PRE-QUERY, how do I create it?
Thanks a lot!
Regards,
Aaron
|
|
|
|
| Re: DETAIL: Maintain user input's visibility [message #132941 is a reply to message #132936] |
Tue, 16 August 2005 23:11 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
No you don't necessarily need to create Pre-Query.
You have TOAD - good. Open a SQL Editor window and place the suggested test SQL in it. That is,select *
from table_b
where exists (select 'X'
from table_b b,
table_a a
where ( (b.item_e < 0 AND a.item_b = 'D')
OR (b.item_e > 0 AND a.item_b = 'C') )
and b.item_c = item_c
and b.item_d = item_d)
Your items :p_itemz1 and :p_itemz2? On which block are they defined? Create a 'ctrl' block and place :p_itemz1 and :p_itemz2 on it. Always refer to items by both their block and item identifier.
Okay, there are two things for you to try - one is the query in SQl in TOAD, and the other is to define your 'parameters' in your form.
Get back to me when the SQL is giving you the reocrds you expect.
David
[Updated on: Tue, 16 August 2005 23:26] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
