Re: Insert question

Because you are using the = operator, it will not recognise null = null. Try using nvl on the cd_field an cd_wd_status fields.

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 nvl(b.CD_FIELD,'*') = nvl(to_char(c.CD_WO_STATUS),'*')

HTH Mark

Gennaro Napolitano wrote in message <3781E164.59684EA2_at_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 - 07:15:29 CDT