Home » SQL & PL/SQL » SQL & PL/SQL » Hierachical query based on column value (Oracle 9.2.0,Solaris 5.8)
Hierachical query based on column value [message #387886] Sat, 21 February 2009 07:12 Go to next message
manoj339
Messages: 9
Registered: April 2007
Junior Member
I have a table X with unique account nos and branch code with other columns also with amts.
I have a table Y which defines structure of accounts in the table for a branch

Acct A in X can be a H or S in strc column of table Y.Also header_acct_no is present in table Y and populated for rows with strc = 'S' otheriwse null for H.

I want to join X and Y and get all unique acct nos from Y
for a particular acct in X.
i.e if acct no in X is head acct ,get head acct and sub acct from Y similarly if acct no in Y is head acct ,get head acct & sub acct from Y
The final o/p of join should not include duplicates acct present in X assuming that some sub accts or head acct may be present in X.

X
---
acct_no
br_cod
amt1

Y
---
acct_no
br_cod
head_acct_no
strc

Re: Hierachical query based on column value [message #387887 is a reply to message #387886] Sat, 21 February 2009 09:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
Re: Hierachical query based on column value [message #387890 is a reply to message #387886] Sat, 21 February 2009 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previouos topic:
Michel Cadot wrote on Mon, 02 February 2009 10:10
From your previous post:
Michel Cadot wrote on Wed, 13 August 2008 13:31
For your next question, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel


Post DDL for your tables.

Regards
Michel

Post a Test case: create table and insert statements along with the result you want with these data.

Re: Hierachical query based on column value [message #387953 is a reply to message #387886] Sun, 22 February 2009 09:15 Go to previous messageGo to next message
manoj339
Messages: 9
Registered: April 2007
Junior Member
2 tables A5 and A6 structure and insert statements are mentioned below :

CREATE TABLE A5
(
  ACCT_NO         VARCHAR2(10),
  HEADER_ACCT_NO  VARCHAR2(10),
  BRANCH          VARCHAR2(3)
)

Insert into a5
   (ACCT_NO, HEADER_ACCT_NO, BRANCH)
 Values
   ('1', '4', '300');
Insert into a5
   (ACCT_NO, HEADER_ACCT_NO, BRANCH)
 Values
   ('3', '4', '300');
Insert into a5
   (ACCT_NO, BRANCH)
 Values
   ('101', '500');
Insert into a5
   (ACCT_NO, BRANCH)
 Values
   ('102', '500');
Insert into a5
   (ACCT_NO, BRANCH)
 Values
   ('103', '500');
Insert into a5
   (ACCT_NO, HEADER_ACCT_NO, BRANCH)
 Values
   ('10', '10', '300');
COMMIT;


CREATE TABLE A6
(
  ACCT_NO         VARCHAR2(10),
  HEADER_ACCT_NO  VARCHAR2(10),
  NODE_TYP        VARCHAR2(1),
  branch          varchar2(3) 
)


Insert into a6
   (ACCT_NO, HEADER_ACCT_NO, NODE_TYP, BRANCH)
 Values
   ('1', '4', 'S', '300');
Insert into a6
   (ACCT_NO, HEADER_ACCT_NO, NODE_TYP, BRANCH)
 Values
   ('2', '4', 'S', '300');
Insert into a6
   (ACCT_NO, HEADER_ACCT_NO, NODE_TYP, BRANCH)
 Values
   ('3', '4', 'S', '300');
Insert into a6
   (ACCT_NO, NODE_TYP, BRANCH)
 Values
   ('4', 'H', '300');
Insert into a6
   (ACCT_NO, HEADER_ACCT_NO, NODE_TYP, BRANCH)
 Values
   ('5', '4', 'H', '300');
Insert into a6
   (ACCT_NO, NODE_TYP, BRANCH)
 Values
   ('6', 'H', '300');
Insert into a6
   (ACCT_NO, HEADER_ACCT_NO, NODE_TYP, BRANCH)
 Values
   ('7', '10', 'S', '300');
Insert into a6
   (ACCT_NO, NODE_TYP, BRANCH)
 Values
   ('10', 'H', '300');
Insert into a6
   (ACCT_NO, HEADER_ACCT_NO, NODE_TYP, BRANCH)
 Values
   ('11', '10', 'S', '300');
COMMIT;



I want the query to get the Desired result
probably using outer join on a5 and a6.

acct_no  header_acct_no br_cod
1             4           300
2             4           300
3             4           300  
4             4           300
5             4           300 
7            10           300
10           10           300 
11           10           300 
101                       500 
102                       500 
103                       500



[mod-edit: added code tags; next time please add them yourself]

[Updated on: Sun, 22 February 2009 14:44] by Moderator

Report message to a moderator

Re: Hierachical query based on column value [message #387955 is a reply to message #387953] Sun, 22 February 2009 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted.

Regards
Michel
Re: Hierachical query based on column value [message #387976 is a reply to message #387953] Sun, 22 February 2009 14:57 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
It looks like what you need is UNION.
Previous Topic: ORA-00942
Next Topic: Function for getting age in Years / Months / Days (merged)
Goto Forum:
  


Current Time: Fri Dec 09 03:51:04 CST 2016

Total time taken to generate the page: 0.15435 seconds