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.
How to get only the last level in Oracle SQL Hierarchy Query?
View 2 Replies View RelatedHow 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.
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]....
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.
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?
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
I have the following query
SQL>
SQL> SELECT DECODE(act.statement_flag, NULL, 'Total',act.statement_flag) "Monthly Statement?",
2 COUNT(account_id) "Count",
3 ROUND(((COUNT(account_id)/
(SELECT COUNT(account_id) FROM accounts
WHERE account_status_id = consts$.acct_sts_active))* 100),2) || '%' "Percent of Active Accounts"
4 FROM accounts act
5 WHERE act.account_status_id = consts$.ACCT_STS_active
6 GROUP BY ROLLUP(act.statement_flag)
SQL> /
Monthly Statement? Count Percent of Active Accounts
------------------ ---------- -----------------------------------------
N 1892 98.08%
Y 37 1.92%
Total 1929 100%
I want the output to be in the following fashion:
Monthly Statement? Count Percent of Active Accounts
------------------ ---------- -----------------------------------------
Y 37 1.92%
N 1892 98.08%
Total 1929
And I would also like to know is there another way to find the Percent of Active accounts in the query.
I got the following data for which I will like to sort in ascending order.
B51-01-008/04022013/CRE1005/1
B51-01-008/04022013/CRE1005/2
B51-01-008/15-1-13/SSA6280/1
B51-01-008/31012013/CRE1005/1
B51-01-008/31012013/CRE1005/10
B51-01-008/31012013/CRE1005/2
and expected result will be
B51-01-008/15-1-13/SSA6280/1
B51-01-008/31012013/CRE1005/1
B51-01-008/31012013/CRE1005/2
B51-01-008/31012013/CRE1005/10
B51-01-008/04022013/CRE1005/1
B51-01-008/04022013/CRE1005/2
I will like to sort it based on the order on second segment for which is date, the earlier appear first then only the rest.
The db field is a string-type field that hold strings such as:
'1234'
'753'
'textstring'
'345'
Obviously, if you sort it, it'll be stored as a string such as:
'1234'
'345'
'753'
'textstring'
My client wants it so the numbers sort as integers, followed by string-like strings (sorted alphabetically), so it's like.
'345'
'753'
'1234'
'textstring'
Is there a quick and dirty SQL-only way to doing this in Oracle?
sort data set A,B,1,2,A1,A2,B1,B2,2B,1000 as A,B,1,2,1000,A1,A2,B1,B2,2B.
I tried with LPAD and EXPREG_REPLACE funtion. But it did not work.
I am trying to sort columns base on cluster and alpha numeric field.
Column1 Columns2 column3
1 2-CA6R-234 9
1 2-CA6R-231 8
1 ARCT-0037000000ewegZ 10
2 2-QIZFF7 1
3 2-PIZFF6 6
3 ARCT-0037000000ewipk 9
3 2-QIZTF7 1
Wanted to sort in a way that column1 will be same order and the second column will order first with ARCT-XXXXX and then reset of the column2. It should look like this
Column1 Columns2 column3
1 ARCT-0037000000ewegZ 10
1 2-CA6R-231 8
1 2-CA6R-234 9
2 2-QIZFF7 1
3 ARCT-0037000000ewipk 9
3 2-PIZFF6 6
3 2-QIZTF7 1
I'm trying to sort a collection in a nested table in PL/SQL so these value can be used later for a display and for export to Excel. The sort is failing.
-- PLS-00642: local collection not allowed in SQL statements
-- PL/SQl: ORA-00902: invalid datatype
The error message are also noted below in the code on the line that fails.
A quick overview of this code- Using a nested table MyNestedTable the values from several select queries are combined into MyRecordsetZero using CURSOR, and MULTISET UNION. I'm trying to either sort MyRecordsetZero or populate MyRecordsetSorted with the sorted values for futher use.
IMPORTANT: The code is running in an enviroment that does not have permission to create.
Versions:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Developer: 8.0.0.1480
DECLARE
-- Declare the variable MyTID to be used by select statements and
-- set its data type and max character count
MyTID varchar2(10);
[code]....
In test.pks file I declared the following type
TYPE tab_tests is table of NUMBER(15);
In test.pkb, I have the following procedure
PROCEDURE report (
i_cid IN NUMBER
)
IS
test1 tab_tests := tab_tests();
test2 tab_tests;
BEGIN
-- I populate test1 with the data and it works fine. But when I tried to make a sorted nested table with the following command.
I got this error 'ORA-00902: invalid datatype',
a nested table than the following, like a built in Oracle collection method?
execute immediate 'select cast( multiset (select * from table(test1) order by 1) as tab_tests) INTO test2 FROM dual';
END;
I have report where there are multiple columns and requirement is user may choose his own sorting order from form, any sample .rdf.
CREATE TABLE EMP (EMP_CODE VARCHAR2(12),EMP_NAME VARCHAR2(20),SAL NUMBER);
INSERT INTO EMP VALUES ('1','A',10);
INSERT INTO EMP VALUES ('2','C',3);
INSERT INTO EMP VALUES ('3','B',20);
[Code]..
--note order by is changing, how its doable in report 6i.
1) eno list values ASC , DESC
priority Column : 2
2) DNO List Values ASC , DESC
priority column : 1
3) DTNO List Values ASC , DESC
priority column : 3
So i need the sorting order output like this
LIST VALUES ASC (OR) DESC USER SELECT AT RUN TIME ANY VALUE.
ORDER BY DNO DESC , ENO ASC , DTNO ASC
* The Priority is user will enter at run time based on the number the column should be sorting.
I have three SQLS which I am running using "Union"
e.g
select col1,count(*) from table where type ='parts'
group by col1
union
select col1,count(*) from table where type ='consumables'
group by col1
union
select 'Total',count(*) from table
group by 'Total'
I want to force the total column to be the last row of the select and the parts column to be the first row always. I did try something like prefixing the col1 with an alphabet. But that shows up in the output also. Was looking for a better way!
I have two list items and from left list item values are populated to right list item through Add and Remove buttons and vice versa.My requirement is
1.) I need to sort the values of list item whenever a new element is added to the list item.
I have a Group report based on one group.For eg..there is machine (Group) and it has detail records .The problem is there are certain specific machine on which i want sorting to be done the rest will be not sorted.
machine WX
Details not be sorted
machine sh4
Details to be sorted
machine sh5
Details to be sorted
machine AN1
Details not to be sorted
Jow can i sort field data getting from formula column. The filed that i want to sort have source of that formula column. When i use order by clause with :abc ---(formula column) then it doesn't not work.
View 3 Replies View RelatedI have a table in which I extract the year and the month and both in combination (from a date type). This is needed to summarize for example the year in a chart.
I need the combination of month and year because people could do a query which switches through years. For expample: From 02.2012 to 03.2013.My problem is I dont know how to tell anychart to sort the dates. I have already tried:
to_number(to_char(testdate,'YYYY')) as Year,
to_number(to_char(testdate,'YYYY'))||to_number(to_char(testdate,'MM')) as MonthyearApex now sorts:
20121,201212,20122
I would like that apex realizes to order:
1.2012,2.2012,3.2012 .........01.2013,02.2013.03.2013
The report itself has now four date relating collums:
1) Date (Type Date)
2) Year (Type Number)
3) Month (Type Number)
4) Monthyear (Type varchar)
Is there a way to short a Matrix report? The query shorts correctly in PLSQL but not in the generated report file.
There are columns like Date,Device,Operator Id etc and the report should be able to short depending on the requirement. For example,
it should be able to short by device, or by Date or both. I tried sorting in the 'Break order' but doesn't come out as required.
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?
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.
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
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?
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.
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
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
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);