Insert question

From: Gennaro Napolitano <Gennaro.Napolitano_at_italdata.it>
Date: Tue, 06 Jul 1999 12:58:44 +0200
Message-ID: <3781E164.59684EA2@italdata.it>

Hy all

I have a little problem with an insert statement. I have to load the data from a table to another one, making some modification.
The involved tables are:

Source table:
SQL> desc WORK_ORDER_REC;

 Name                            Null?    Type


 ------------------------------- -------- ----


 OBJ_NO                          NOT NULL VARCHAR2(22)
 DESCRIPTION                              VARCHAR2(50)
 CD_WO_STATUS                             NUMBER(2)

and more, but these are significant to understand my problem

Destination table:
SQL> desc TC_PROJECT

 Name                            Null?    Type


 ------------------------------- -------- ----


 ID_PROJECT                      NOT NULL VARCHAR2(22)
 DESCRIPTION                              VARCHAR2(50)
 CD_WOSTATUS                              NUMBER(12)

etc...

I have a look-up table that give me the right relationships between the columns of the previous tables(i.e. OBJ_NO(WORK_ORDER_REC) should be loaded into ID_PROJECT(TC_PROJECT) etc..).

During the upload, some values must be modified using specific rules, in this case the upload columns OBJ_NO and CD_WO_STATUS must be converted using the following tables:

1)
SQL> desc TO_GUID

 Name                            Null?    Type


 ------------------------------- -------- ----


 OBJ_NO                          NOT NULL VARCHAR2(22)
 GUID                                     VARCHAR2(22)

for this table the rule is: insert into the destination table the GUID value for the given OBJ_NO.

2)
SQL> desc TC_SD_CODELIST

 Name                            Null?    Type


 ------------------------------- -------- ----


 ID_CODELIST                     NOT NULL NUMBER(12)
 TYPE                            NOT NULL VARCHAR2(35)
 CD_FIELD                        NOT NULL VARCHAR2(10)

for this table the rule is: insert into the destination table the ID_CODELIST value where TYPE = 'CD_WOSTATUS' and CD_FIELD = to_char(CD_WO_STATUS)

I tried with the following insert statement: INSERT INTO TC_PROJECT (ID_PROJECT, DESCRIPTION, CD_WOSTATUS) (SELECT a.guid, DESCRIPTION, b.ID_CODELIST from to_guid a, TC_SD_CODELIST b, WORK_ORDER_REC c
where c.obj_no = a.obj_no and
b.TYPE = 'CD_WOSTATUS' and b.CD_FIELD = to_char(c.CD_WO_STATUS))

It works fine if all the source records have a value for CD_WO_STATUS, whereas I am not able to load the source records having NULL value for CD_WO_STATUS. Has somebody any hints to give me in order to be able to load the other records too?

Thanks in advance

Ciao Gennaro Received on Tue Jul 06 1999 - 05:58:44 CDT