How To Display Hierarchy List

Oct 1, 2009

query in order to display a hierarchy list. Example:

Database has three tables

Heading --> id, status, order, title
Sub-heading --> id, status, order, title, heading_id
Item --> id, status, order, title, heading_id, sub-heading_id, actual_content

order = the sequence in which they are to be displayed a sub-heading's heading_id refers to the parent heading, likewise an item's sub-heading_id refers to the parent sub-heading.

I assumed there was no need to track child content.

Now, the output format should be something like this, where the sub-headings are not repeated, nor are the items. Sort of like chapters in a book, things in a catalogue, or even topics in a forum.

Heading 1
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Heading 2
Sub-heading 1
Item 1
Item 2
Item 3
Heading 3
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Sub-heading 3
Item 1
Item 2
Item 3

Where I'm getting hung up is how to query the data so it's quick, efficient, clean and easy to read and maintain. I've only gotten to listing the sub-headings and already the query looks convoluted. So, how should I approach this? Should it be done all in one query, and always checking against the results to know when to print a new heading or sub heading. Or should I run multiple queries and save the results to a variable, and then run sub-queries off that? Is there a better way to do this, am I going about it the hard way or am I in the right direction and just haven't gone far enough with it yet?

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Display In List Non-Existent Value In Output

Mar 6, 2012

I am not sure if this is even possible however I will give it a try here. is there a way to take a value from an In List that doesn't exist in the table and get SQL PLus to output that value with an assigned decode construct. example.

1 select decode(count(object_name),1,'EXIST',null,'NOT EXIST',0,'NOT EXIST'),
2 object_name, object_type
3 from dba_objects where object_name in (
4 'GV_$METRICNAME',
5 'GV$METRICNAME',
6 'GV_$METRICGROUP',
7 'GV$METRICGROUP',
8 'ABC'
9 )
10* group by object_name, object_type
SQL> /

DECODE(CO OBJECT_NAME OBJECT_TYPE
--------- ------------------------- -------------------
EXIST GV_$METRICNAME VIEW
EXIST GV_$METRICGROUP VIEW
EXIST GV$METRICNAME SYNONYM
EXIST GV$METRICGROUP SYNONYM

and have it look like this:

DECODE(CO OBJECT_NAME OBJECT_TYPE
--------- ------------------------- -------------------
EXIST GV_$METRICNAME VIEW
EXIST GV_$METRICGROUP VIEW
EXIST GV$METRICNAME SYNONYM
EXIST GV$METRICGROUP SYNONYM
NOT EXIST ABC

View 4 Replies View Related

SQL & PL/SQL :: Display All Values In Same Order As In IN List

Mar 29, 2010

I had a table main with 2 fields. please see the contents below.

seq name
a aaa
b bbb
c ccc
d ddd

1. My query is as below

select seq,name from main where seq in ('a','b','c','b','d','d') output of query is

a aaa
b bbb
c ccc
d ddd

but I need the output to be as shown below.

a aaa
b bbb
c ccc
b bbb
d ddd
d ddd

I need to display all the values in the same order as in the "IN" list.

View 18 Replies View Related

SQL & PL/SQL :: Procedure To Display Data From Drop Down List

Feb 6, 2012

I have to write a proc to display data based on user input for t_grp, and it should exclude grp0

So below is the proc I have written, and I want to display the report for all t_grp when user gives user_input as All or gives null. How can I acheieve this is in procedure.

table: target_data

create table target_data
(
t_mon varcahr2(6)
, t_grp varchar2(30)
, t_data varchar2(50)
)
;
INSERT INTO target_data VALUES ('Apr-11', 'grp1', 'some_data');
INSERT INTO target_data VALUES ('Apr-11', 'grp2', 'some_data');
INSERT INTO target_data VALUES ('Apr-11', 'grp4', 'some_data');
[code]......

Now the above proc is running fine, if I give the input as grp1 or any other t_grp.

View 2 Replies View Related

Application Express :: Get Display Value From Select List

Sep 9, 2012

I am using Apex 4.1 and I have a select list in my page. I know i can access the value of the selected item by :P1_SelectList, but I would like to know how to access the display value of the selected item.

View 6 Replies View Related

Application Express :: Display Value Into List Manager Box

Feb 15, 2013

i am using List Manager Item .i have use List of values (lov)

SELECT LD.LOOKUP_NAME d ,LD.ID r FROM CRM_LOOKUP_MAS LM,
CRM_LOOKUP_DTL LD
WHERE LM.LOOKUP_ID=LD.LOOKUP_ID
AND LM.LOOKUP_TYPE='TRAVEL RHYTHM' and LD.ACTIVE_FLAG='Y'
order by LD.LOOKUP_NAME
}

when i click to add new value and click then pop up window open with LD.LOOKUP_NAME display value but when i click on add Button then value added in to below box with LD.ID

i want to display here my LD.LOOKUP_NAME value into below box.

How can i do this.

View 0 Replies View Related

Display A List Of All Book Titles Along With Their Percentage Of Markup?

Jul 14, 2013

I've gotten it to display the percentage of markup correctly(as in, multiplied by 100 with no decimal places), but I cannot for the life of me get the percentage sign to display on the right of the values(i.e. 40, but not 40%).

Here's my code so far:

SELECT title, to_char(round((retail-cost)/retail*100), '99')
AS "MARKUP %" FROM BOOKS;

I've tried concatenating with both the + sign and the ampersand (&) sign, to no avail.

View 1 Replies View Related

Application Express :: Select List Read Only / Or Display Only

Sep 3, 2012

I have Select list itemS based on Lookup tables. When the form in Edit mode, I want to prevent the end user from changing the value of these Select list items. How can I accomplish this ??? **If I change the type of the item to Display only, then it will show the returned value of the Select list.

View 1 Replies View Related

Application Express :: Display Quarter Of Year As Select List

Jul 16, 2012

I need to display quarter of current year in select list.

for example current year is 2012 so, i want Jan-Mar 2012,Apr-Jun 2012,July-Sept 2012,Oct-Dec 2012 in select list.

View 3 Replies View Related

Express Edition (XE) :: Display A List Of All User And Admin Accounts

Jan 28, 2013

I've installed XE and I want to know what accounts are active. If I log in to SQL*Plus as sys as SYSDBA how can I display a list of all user and admin accounts? I want to know who can log in.

View 3 Replies View Related

Application Express :: Display List Of All Users On Page / Accessing Or Using Application

Mar 23, 2013

i want to display a list of all users on a page those currently accessing or using the application. How do i accomplish this requirement?

View 1 Replies View Related

SQL & PL/SQL :: Sorting Hierarchy Query

Jun 10, 2013

Is it possible to sort records based on hierarchy in such a way that records sorts in sequence and records of same level comes in the last (in sorting order)

Eg: Default hierarchy level order: 0,1,2,2,3,3,3,4,5

Above given is the default sorting order of an query for an parent 'NODE_A1' having multiple same level i.e 2 and 3

Expected Hierarchy level order: 0,1,2,3,4,5,2,3,3

Please check attached file for an example.

View 8 Replies View Related

SQL & PL/SQL :: Parent-Child Hierarchy?

Dec 20, 2012

way to achieve the below logic.

SQL to pick up parent child relationship within same table with a certain logic.

Example:
mod_product_numberProduct_Hierarchy
H555888 PH05678
H888987 H555888
H8889 H555888

[code]...

Example: I expect the rows with H8889,H9955 & P6666 & P5555 to be sub-category values value for product hierarchy H555888.

If there are rows with H8888987 as Product_hierarchy, we will pull up those rows too for product hierarchy H555888. The extra condition is we drill down only on 7 character mod_prod_number not on 5 character mod_prod_number. We pull out all sub category mod_prod_number for all distinct Product hierarchy.

View 11 Replies View Related

SQL & PL/SQL :: Hierarchy Record Manipulation?

Jun 13, 2011

Ihave the following result set based on a hiearchy query

SELECT LEVEL ,
BM.ITEM item
BM.Part part
connect_by_isleaf leaf
FROM item_part_tab bm
START WITH bm.item_id='002PH'
CONNECT BY BM.ITEM_ID = prior BM.PART_id

LEVEL ITEM PART LEAF
-----------------------------------------------------------------
1 002IT XPART1 0
2 XPART1 SUBPART1 1

In the above resultset PART='SUBPART1' does'nt have any child as evident from the connect_by_isleaf (LEAF=1) .Is there any way to create a pseudo record if such values are returned by the query, i.e., if a part has no tree under it a dummy record should be created i.e. in this case an additional record should be returned by this query. i.e., as below. the LEVEL=3 record should be created.

LEVEL ITEM PART LEAF
---------------------------------------------------------
1 002IT XPART1 0
2 XPART1SUBPART1 1
3 SUBPART1 1

View 16 Replies View Related

PL/SQL :: Get Only Last Level In SQL Hierarchy Query

Jun 4, 2013

How to get only the last level in Oracle SQL Hierarchy Query?

View 2 Replies View Related

SQL & PL/SQL :: Find Locked Tables In Hierarchy

Feb 1, 2012

Is it possible to find the locked objects in hierarchical order. Consider the below example

--Connect to scott schema
create table block_session(a NUMBER, b VARCHAR2(100))
/
insert into block_session select rownum,rownum*10 from dual connect by level<=10
/
COMMIT
/
GRANT ALL ON block_session to HR
/
update scott.block_session
SET a=10
where b=10
[code]......

Is it possible to get all locked table details in hierarchical order?

View 4 Replies View Related

SQL & PL/SQL :: Removing Duplicate In Hierarchy Level

Jul 18, 2013

I have requirement to suppress the duplicate nodes on same level in hierarchy query.
Below given is the script for it.

CREATE TABLE NODE_LVL (PARENT_NODE VARCHAR2(100), CHILD_NODE VARCHAR2(100));

INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AFL');
INSERT INTO NODE_LVL VALUES('TBL_APP','TBL_ACS');
INSERT INTO NODE_LVL VALUES('TBL_ADD','TBL_ADW');
INSERT INTO NODE_LVL VALUES('TBL_ADP','TBL_ADV');
INSERT INTO NODE_LVL VALUES('TBL_AOP','TBL_AOV');
[code]......

Table 'TBL_APP' is having 2 parent nodes i.e 'TBL_AOV' and 'TBL_ADV'
SELECT * FROM node_lvl WHERE child_node = 'TBL_APP';

At level 5 there is duplicate nodes i.e 'TBL_APP' and 'TBL_ACS' as parent_node and child_node respectively.

SELECT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;

I want to suppress such duplicates. So I added DISTINCT

SELECT DISTINCT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;

BUT requirement is to maintain the same order (of hierarchy) as it was before adding DISTINCT.

View 11 Replies View Related

SQL & PL/SQL :: Creating Materialized View For Getting Hierarchy?

Oct 3, 2013

I want to create a materialized view for the with same logic as below view

CREATE OR REPLACE FORCE VIEW GFL_MCP_CORE.VW_CLIENT_HIERARCHY
(KEY_INDENTED, CLIENT_LEVEL, ID, NAME, CLIENT_TYPE_CODE,
L2_CLIENT_ID, L2_CLIENT_NAME, TOP_CLIENT_ID, TOP_CLIENT_NAME, CLIENT_MCP_FLAG,
CLIENT_GROUP_NAME, ORG_REGION_NAME, COUNTRY_NAME, PROCESSOR_CLIENT_CODE)

[code]....

Here is the sample data for the client table and other tables are generic.

Row#IDNAMECOUNTRY_CODECLIENT_TYPE_CODECLIENT_GROUP_IDPARENT_CLIENT_IDSTATUS_FLAG
134801ZhuanshenguangchangCNBR10048232909A
212515229ConsolidatedFCUUSCL54812515A
314821286 Golden Mile BranchCABR53112712A
42050014513Bay CityUSBR56612009A
53864712-44-125 LeicesterGBBR56038630A
63869114736 Freestar BrickyardUSBR10020238686A
731141Walsall StoreGBL254631103A

View 1 Replies View Related

SQL & PL/SQL :: Hierarchy Query And Tree Structure

Jun 26, 2012

How to create one complex query. here what I have in my table.

DELIVERABLESPRECURSORS
------------ -----------
4 15
15 332
15 26
26 332
29 4
29 15
224 26
224 274
224 259
259 29
274 15
288 259
288 26
288 274
916 27
917 27
918 27

I need a query which run against this table and bring me the following result.

4 > 15 > 332
15 > 332
15 > 26 > 332
26 > 332
29 > 4 > 15 > 332
..... and so on

In theory, I need query which will track DELIVERABLES'S PRECURSOR and PRECURSOR's of there PRECURSOR'S.

View 16 Replies View Related

SQL & PL/SQL :: To Build A Hierarchy And Output It Into A Sub-report

Feb 26, 2010

I want to build a hierarchy and output it into a sub-report. Since the data in few hierarchy fields exceeds 4000 characters and therefore m getting the ORA-01489 error and the entire report was failing.

So i have used user aggregate function to print the out put. I have also used row_number() to print sequence in query output.

the query that i am using is

SELECT pr_id,
REPLACE (REPLACE (twadmin.STRAGG2(distinct RPAD (to_number(curr), 2, ' ') || ')' || NVL (DECODE (medical, NULL, clinical, medical), 'UNKNOWN') || '###' ||
RPAD (' Causality as per rport ', 50, ' ') || ': ' || reporter || '###' ||
RPAD (' Causality as per report2 ', 50, ' ') || ': ' || hospital || '###' ||
RPAD (' Dechallenge', 50, ' ') || ': '|| dechallenge || '###' ||
RPAD (' Rechallenge', 50, ' ') || ': '|| rechallenge), ',', CHR(10)), '###', CHR (10)) causality
[code]......

the output of the query is

ID Causality
-- ------
3755101 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
2 )Mental status changes (Mental status changes)
[code].......

but the expected out put is

ID Causality
-- ------
3755101 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
[code]........

but it should be as

1)
2)
3)
..
..
..
10)
11)
12) for the values in the causality field

View 25 Replies View Related

JDeveloper, Java & XML :: Recursive Hierarchy Query To XML?

Mar 5, 2013

To use dynatree (URL] I want the result to be in the xml form.but the result is not what I want.

SELECT
XMLELEMENT("div",xmlattributes('tree' AS "id"),
(SELECT DBMS_XMLGEN.getXMLType(
DBMS_XMLGEN.newContextFromHierarchy('
SELECT LEVEL,
case
when CONNECT_BY_ISLEAF = 0 then

[code]....

View 6 Replies View Related

Forms :: Tree Hierarchy - Not Working Properly?

Apr 8, 2009

i have created a tree hierarchy....when the user clicks on any node,that particular form will open....But there is some problem...i click on form A,it opens.,...then i exit that form via a exit button on the toolbar....and then I click on form B on the tree....but first the form A only opens...and then again i exit form A, then form B will open..

i have written the following code in when_tree_node_selected trigger :

if condition then
call_form(path,no_hide,do_replace);

View 3 Replies View Related

SQL & PL/SQL :: Oracle 10.2.0.4 On Solaris 10 - Recursive Query Using Hierarchy

Nov 9, 2011

We are on oracle 10.2.0.4 on solaris 10. My question is on a sql query. Is it possible to rewrite a query to avoid the connect by and prior constructs and use joins? For example i have the following query:

SELECT empno,
ename,
job,
mgr,
hiredate
FROM emp
START WITH empno in (7566,7698)
CONNECT BY PRIOR empno = mgr

How can it be rewritten using a two table join (self join)? I am not sure if it can be done and whether it is possible.

View 4 Replies View Related

SQL & PL/SQL :: Passing Hierarchy As Input To Function That Returns XML?

Aug 3, 2012

I have an requirement to create an function which takes table or hierarchy of tables as input and returns xml output in hierarchy. Below given is the Tables hierarchy.

AAAA
----AAA
----BBB
----CCC
-------CC1
-------CC2
-------CC3

[code]....

Requirement: Initially input was table name and using table as the root node output should generate xml of all the records of child tables.But now requirement is to give the flexibility to user to select what hierarchy he needs i.e he may select AAAA, CCC and in the nodes C1,C2,C3 and C4 if he doesn't want C3 then that node should not be shown in output.

I have created Hierarchy table having 3 columns SI.No, ParentNode and ChildNode and entered the above hierarchy relation.

1. What is the best to way (design)to pass input parameter for the function.

2. How to generate hierarchy in xml using DBMS_XMLGEN

View 12 Replies View Related

SQL & PL/SQL :: Find All Data Items In Hierarchy Recursion?

Jan 21, 2003

I need to find all "report to" supervisors in a hierarchical chain. A binary search won't work, as each level may contain > 2 supervisors. Example:

1

2 3 4

5 6 7 8 9

10

10 reports to 9
9 reports to 4
5 and 6 report to 2
7 and 8 report to 3
2, 3 and 4 report to 1

Result for all reporting to 1 would be a table containing 2, 3, 4, 5, 6, 7, 8, 9, 10 (order is unimportant!).

I think I need to use recursion. I can do this in C; need code for PL/SQL??? (I can't get the "tree" to display well!)

View 4 Replies View Related

PL/SQL :: How To Query A Hierarchy Structure (Manager-Employee)

Apr 29, 2013

I am using Oracle database 10g R2.

suppose I have this structure

EMP_ID               EMP_NAME          MANAGER_ID
1                    KING               
2                    STEVE               1
3                    FRANK               2

What I want is when I query for any EMP_ID, I want to get all his manager first, and the last record is the employee in leaf of the hierarchy in the query.

For example, when I query for EMP_ID=2 ,I want to get this result

EMP_ID               EMP_NAME          MANAGER_ID
1                    KING               
2                    STEVE               1

and when I query for EMP_ID=3 ,I want to get this result:

EMP_ID               EMP_NAME          MANAGER_ID
1                    KING               
2                    STEVE               1
3                    FRANK               2

How to write this query?

View 3 Replies View Related

SQL & PL/SQL :: Hierarchy Query - Calculate Total Miles

Jul 30, 2013

I've below tables.

create table tst_link (parent_pid number(5),
child_pid number(5)
);

insert into tst_link (1, 2);

[Code]....

For each parent pid i need to calculate total miles (including the child_pid also).

Required Output

PARENT_PID TOTAL_MILES
----------- ------------
1 48
4 100
8 125

View 4 Replies View Related

RAC & Failsafe :: Hierarchy To Stop And Start Oracle RAC Instance

Apr 20, 2013

What is the hierarchy to stop and start oracle RAC instance ?

View 10 Replies View Related

SQL & PL/SQL :: View Of Item Hierarchy By Eliminating NULL Levels

Aug 22, 2013

Here is the table structure I will be speaking of below:

SELECT lvl_1_id, lvl_1_dsc,
lvl_2_id, lvl_2_dsc,
lvl_3_id, lvl_3_dsc,
lvl_4_id, lvl_4_dsc,
lvl_5_id, lvl_5_dsc,
lvl_6_id, lvl_6_dsc,
lvl_7_id, lvl_7_dsc,
lvl_8_id, lvl_8_dsc
FROM item_hier_tbl;

I have a table that mimics an hierarchy of items (parent-child relationship). The top level (level 1) never has a NULL value, but it is possible for any of the lower values to have a NULL value. For example, levels 1, 2, 5, 7, & 8 may have values, but 3, 4, & 6 may all be NULL.

I need a View that can return the same data, but with out NULL values. The View would move the NOT NULL values up the levels, so there is consecutive data on each level starting at level. So using the example, the View would move value in 5 up to 3, 7 up to 4, and 8 up to 5. Then level 6 to 8 would have NULL.

I have done this already by creating a 2nd table and using a stored proc to copy the data over to the new table they I want it. But I was told a View or Materialized view "may" work, and perform quicker during the queries. I looked at the Oracle functions LAG and LEAD, but by definition they work by rows, I need to work by columns on the same row.

LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

Is what I am asking possible in a View or Materialized View?

View 2 Replies View Related

Forms :: How To Avoid (no List Elements Defined For List Item) Error

Sep 27, 2011

I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".

I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved