Home » Developer & Programmer » Forms » How to display data in column of table to different fields of database block in oracle forms 11g (Oracle Forms 11g )
How to display data in column of table to different fields of database block in oracle forms 11g [message #603174] |
Thu, 12 December 2013 00:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mary.yujin
Messages: 26 Registered: June 2010
|
Junior Member |
|
|
I have a table with 3 fields.
Table Name : ACC_CC_MASTER
cc_code number(5),
cc_desc varchar2(200),
cc_class_code number(5)
The table stores heirarchical information. Sample data is as shown below
Asset
Current Asset
Accounts Receivable
Other current asset
Fixed Asset
Machinery
Acc.Depr
Office eqpt
Liability
Liability
Equity
My requirement is to display the above data in each level in different fields of a database block in oracle 11g. Means Asset, Liability in first column of database block. Current Asset, Fixed Asset, Liability & Equity in second column of database block..like that.
Please advise me any solution
[EDITED by LF: applied [pre] tags to preserve formatting]
[Updated on: Thu, 12 December 2013 01:41] by Moderator Report message to a moderator
|
|
|
Re: How to display data in column of table to different fields of database block in oracle forms 11g [message #603186 is a reply to message #603174] |
Thu, 12 December 2013 02:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Why wouldn't you simply create a tree?
If you insist on a data block, then - I believe - you should base it on a view (as it seems to be simpler than other options). A view would actually create a tree structure and then extract each column depending on a tree level. Here's an example:SQL> WITH test AS (SELECT 1 cc_code, 'asset' cc_desc, NULL cc_class_code FROM DUAL
2 UNION
3 SELECT 2, 'current asset', 1 FROM DUAL
4 UNION
5 SELECT 3, 'accounts receivable', 2 FROM DUAL
6 UNION
7 SELECT 4, 'other current asset', 2 FROM DUAL
8 UNION
9 SELECT 5, 'fixed asset', 1 FROM DUAL
10 UNION
11 SELECT 6, 'machinery', 5 FROM DUAL
12 UNION
13 SELECT 7, 'acc. depr', 6 FROM DUAL
14 UNION
15 SELECT 8, 'office eqpt', 5 FROM DUAL),
16 tree
17 AS ( SELECT LEVEL lvl,
18 cc_code,
19 cc_desc
20 FROM test
21 CONNECT BY PRIOR cc_code = cc_class_code
22 START WITH cc_class_code IS NULL)
23 SELECT DECODE (lvl, 1, cc_desc) lvl_1,
24 DECODE (lvl, 2, cc_desc) lvl_2,
25 DECODE (lvl, 3, cc_desc) lvl_3,
26 DECODE (lvl, 4, cc_desc) lvl_4
27 FROM tree;
LVL_1 LVL_2 LVL_3 LVL_4
---------- -------------------- ------------------------- ---------------
asset
current asset
accounts receivable
other current asset
fixed asset
machinery
acc. depr
office eqpt
8 rows selected.
SQL>
A form would, then, fetch view values and populate data block items. Note that Forms can't do that "dynamically", so you'll have to pre-define max number of columns (4 levels in this example). Otherwise, some columns/levels won't be visible in that form.
|
|
|
Goto Forum:
Current Time: Wed Aug 21 19:22:18 CDT 2024
|