Home » SQL & PL/SQL » SQL & PL/SQL » How to handle NULL while inserting records in a table in PL/SQL? (11.6)
How to handle NULL while inserting records in a table in PL/SQL? [message #577750] Wed, 20 February 2013 03:29 Go to next message
oraclethebest
Messages: 19
Registered: June 2011
Location: Noida
Junior Member
HI,

I have a data from the source table like this :

Id Date1 No1 Date2 No2
1 2/15/1995 9292 8/30/1997 11767
2 5/21/1993 2812 4/29/2002 2812
3 9/22/1978
4 10/11/1978 1157381 2/8/1979 929137
5 8/2/1989 1698430 11/30/1989 1365528
6
7 11/20/1995 41 1/23/1997 677
8 11/20/1995 1/23/1997 680


I want to insert into two separate tables using the following logic :
If date1 is not null or no1 is not null then insert into target_table1(id,date1,no1)
If date2 is not null or no2 is not null then insert into target_table2(id,date2,no2)

PLease help
Re: How to handle NULL while inserting records in a table in PL/SQL? [message #577752 is a reply to message #577750] Wed, 20 February 2013 03:31 Go to previous messageGo to next message
cookiemonster
Messages: 11191
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use two seperate insert/select statements.
Re: How to handle NULL while inserting records in a table in PL/SQL? [message #577753 is a reply to message #577750] Wed, 20 February 2013 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use multi-table INSERT.

Regards
Michel

[Updated on: Wed, 20 February 2013 03:32]

Report message to a moderator

Re: How to handle NULL while inserting records in a table in PL/SQL? [message #577754 is a reply to message #577753] Wed, 20 February 2013 03:33 Go to previous messageGo to next message
oraclethebest
Messages: 19
Registered: June 2011
Location: Noida
Junior Member
how do i handle records no 6 which is null. it gives me error: Case not found and exits from the loop.
Re: How to handle NULL while inserting records in a table in PL/SQL? [message #577755 is a reply to message #577753] Wed, 20 February 2013 03:34 Go to previous message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance:
INSERT FIRST
WHEN (s.date1 is not or s.no1 is not null) THEN INTO target1 VALUES (id,date1,no1)
WHEN (s.date2 is not or s.no2 is not null) THEN INTO target2 VALUES (id,date2,no2)
SELECT * FROM source;

Regards
Michel

Previous Topic: Concat
Next Topic: Audit Table with Primary keys
Goto Forum:
  


Current Time: Tue Nov 25 21:23:45 CST 2014

Total time taken to generate the page: 0.05612 seconds