Home » SQL & PL/SQL » SQL & PL/SQL » Connect By Query (Oracle 9i)
Connect By Query [message #392191] Mon, 16 March 2009 23:57 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

I am using the following query.

SELECT  REGEXP_SUBSTR (input_value_name, '[^|]+', 1, LEVEL) AS input_value_name
           ,REGEXP_SUBSTR (screen_entry_value, '[^|]+', 1, LEVEL) AS screen_entry_value
       FROM (SELECT ROWNUM ID
                   ,'Date Taken|Hours||||'  input_value_name
                   ,'|8||||'  screen_entry_value
                   FROM DUAL
            )
     CONNECT BY INSTR (input_value_name, '|', 1, LEVEL - 1) > 0
            AND ID = PRIOR ID
            AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL


Result IS
---------------------------------------------
INPUT_VALUE_NAME SCREEN_ENTRY_VALUE
---------------------------------------------
Date Taken 8

Hours
---------------------------------------------


How to get the following result??

---------------------------------------------
INPUT_VALUE_NAME SCREEN_ENTRY_VALUE
---------------------------------------------
Date Taken

Hours 8
---------------------------------------------

I think the problem is AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

please give your suggestions.
Re: Connect By Query [message #392195 is a reply to message #392191] Tue, 17 March 2009 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Align your columns in output.
Put your version with 4 decimals.
Post a test case.
Use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Tue, 17 March 2009 00:16]

Report message to a moderator

Re: Connect By Query [message #392200 is a reply to message #392191] Tue, 17 March 2009 00:22 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Version is 10.2.0.2.0

Ouptut I am getting.
--------------------------------------------
INPUT_VALUE_NAME	SCREEN_ENTRY_VALUE
-------------------------------------------
Date Taken	        8

Hours	
--------------------------------------------

Output i needed. Because here the date_taken has NULL, 
and hours only have value 8. 
But the DBMS_RANDOM package 
moving the not values to previous row.  
How can i achieve the following output?

--------------------------------------------
INPUT_VALUE_NAME	SCREEN_ENTRY_VALUE
-------------------------------------------
Date Taken	        

Hours	                8
--------------------------------------------
Re: Connect By Query [message #392202 is a reply to message #392200] Tue, 17 March 2009 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post:
SQL> SELECT  REGEXP_SUBSTR (input_value_name, '[^|]+', 1, LEVEL) AS input_value_name
  2             ,REGEXP_SUBSTR (screen_entry_value, '[^|]+', 1, LEVEL) AS screen_entry_value
  3         FROM (SELECT ROWNUM ID
  4                     ,'Date Taken|Hours||||'  input_value_name
  5                     ,'|8||||'  screen_entry_value
  6                     FROM DUAL
  7              )
  8       CONNECT BY INSTR (input_value_name, '|', 1, LEVEL - 1) > 0
  9              AND ID = PRIOR ID
 10              AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
 11  /
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

Regards
Michel

[Updated on: Tue, 17 March 2009 00:42]

Report message to a moderator

Re: Connect By Query [message #392204 is a reply to message #392191] Tue, 17 March 2009 00:41 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi Michel,

I am getting the result for the same query.

Re: Connect By Query [message #392205 is a reply to message #392204] Tue, 17 March 2009 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't trust you, copy and paste your SQL*Plus session.
How can I trust you when you say "Version is 10.2.0.2.0" and in the topic title "Oracle 9i".
Copy and paste query from v$version.

Regards
Michel

[Updated on: Tue, 17 March 2009 00:44]

Report message to a moderator

Re: Connect By Query [message #392207 is a reply to message #392191] Tue, 17 March 2009 00:47 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member

select * from v$version;
/
BANNER
---------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE	10.2.0.2.0	Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production



Sorry for the wrong verion in title.
Re: Connect By Query [message #392223 is a reply to message #392207] Tue, 17 March 2009 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still didn't post your query execution.

Regards
Michel
Re: Connect By Query [message #392236 is a reply to message #392191] Tue, 17 March 2009 02:18 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Michel,
Can you please tell me what query execution you want?
Re: Connect By Query [message #392237 is a reply to message #392236] Tue, 17 March 2009 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yours as I posted it.

Regards
Michel
Re: Connect By Query [message #392243 is a reply to message #392191] Tue, 17 March 2009 02:47 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi Michel,

I am using TOAD and attached screenshot
Re: Connect By Query [message #392244 is a reply to message #392202] Tue, 17 March 2009 02:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I get exactly what @Michel gets:
SQL> SELECT  REGEXP_SUBSTR (input_value_name, '[^|]+', 1, LEVEL) AS input_value_name
  2             ,REGEXP_SUBSTR (screen_entry_value, '[^|]+', 1, LEVEL) AS screen_entry_value
  3         FROM (SELECT ROWNUM ID
  4                     ,'Date Taken|Hours||||'  input_value_name
  5                     ,'|8||||'  screen_entry_value
  6                     FROM DUAL
  7              )
  8       CONNECT BY INSTR (input_value_name, '|', 1, LEVEL - 1) > 0
  9              AND ID = PRIOR ID
 10              AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


@Spmano - what we want you to do is to go to SQL*Plus,run the query from there, and cut and paste the screen showing the query and the results from SQL*Plus into here.
Re: Connect By Query [message #392245 is a reply to message #392244] Tue, 17 March 2009 03:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Weird..
Oracle 10 XE:
SQL> SELECT  REGEXP_SUBSTR (input_value_name, '[^|]+', 1, LEVEL) AS input_value_name
  2             ,REGEXP_SUBSTR (screen_entry_value, '[^|]+', 1, LEVEL) AS screen_entry_value
  3         FROM (SELECT ROWNUM ID
  4                     ,'Date Taken|Hours||||'  input_value_name
  5                     ,'|8||||'  screen_entry_value
  6                     FROM DUAL
  7              )
  8       CONNECT BY INSTR (input_value_name, '|', 1, LEVEL - 1) > 0
  9              AND ID = PRIOR ID
 10              AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

INPUT_VALUE_NAME     SCREEN
-------------------- ------
Date Taken           8
Hours





6 rows selected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Re: Connect By Query [message #392581 is a reply to message #392245] Wed, 18 March 2009 09:07 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
There's no need of dbms_random when querying for connect_by_root but I heard that the won't allow this any more on 11g.

SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> SELECT  REGEXP_SUBSTR (input_value_name, '[^|]+', 1, LEVEL) AS input_value_name
  2             ,REGEXP_SUBSTR (screen_entry_value, '[^|]+', 1, LEVEL) AS screen_entry_value
  3         FROM (SELECT ROWNUM ID
  4                     ,'Date Taken|Hours||||'  input_value_name
  5                     ,'|8||||'  screen_entry_value
  6                     FROM DUAL
  7              )
  8       CONNECT BY INSTR (input_value_name, '|', 1, LEVEL - 1) > 0
  9              AND ID = connect_by_root ID
 10  /

INPUT_VALUE_NAME     SCREEN
-------------------- ------
Date Taken           8
Hours





6 rows selected.

SQL>



Bye Alessandro
Re: Connect By Query [message #392585 is a reply to message #392581] Wed, 18 March 2009 09:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I tried that, and still no joy.
SQL> select * from v$version
  2  ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> SELECT  REGEXP_SUBSTR (input_value_name, '[^|]+', 1, LEVEL) AS input_value_name
  2             ,REGEXP_SUBSTR (screen_entry_value, '[^|]+', 1, LEVEL) AS screen_entry_value
  3         FROM (SELECT ROWNUM ID
  4                     ,'Date Taken|Hours||||'  input_value_name
  5                     ,'|8||||'  screen_entry_value
  6                     FROM DUAL
  7              )
  8       CONNECT BY INSTR (input_value_name, '|', 1, LEVEL - 1) > 0
  9              AND ID = PRIOR ID
 10  /
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected


I'm upgrading to 10.2.0.4 tonight, so I'll see what we get then.
Previous Topic: DB_LINK question
Next Topic: how to get length long raw datatype (merged 4)
Goto Forum:
  


Current Time: Mon Dec 05 13:04:15 CST 2016

Total time taken to generate the page: 0.11294 seconds