Home » SQL & PL/SQL » SQL & PL/SQL » Insert into one table from multi table (Oracle 9i R2, SUN Solaris)
Insert into one table from multi table [message #350378] Wed, 24 September 2008 20:30 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I have following table structures and i have to insert/update from another four tables into web table.
I used to only insert and now requirement got changed as they wants only data when email is not null from USER table for email nad other info, phone from contact tables if phone is not null and other info.

Table Scripts:
CREATE TABLE WEB_INFO
(
  ID                   		VARCHAR2(20 BYTE) NOT NULL,
  EMAIL                         VARCHAR2(100 BYTE) NOT NULL,
  MGR		             	VARCHAR2(80 BYTE),
  SHIFT                     	VARCHAR2(10 BYTE),
  CREW                      	VARCHAR2(20 BYTE),
  SHIFT_FROM               	VARCHAR2(8 BYTE),
  SHIFT_TO                 	VARCHAR2(8 BYTE),
  ALIAS                    	VARCHAR2(80 BYTE),
  TEAM                  	VARCHAR2(60 BYTE),
  WORK_PHONE             	VARCHAR2(25 BYTE),
  MOBILE_PHONE           	VARCHAR2(25 BYTE),
  TITLE           		VARCHAR2(60 BYTE),
  WAVE                      	VARCHAR2(20 BYTE),
    CONSTRAINT WEB_INFO_PK PRIMARY KEY (ID),
  CONSTRAINT WEB_INFO_FK1 FOREIGN KEY (ID)
 
    REFERENCES USER (USER_ID)
)

CREATE TABLE USER
(
  USER_ID                   		VARCHAR2(20 BYTE) NOT NULL,
  EMAIL                         VARCHAR2(100 BYTE),
CONSTRAINT USER_PK PRIMARY KEY (ID)
 
)


CREATE TABLE EMP
(
  USER_ID                   	VARCHAR2(20 BYTE) NOT NULL,
  EMAIL                         VARCHAR2(100 BYTE),
  SHIFT				VARHCAR2(8),
  ALIAS				VARHCAR2(8),
  TITLE				VARCHAR2(60 BYTE),
  ACTIVE			CHAR(1),
CONSTRAINT EMP_PK PRIMARY KEY (USER_ID),
CONSTRAINT WEB_INFO_FK1 FOREIGN KEY (ID)
 
    REFERENCES USER (USER_ID)
 
)

CREATE TABLE Contact
(
  ID                   		VARCHAR2(20 BYTE) NOT NULL,
  USER_ID       		VARCHAR2(40 BYTE),
  PHONE                  	VARCHAR2(25),
  FAX                    	VARCHAR2(25),
      CONSTRAINT Contact_PK PRIMARY KEY (ID)
  )

CREATE TABLE ADDRESS
(
  ID                	VARCHAR2(40 BYTE)    NOT NULL,
  HOME_ID      		VARCHAR2(40 BYTE),
  BILL_ID   		VARCHAR2(40 BYTE),
  SHIP_ID  		VARCHAR2(40 BYTE),
  CONSTRAINT ADDRESS_PK PRIMARY KEY (ID),
  FOREIGN KEY (ID)
 
    REFERENCES USER (USER_ID)


My original query:
SELECT a.id, decode(a.email,null,' ', a.email ), '', b.shift,'','','', b.alias, '',
 
replace(d.phone,'"'), replace(d.fax,'"'), b.title,''
 
 FROM user a, emp b, address c, contact d
 WHERE a.user_id = b.user_id AND active = 1 AND a.USER_id = c.id AND c.home_id = d.id
and a.USER_ID not in (Select T.ID from WEB_INFO T)

Now i can't use decode(a.email,null,' ', a.email ) while inserting as they wants only emails when USER.email is not null and same thing for Contact.phone is not null.
I would like to know how can i add that not null in my query to restrict each for email for USER table and phone for Contact table.

Thanks and appreciate your help!
Re: Insert into one table from multi table [message #350397 is a reply to message #350378] Wed, 24 September 2008 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Jusr replace "decode(a.email,null,' ', a.email )" by a simple "a.email".

Regards
Michel
Re: Insert into one table from multi table [message #350411 is a reply to message #350378] Thu, 25 September 2008 00:07 Go to previous messageGo to next message
coolyogi4u
Messages: 10
Registered: September 2008
Junior Member
you can add condition in where clause as

a.email is not null and d.phone is not null
Re: Insert into one table from multi table [message #350583 is a reply to message #350397] Thu, 25 September 2008 08:43 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Michel.
If i Just replace "decode(a.email,null,' ', a.email )" by a simple "a.email" then email is NULL field in USER table and email is NOT NULL in Web_info table so it willn't be a problem.

Thanks,
Poratips
Re: Insert into one table from multi table [message #350585 is a reply to message #350411] Thu, 25 September 2008 08:45 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks CoolYogi.
Thanks for your response.
If i add NOT NULL in my where clause for both the fields then it will look the records if both the condition satisfied then it omit the records for either email or phone right?
Let say if email is not null but phone is null then it will not insert the records either as for email is still not null and if phone is Not null but email NULL then it will also won't insert the records and i will lose those records also, right?
SELECT a.id, decode(a.email,null,' ', a.email ), '', b.shift,'','','', b.alias, '',
replace(d.phone,'"'), replace(d.fax,'"'), b.title,''
FROM user a, emp b, address c, contact d
WHERE a.user_id = b.user_id AND active = 1 AND a.USER_id = c.id AND c.home_id = d.id
and a.USER_ID not in (Select T.ID from WEB_INFO T)
--You are asking to add like:
AND USER.email is not null
and Contact.phone is not null

but if we can add sub query for email and phone number so it will restrict there, right?
I am having difficulties to add sub clause.

Thanks,
Re: Insert into one table from multi table [message #350801 is a reply to message #350585] Fri, 26 September 2008 07:12 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I have also tried to Where (Email is not null OR phone is not null) but it throwing an error becuase email is NOT NULL in Web_info table and NULL in USER table.
Appreciate yout help.

Thanks,
Re: Insert into one table from multi table [message #350821 is a reply to message #350801] Fri, 26 September 2008 09:13 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Following is my mapping for requirements:


TARGET TABLE	TARGET columns	SOURCE table	SOURCE columns	Condition (individual)
				
WEB_INFO	USER_ID (PK)	USER	ID (PK)	
WEB_INFO	EMAIL (NN)	USER	EMAIL (NULL)	Load Only when USER.email is NOT NULL
WEB_INFO	MGR			
WEB_INFO	SHIFT	EMP	SHIFT	Load Only when EMP.shift is NOT NULL
WEB_INFO				
WEB_INFO	TIME_FROM			
WEB_INFO	TIME_TO			
WEB_INFO	ALIAS	EMP	ALIAS	
WEB_INFO				
WEB_INFO	PHONE	CONTACT	PHONE (NULL)	Load only when CONTACT.phone is NOT NULL
WEB_INFO	MOBILE	CONTACT	FAX	
WEB_INFO	TITLE	EMP	TITLE	



Thanks,
Previous Topic: Package, Function Application between multiple tables Query
Next Topic: Getting table statistics
Goto Forum:
  


Current Time: Sat Dec 03 12:10:42 CST 2016

Total time taken to generate the page: 0.04183 seconds