Home » SQL & PL/SQL » SQL & PL/SQL » CHANGE THE LEVEL NUMBER IN AN SQL QUERY (merged)
CHANGE THE LEVEL NUMBER IN AN SQL QUERY (merged) [message #260355] Sun, 19 August 2007 01:11 Go to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi
I've this query:

SELECT LEVEL,LPAD(' ',(LEVEL+1)*5,' ')||ENAME ENAME
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR

the result is:
LEVEL ENAME
1 KING
2 JONES
3 SCOTT
2 FORD
3 SMITH

my question is that if i except FORD how can i change the level number to SMITH from 3 to 2.
for example:

SELECT LEVEL,LPAD(' ',(LEVEL+1)*5,' ')||ENAME ENAME
FROM EMP
WHERE ENAME <> 'FORD'
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO= MGR

the result is:
LEVEL ENAME
1 KING
2 JONES
3 SCOTT
2 SMITH

regards
nadia



Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY [message #260356 is a reply to message #260355] Sun, 19 August 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We already asked you in your previous topic to format your post.
You don't, I don't answer.
Too bad for you as I could help.

Regards
Michel
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY [message #260359 is a reply to message #260355] Sun, 19 August 2007 01:50 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Thanks for your replay anyway.
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY [message #260361 is a reply to message #260355] Sun, 19 August 2007 01:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Thanks for your replay anyway.
I understand that English is not your primary language.
For future reference "replay" is correct when there is a penalty in US/Amercian NFL Football & the team gets to re-play the down.
"REPLY" is what Michel did to nadia74 original post.
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY [message #260362 is a reply to message #260359] Sun, 19 August 2007 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just follow the rules and you'll have a better answer.

Regards
Michel
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY [message #260365 is a reply to message #260355] Sun, 19 August 2007 02:14 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
You are right English isn't my native language.
thanks for correcting
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY [message #260373 is a reply to message #260365] Sun, 19 August 2007 05:31 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

nadia74 wrote on Sun, 19 August 2007 13:14
You are right English isn't my native language.
thanks for correcting

thanks for correcting
Laughing Laughing Laughing

He/ She made a mistake that is corrected!!! Laughing Laughing Laughing
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY [message #260375 is a reply to message #260373] Sun, 19 August 2007 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Her greatest mistake is to not follow the rules.

Regards
Michel
hierarcheal query level value [message #260853 is a reply to message #260355] Tue, 21 August 2007 02:17 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi
Need help writing a query which will return the branch values AND the level when one leaf or more is excepted.

Sample Data:

table
id            parent_id
0
1             0
1A            1
1Aa           1A
1Aaa          1Aa
1Ab           1A
1B            1
1Bb           1B
2             0
2A            2
2Aa           2A
2B            2

The hierarchy looks like this:

	              tree                (level)
                	0                   (1)	  
                      /	   \       	    
                    1	     2              (2)         
                 /  |      /   \       
               1A   1B     2A   2B          (3)         
              /  |  |       |      
           1Aa  1Ab 1Bb   2Aa               (4)      
            |  
          1Aaa                              (5)


So, If I except one leaf "1A" for examle, his children "1Aa, 1Ab" will belong to their
grandfather " 1 ".
the hierarchy will look like this:
                      tree              (level)
                	 0                 (1)	  
                    /	      \       	    
                   1	      2            (2) 
               /  /    \    /   \       
           1Aa  Ab     1B  2A   2B         (3)         
           |           |   |          
         1Aaa        1Bb  2Aa              (4) 


Any ideas? Thanks!

[Updated on: Tue, 21 August 2007 03:19]

Report message to a moderator

Re: hierarcheal query level value [message #260873 is a reply to message #260853] Tue, 21 August 2007 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is your third question.
This is the third time I tell you:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

And if you want more help, post a test case (create table and insert statements).

Regards
Michel
Re: hierarcheal query level value [message #260884 is a reply to message #260873] Tue, 21 August 2007 03:21 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Thank you for you help.I,ve done what you told me.
Hope it will better. I,ve oracle 10g database.
regards
Re: hierarcheal query level value [message #260889 is a reply to message #260884] Tue, 21 August 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is better and it would be even better if you posted a test case.
I will merge this topic with your previous one as they are the same question.

Regards
Michel
Re: hierarcheal query level value [message #260893 is a reply to message #260853] Tue, 21 August 2007 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
create table t (id varchar2(5), pid varchar2(5));
insert into t values ('0',     '');
insert into t values ('1',     '0');
insert into t values ('1A',    '1');
insert into t values ('1Aa',   '1A');
insert into t values ('1Aaa',  '1Aa');
insert into t values ('1Ab',   '1A');
insert into t values ('1B',    '1');
insert into t values ('1Bb',   '1B');
insert into t values ('2',     '0');
insert into t values ('2A',    '2');
insert into t values ('2Aa',   '2A');
insert into t values ('2B',    '2');
commit;

SQL> select * from t;
ID    PID
----- -----
0
1     0
1A    1
1Aa   1A
1Aaa  1Aa
1Ab   1A
1B    1
1Bb   1B
2     0
2A    2
2Aa   2A
2B    2

12 rows selected.

SQL> col path format a30
SQL> select level, sys_connect_by_path(id, '/') path
  2  from t
  3  connect by prior id = pid
  4  start with pid is null
  5  /
     LEVEL PATH
---------- ------------------------------
         1 /0
         2 /0/1
         3 /0/1/1A
         4 /0/1/1A/1Aa
         5 /0/1/1A/1Aa/1Aaa
         4 /0/1/1A/1Ab
         3 /0/1/1B
         4 /0/1/1B/1Bb
         2 /0/2
         3 /0/2/2A
         4 /0/2/2A/2Aa
         3 /0/2/2B

12 rows selected.

SQL> var exclude varchar2(5);
SQL> exec :exclude := '1A'

PL/SQL procedure successfully completed.

SQL> select lvl-decode(instr(path,'/'||:exclude||'/'),0,0,1) "LEVEL",
  2         replace(path,'/'||:exclude||'/','/') path
  3  from ( select level lvl, sys_connect_by_path(id, '/') path
  4         from t
  5         connect by prior id = pid
  6         start with pid is null )
  7  where path not like '%/'||:exclude
  8  /
     LEVEL PATH
---------- ------------------------------
         1 /0
         2 /0/1
         3 /0/1/1Aa
         4 /0/1/1Aa/1Aaa
         3 /0/1/1Ab
         3 /0/1/1B
         4 /0/1/1B/1Bb
         2 /0/2
         3 /0/2/2A
         4 /0/2/2A/2Aa
         3 /0/2/2B

11 rows selected.

Regards
Michel
Re: hierarcheal query level value [message #260896 is a reply to message #260889] Tue, 21 August 2007 04:05 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
the test case will be on the EMP table:

CREATE TABLE EMP
(
  EMPNO NUMBER (4,0) NOT NULL,
  ENAME VARCHAR2 (10),
  MGR NUMBER (4,0),
  STATUS  CHAR(1),
  DEPTNO NUMBER (2,0) NOT NULL
);


INSERT INTO EMP (EMPNO, ENAME, MGR, DEPTNO) 
VALUES (1,KING, ,'Y',10);

INSERT INTO EMP (EMPNO, ENAME, MGR, DEPTNO)
VALUES (2,JONES,1,'Y',20);

INSERT INTO EMP (EMPNO, ENAME, MGR, DEPTNO)
VALUES (3,FORD,2,'N',20);

INSERT INTO EMP (EMPNO, ENAME, MGR, DEPTNO)
VALUES (4,ALAN,3,'Y',20);

INSERT INTO EMP (EMPNO, ENAME, MGR, DEPTNO)
VALUES (5,SMITH,3,'Y',20);

INSERT INTO EMP (EMPNO, ENAME, MGR, DEPTNO)
VALUES (6,SCOTT,2,'Y',20);

INSERT INTO EMP (EMPNO, ENAME, MGR, DEPTNO)
VALUES (7,ADAMS,6,'Y',20);


I want to exclude the employee whose status='N'
regards

[Updated on: Tue, 21 August 2007 04:12]

Report message to a moderator

Re: hierarcheal query level value [message #260905 is a reply to message #260896] Tue, 21 August 2007 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the same way that I show you in other example.

Regards
Michel
Re: hierarcheal query level value [message #260908 is a reply to message #260896] Tue, 21 August 2007 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your test case is wrong.
Quote:
VALUES (1,KING, ,'Y',10);

I don't think ", ," will work
Quote:
INSERT INTO EMP (EMPNO, ENAME, MGR, DEPTNO)
VALUES (2,JONES,1,'Y',20);

4 columns, 5 values, I don't think will work.
JONES is not an identifier.
...

Fix it.

Regards
Michel

Re: hierarcheal query level value [message #260916 is a reply to message #260908] Tue, 21 August 2007 04:45 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Thanks for your attention.
but i don't know where to put my condition (status='N') in the query.
regards
Re: hierarcheal query level value [message #260928 is a reply to message #260916] Tue, 21 August 2007 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Start with the basic connect query, see its output, put the condition in a where clause, see what happens...

Regards
Michel
Re: hierarcheal query level value [message #261048 is a reply to message #260916] Tue, 21 August 2007 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nadia,

Are you stuck or did you find?

Regards
Michel
Re: hierarcheal query level value [message #261167 is a reply to message #261048] Wed, 22 August 2007 01:35 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi
Unfortunately i didn't find it.
regards
Re: hierarcheal query level value [message #261179 is a reply to message #261167] Wed, 22 August 2007 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where are you stuck?
Post what you tried, we help you to go on.

Regards
Michel
Re: hierarcheal query level value [message #261191 is a reply to message #261179] Wed, 22 August 2007 02:11 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
I've tried to exclude the emp which status='N' from the path
and it didn't work also the level didn't match the required.


select lvl-decode(STATUS,'N',0,1) "LEVEL",
    decode(STATUS,'N', substr(path,1,b-a-1), path)  path1
 from ( select level lvl,sys_connect_by_path(ename, '/') path ,
 STATUS ,length(sys_connect_by_path(ename, '/')) b  , length(ename) a
         from emp
        connect by prior empno = mgr
        start with mgr is null )
 where STATUS <> 'N'


Thanks for your help.
regards
Re: hierarcheal query level value [message #261195 is a reply to message #261191] Wed, 22 August 2007 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
quote title=Michel Cadot wrote on Tue, 21 August 2007 12:02]Start with the basic connect query, see its output, put the condition in a where clause, see what happens...[/quote]
Start with the inner query, don't start from the whole solution I gave for the other problem.

Regards
Michel

[Updated on: Wed, 22 August 2007 02:18]

Report message to a moderator

Re: hierarcheal query level value [message #261204 is a reply to message #261195] Wed, 22 August 2007 02:53 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
The inner query:

select level lvl, sys_connect_by_path(ename, '/') path ,STATUS
from emp
where STATUS <> 'N'
connect by prior empno = mgr
start with mgr is null


the results would be:
lvl       path                   status
 1   /KING                         Y
 2   /KING/JONES                   Y
 3   /KING/JONES/SCOTT             Y
 4   /KING/JONES/SCOTT/ADAMS       Y
 4   /KING/JONES/FORD/SMITH        Y 


and if i try to reduce the level:

select level-decode(STATUS,'N',1,0) "LEVEL", 
sys_connect_by_path(ename, '/') path ,STATUS
from emp
where STATUS <> 'N'
connect by prior empno = mgr
start with mgr is null


nothing change
regards
Re: hierarcheal query level value [message #261209 is a reply to message #261204] Wed, 22 August 2007 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to decrease the level not if the current status is 'N' (which can't happen as you exclude these rows) but if there is a status 'N' in the path between root and current row.

Regards
Michel
Re: hierarcheal query level value [message #261229 is a reply to message #261209] Wed, 22 August 2007 03:21 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
I've done as you told me and it works:

select level-
DECODE(INSTR(sys_connect_by_path(STATUS||'-'||ename, '/'),'N-'),0,
0 ,1) "LEVEL"  ,sys_connect_by_path(ename, '/') path
,STATUS
from emp
where STATUS <> 'N'
connect by prior empno = mgr
start with mgr is null


but if there was more than on employee in the path have status='N' how can i count them.
regards
Re: hierarcheal query level value [message #261236 is a reply to message #261229] Wed, 22 August 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select length(translate('Y-Y-Y-N-Y-Y-N-Y','NY-','N')) nb_N from dual;
      NB_N
----------
         2

1 row selected.

You are really close now.

Regards
Michel

[Updated on: Wed, 22 August 2007 03:40]

Report message to a moderator

Re: hierarcheal query level value [message #261289 is a reply to message #261236] Wed, 22 August 2007 04:59 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
What would i do if there are a series of N.
for example:
Y-Y-N-N-N-N-Y-Y
REGARDS
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY (merged) [message #261325 is a reply to message #260355] Wed, 22 August 2007 05:48 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi
It works.You were a greate helpfull to me.
Thank you Michel
regards
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY (merged) [message #261347 is a reply to message #261325] Wed, 22 August 2007 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey, post your solution, it will be helpful for others.

Glad to have helped you.
Regards
Michel
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY (merged) [message #261356 is a reply to message #261347] Wed, 22 August 2007 06:43 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
The result was:

select LEVEL
       - DECODE(LENGTH(TRANSLATE(sys_connect_by_path(STATUS, '/'),'NY/','N')),
                NULL, 0, 
                LENGTH(TRANSLATE(sys_connect_by_path(STATUS, '/'),'NY/','N'))) "LVL",
       ename,
       STATUS
from emp
where STATUS <> 'N'
connect by prior empno = mgr
start with mgr is null
/

regards

[Updated on: Wed, 22 August 2007 06:49] by Moderator

Report message to a moderator

Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY (merged) [message #261360 is a reply to message #261356] Wed, 22 August 2007 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good job. You can use NVL instead of "decode(...,null,...)":
SQL> select level-nvl(length(translate(sys_connect_by_path(status,'/'),'NY/','N')),0) "LEVEL",
  2         ename
  3  from emp
  4  where status != 'N'
  5  connect by prior empno = mgr
  6  start with mgr is null
  7  /
     LEVEL ENAME
---------- ----------
         1 KING
         2 JONES
         3 ALAN
         3 SMITH
         3 SCOTT
         4 ADAMS

6 rows selected.

Regards
Michel
Re: CHANGE THE LEVEL NUMBER IN AN SQL QUERY (merged) [message #262191 is a reply to message #261360] Fri, 24 August 2007 23:31 Go to previous message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
Michel, this query for sure will provide the OP's expected result, but actually it's not a hierarchy.

to get the full hierarchy (of course, if needed) we can by:
SQL>  select t.*, (select empno from emp t2
  2                 where t2.status != 'N' and rownum=1
  3                connect by prior mgr=empno
  4                start with t2.empno=t.mgr) new_mgr
  5     from emp t
  6     where status !='N'
  7  /

EMPNO ENAME        MGR STATUS DEPTNO    NEW_MGR
----- ---------- ----- ------ ------ ----------
    1 KING             Y          10 
    2 JONES          1 Y          20          1
    4 ALAN           3 Y          20          2
    5 SMITH          3 Y          20          2
    6 SCOTT          2 Y          20          2
    7 ADAMS          6 Y          20          6

6 rows selected

[Updated on: Fri, 24 August 2007 23:34]

Report message to a moderator

Previous Topic: ORA-00979: not a GROUP BY expression
Next Topic: range from comma separated values
Goto Forum:
  


Current Time: Fri Dec 09 21:27:16 CST 2016

Total time taken to generate the page: 0.14670 seconds