| SQL Query in Oracle Forms [message #86840] |
Tue, 09 November 2004 09:16 |
Mamdani
Messages: 1
Registered: November 2004
|
Junior Member |
|
|
I have two tables with following fields
1. Trans_master
trans_id varchar2 -- Primary key
tdate date
account_no varchar2 -- Foreign key
Data for your understanding
Trans_id tdate account_no
0001 01-Oct-04 1234-01
0002 02-Oct-04 6789-02
0003 03-Oct-04 6789-02
0004 04-Oct-04 1234-01
2. Trans_Detail
trans_id varchar2 -- Foreign key
Debit number
credit number
tag char(1)
tag_dt date
Trans_id debit credit tag tag_Dt
0001 500 Y 02-Oct-04
0001 1000 N 03-Oct-04
0002 600 N 03-Oct-04
0002 400 Y 04-Oct-04
0003 700 Y 06-Oct-04
0003 900 N 06-Oct-04
0004 800 Y 07-Oct-04
0004 100 N 07-Oct-04
Parkar, I want to make a query/view form based on Trans_detail table in which the user cannot insert, update or delete data except 'tag' field which can be updated. for that purpose I created a dummy block 'Control' and data block 'Trans_detail' based on table. In dummy block I created an item 'Acc_no'.
Now I want to retrieve the data like this:
Control Block
Acc_no 1234-01 (after pressing Enter key, KEY-NXT-ITEM trigger should fire and execute query in detail block)
Trans_Detail Block
Trans_id debit credit tag tag_Dt
0001 1000 N 03-Oct-04
0004 100 N 07-Oct-04
In SQL prompt I wrote the following query and it worked correct as I wanted.
select b.*
from trans_master a, trans_detail b
where a.trans_id = b.trans_id
and a.account_no = '1234-01'
and a.tag = 'N'
Please tell me how can I use above query in forms?
Your early reply in this regard will highly be appreciated.
Thanks
Mamdani
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
