0

I have a table X with columns a, seq_no, dept_no, status where column a contains values like "1,2,3,4,5,6". To change the row to column I have used the query as:

 SELECT DISTINCT TO_NUMBER(regexp_substr(a,'[^,]+',1,level)) numbers
   FROM x 
CONNECT BY TO_NUMBER(regexp_substr(a,'[^,]+',1,level)) IS NOT NULL;

Here I want to add condition like where dept_no is null and seq_no=1 and status='U'. How can I add the above mentioned condition.

Edit from comments:

I tried:

 SELECT DISTINCT TO_NUMBER(regexp_substr(a,'[^,]+',1,level)) numbers 
   FROM x 
  WHERE seq_no=1 
    AND status='U' 
  START WITH dept_no IS NULL 
CONNECT BY TO_NUMBER(regexp_substr(a,'[^,]+',1,level)) IS NOT NULL
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
user3195273
  • 193
  • 1
  • 5
  • 13
  • check examples given under http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm – Exhausted Jan 14 '15 at 11:52
  • I tried in the samy way, like SELECT DISTINCT TO_NUMBER(regexp_substr(a,'[^,]+',1,level)) numbers FROM x WHERE seq_no=1 AND status='U' START WITH dept_no IS NULL CONNECT BY TO_NUMBER(regexp_substr(a,'[^,]+',1,level)) IS NOT NULL; – user3195273 Jan 14 '15 at 12:03

0 Answers0