Home » SQL & PL/SQL » SQL & PL/SQL » Tree View Query
Tree View Query [message #399015] Mon, 20 April 2009 05:14 Go to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Dear All


I have one table item2

which contain 3 column

catagory_no
Catagory_name
item_no
item_name

i need Tree view querry to show my tree like that

Niddle
DB*1 #18
DB*2 #19

niddle is Catagory name

and db*1 #18 and 19 is item_name

the code of needle is 1

and item_name code db*1 #18 =1000 and db*2 #19=2000


please help


Thanks


Shahzaib ismail
Re: Tree View Query [message #399041 is a reply to message #399015] Mon, 20 April 2009 06:13 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Problem Solve with that query


SELECT 1, LEVEL, ITEM_NAME , '',TO_CHAR(item_NO) FROM ITEM2 CONNECT BY PRIOR item_no = CATAGORY_NO START WITH item_no in (1,2,3,4,5,6,7,8,9,10)


Shahzaib ismail
Re: Tree View Query [message #399043 is a reply to message #399041] Mon, 20 April 2009 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Re: Tree View Query [message #399049 is a reply to message #399015] Mon, 20 April 2009 06:24 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Thanks for your information


Now i have another query i need item_name and also item_no in
tree view how can i execute item_name and item_no in tree view

please explain


Thanks


Shahzaib
Re: Tree View Query [message #399050 is a reply to message #399049] Mon, 20 April 2009 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Tree View Query [message #399105 is a reply to message #399015] Mon, 20 April 2009 11:08 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Problem not Solve unable to understand your feedback


Shahzaib ismail
Re: Tree View Query [message #399106 is a reply to message #399015] Mon, 20 April 2009 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Tree View Query [message #399168 is a reply to message #399015] Tue, 21 April 2009 00:23 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
I CREATE TABLE

CREATE TABLE ITEM2
(ITEM_NO NUMBER(10),
ITEM_NAME VARCHAR2(20))

INSERT INTO ITEM2
VALUES
('1','THREAD')

/


INSERT INTO ITEM2
VALUES
('2','NEEDLE')

/

INSERT INTO ITEM2
VALUES
('1000','THREAD 50/3 BROWN')

/

INSERT INTO ITEM2
VALUES
('2000','DB*1 #18 NEEDLE')

/


NOW I NEED THE TREE WHICH LOOK LIKE THIS

THREAD (01)
THREAD 50/3 BROWN (1000)

NEEDLE (02)
DB*1 #18 NEEDLE (2000)


I WRITE THIS QUERRY

SELECT 1, LEVEL, ITEM_NAME , TO_CHAR(item_NO) 
FROM ITEM2 
CONNECT BY PRIOR item_no = CATAGORY_NO START WITH item_no in (1,2,3,4,5,6,7,8,9,10)


ITS GIVE ME RESULT LIKE THIS

THREAD
THREAD 50/3 BROWN

NEEDLE
DB*1 # 18 NEEDLE


BUT I NEED THE ITEM_NO AFTER ITEM_NAME

HOPE YOU GUYS UNDERSTAND AND WILL HELP ME


THANKS


SHAHZAIB ISMAIL

[Updated on: Tue, 21 April 2009 00:35] by Moderator

Report message to a moderator

Re: Tree View Query [message #399169 is a reply to message #399015] Tue, 21 April 2009 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Tree View Query [message #399172 is a reply to message #399015] Tue, 21 April 2009 00:33 Go to previous messageGo to next message
irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan
Senior Member

SELECT 1, LEVEL, ITEM_NAME , '',TO_CHAR(item_NO) FROM ITEM2 CONNECT BY PRIOR item_no = CATAGORY_NO
START WITH item_no in (1,2,3,4,5,6,7,8,9,10)

I can not understand this query i want to explain any one what does this query deos.
connected by used for what purpose.
start with what deos this.
thanks
Re: Tree View Query [message #399175 is a reply to message #399015] Tue, 21 April 2009 00:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SQL produces results set.
You ask the question; you get the answer.
Re: Tree View Query [message #399176 is a reply to message #399168] Tue, 21 April 2009 00:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
shahzaib_4vip@hotmail.com wrote on Tue, 21 April 2009 07:23
I WRITE THIS QUERRY

SELECT 1, LEVEL, ITEM_NAME , TO_CHAR(item_NO) 
FROM ITEM2 
CONNECT BY PRIOR item_no = CATAGORY_NO START WITH item_no in (1,2,3,4,5,6,7,8,9,10)


ITS GIVE ME RESULT LIKE THIS

THREAD
THREAD 50/3 BROWN

NEEDLE
DB*1 # 18 NEEDLE


Impossible. You faked your output.
And stop posting in all-caps. Netiquette defines it as shouting and it is considered to be rude.
Re: Tree View Query [message #399177 is a reply to message #399168] Tue, 21 April 2009 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 20 April 2009 13:16
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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

In addition, don't post in UPPER case and post a valid test case.

Read documentation: SQL Reference, Hierarchical Queries

Regards
Michel
Re: Tree View Query [message #399256 is a reply to message #399015] Tue, 21 April 2009 05:45 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
@ frenk

i do apologies for wrong data provide but its a mistake please find the attachment of dmp file and also fmb file i hope you will provide me information that how to create tree with item_no and item_name as i mansion earlier


Thanks

Shahzaib ismail
  • Attachment: tree.fmb
    (Size: 44.00KB, Downloaded 158 times)
Re: Tree View Query [message #399258 is a reply to message #399256] Tue, 21 April 2009 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't download file.
I can't read fmb file.

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

Regards
Michel
Re: Tree View Query [message #399260 is a reply to message #399015] Tue, 21 April 2009 05:52 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
please import temp.dmp in your database which is 1 table

create table item2
(item_no number(10),
item_name varchar2(20),
catagory_no number(10))

/

INSERT INTO ITEM2
(ITEM_NO,ITEM_NAME)
VALUES
('1','NEEDLE')

/

INSERT INTO ITEM2
(ITEM_NO,ITEM_NAME)
VALUES
('2','NEEDLE')

/


insert into item2
VALUES
('1000','THREAD 20/3 BROWN','1')

/
INERT INTO ITEM2
VALUES
('2000','DB*1#18 NEEDLE','2')

/


THE DMP FILE IS NOT SUPPORT THATS WHY I WRITE ALL INSERT DATA AND TABLE



SHAHZAIB
Re: Tree View Query [message #399261 is a reply to message #399260] Tue, 21 April 2009 05:55 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 21 April 2009 07:38
Michel Cadot wrote on Mon, 20 April 2009 13:16
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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

In addition, don't post in UPPER case and post a valid test case.

Read documentation: SQL Reference, Hierarchical Queries

Regards
Michel


Use SQL*Plus and post what you already tried.
Previous Topic: Oracle 9i issue
Next Topic: Error when stored proc called from vb.net
Goto Forum:
  


Current Time: Sat Dec 10 14:35:35 CST 2016

Total time taken to generate the page: 0.05241 seconds