Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to mirroring/replicating a table from one database to another database?

622371Jun 24 2008 — edited Jun 25 2008
I am having a table in 10g Database.
I want to mirror this table in 9i Database.

How to do this?

Comments

BluShadow
Try this:
select sum(case when customer_po_number like 'consign-%' then 1 else null end) as test1
      ,sum(case when customer_po_number like 'non-consign-%' then 1 else null end) as test2
      ,sum(case when customer_po_number like 'REGULAR%' then 1 else null end) as test3
      ,sum(case when customer_po_number like 'NON-REGULAR%' then 1 else null end) as test4
from oe_headers_all
/
Toon Koppelaars
That's easy :-)
(select count(*) "test1" from oe_headers_all where customer_po_number like 'consign-%')
,
(select count(*) "test2" from oe_headers_all where customer_po_number like 'non-consign-%')
,
(select count(*) "test3" from oe_headers_all where customer_po_number like 'REGULAR%')
,
(select count(*) "test4" from oe_headers_all where customer_po_number like 'NON-REGULAR%')
from dual
Not efficient though. Previous reply is much more efficient...

Edited by: Toon Koppelaars on Dec 23, 2009 11:16 AM
Karthick2003
You can use CASE statement to achieve this.
select count(case when customer_po_number like 'consign-%' then 1 else null end) test1,
       count(case when customer_po_number like 'non-consign-%' then 1 else null end) test2,
       count(case when customer_po_number like 'REGULAR%' then 1 else null end) test3,
       count(case when customer_po_number like 'NON-REGULAR%' then 1 else null end) test4
  from oe_headers_all
730428 
select sum(CASE WHEN customer_po_number like 'consign-%' THEN 1 ELSE 0 END) "test1", 
       sum(CASE WHEN customer_po_number like 'non-consign-%' THEN 1 ELSE 0 END) "test2", 
       sum(CASE WHEN customer_po_number like 'REGULAR%' THEN 1 ELSE 0 END) "test3", 
       sum(CASE WHEN customer_po_number like 'NON-REGULAR%' THEN 1 ELSE 0 END) "test4"
from oe_headers_all 
where customer_po_number like 'NON-REGULAR%' or
customer_po_number like 'REGULAR%'  or
customer_po_number like 'non-consign-%' or
customer_po_number like 'consign-%' ;
Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2009/12/18/table-elimination-oppure-join-elimination-lottimizzatore-si-libera-della-zavorra]
ravikumar.sv 
select 
count(case when instr(customer_po_number,'consign-')=0 then 1 else null end) "test1",
count(case when instr(customer_po_number,'non-consign-')=0 then 1 else null end) "test2",
count(case when instr(customer_po_number,'REGULAR')=0 then 1 else null end) "test3",
count(case when instr(customer_po_number,'NON-REGULAR')=0 then 1 else null end) "test4"
from oe_headers_all
Ravi Kumar
711216
Hi ,

The query is working fine.

Thankyou

Regards,
Kiran
BluShadow
ravikumar.sv wrote: 
select 
count(case when instr(customer_po_number,'consign-')=0 then 1 else null end) "test1",
count(case when instr(customer_po_number,'non-consign-')=0 then 1 else null end) "test2",
count(case when instr(customer_po_number,'REGULAR')=0 then 1 else null end) "test3",
count(case when instr(customer_po_number,'NON-REGULAR')=0 then 1 else null end) "test4"
from oe_headers_all
Ravi Kumar
That won't work.

"consign-" is a sub string of "non-consign" and "REGULAR" is a sub string of "NON-REGULAR" so you will get false matches on two of the counts.
ravikumar.sv
check the query...I didn't kept >=0...
instr(customer_po_number,'consign-')=0
I kept just =0 so it will pass only when it starts with that string...else it will fail
check this query... 
select case when instr('non-consign','consign')=0 then 'true' else 'false' end from dual
;-)

Ravi Kumar
BluShadow
Ah, my mistake, you are correct. However, I guess that just shows that using instr isn't the clearest solution to follow. :)
730428
instr(customer_po_number,'consign-')=0
doesn't work anyway, you should use
instr(customer_po_number,'consign-')=1
;)


Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2009/12/18/table-elimination-oppure-join-elimination-lottimizzatore-si-libera-della-zavorra]
Karthick2003
ravikumar.sv wrote: 
select 
count(case when instr(customer_po_number,'consign-')=0 then 1 else null end) "test1",
count(case when instr(customer_po_number,'non-consign-')=0 then 1 else null end) "test2",
count(case when instr(customer_po_number,'REGULAR')=0 then 1 else null end) "test3",
count(case when instr(customer_po_number,'NON-REGULAR')=0 then 1 else null end) "test4"
from oe_headers_all
Ravi Kumar
Not sure if iam missing something
select instr('non-consign-','non-consign-') from dual
this returns 1, correct?

Then how will your code work. Because it checks for 0.
ravikumar.sv
yeah you both are right...yes it will return start position...i.e '1'

may be it's the influence from other languages...i gave as 0.. :-)

Ravi Kumar
1 - 12
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 23 2008
Added on Jun 24 2008
#general-database-discussions
15 comments
1,845 views