cannot insert NULL into PRICE_DATE

sateeshbabu · Post by **sateeshbabu** » Mon Sep 18, 2006 1:06 am

Hi Folks,

Actually from source i am getting price_date as varchar.First i need to ftp'ed the file and then should load into database.But while loading database i need to convert the price_date of string format to date.

i had defined price_date as

If IsNull(Lnk_Sp_ims_investec.PRICE_DATE) Then '' Else (Oconv(Iconv(Lnk_Sp_ims_investec.PRICE_DATE,"D/ MDY"),"DMDY"))

But i am getting a warning like cannot insert null into Price_date.

Could any body suggest me to solve this.

Thnaks i advance.
Sateesh.M

Kirtikumar · Post by **Kirtikumar** » Mon Sep 18, 2006 1:25 am

Which is the database?

If it is Oracle, then I guess you might need to put ' ' instead of '', because Oracle treats '' as null.

kumar_s · Post by **kumar_s** » Mon Sep 18, 2006 3:01 am

What is the exact error message? Check for nullability in the target table as well as the Load stage. By the way, why do you need to check for Null if you need to load Null based on the availablility of null in input?

info_ds · Post by **info_ds** » Mon Sep 18, 2006 5:57 am

Hi ,

First thing it is clear that the target column is non-nullable .
Check the source whether any null values are coming ,if there is some null values coming change that to a hardcoded value.when you are reading from the flatfile isnull routine does not read as null so make that as if input.col1 =" " then "some value".

Hope this will work out

kumar_s · Post by **kumar_s** » Mon Sep 18, 2006 7:21 am

Flat file cannot change Null into a space " ", unless a conversion is used or an internal property is assigned.

sateeshbabu · Post by **sateeshbabu** » Tue Sep 19, 2006 12:51 am

Hi Friends,

Thanksalot for your inputs.In source we are not getting as null.We are getting in the format of mm/dd/yyyy.

My targetdatabase is oracle.In query i had changed To_Date of price_date then it worked well.I had deleted the routine.

Thanks again for your inputs.

Regards,
Sateesh.M