In Lkp / Date field /db INSERT Null problem

hhh · Post by **hhh** » Sat Aug 26, 2006 12:15 am

My Formula for start_date column is "If IsNotNull(Summary_Report_ToTfm.Date) Then (If Summary_Report_ToTfm.Date <> '' Then Summary_Report_ToTfm.Date Else Summary_Report_ToTfm.Start_Date) Else Summary_Report_ToTfm.Start_Date "

Date is coming from Lkp database and it is not NULL field in table.Start_Date is coming from input database and it is not NULL field.
My output start_Date field in database is not NULL field.DATA TYPE OF ALL FIELDS ARE CHAR(10) ONLY.And My Target Table action is Replace Existing Rows Completely.

If i use above formula , getting following warning "CTI_ETL_JOB_STATUS_SUMMARY_ODBC,0: Warning: CTI_InventoryStatus_Report_Job_DyRdbms_Final.CTI_ETL_JOB_STATUS_SUMMARY_ODBC: [DataDirect][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'Start_Date', table 'mfcms.dbo.CTI_ETL_JOB_STATUS_SUMMARY'; column does not allow nulls. INSERT fails."

With above condition if look up field is not found then Start_Date should be our output.
If i pass Start_Date directly from input database to Start_Date derivation(OutPut field) , i am not getting any warning.

Is there any thing wrong with this condition or some other problem ? Please suggest your idea on this issue.

Regards,
Hiten

ArndW · Post by **ArndW** » Sat Aug 26, 2006 1:16 am

There are some anomalies in your logic:
- Why are you checking for IsNotNull() when you assert that no nulls can be found.
- A CHAR(10) field can never be equal to ''.

The only way for a null value to slip into your logic is if your "Date" field is set to null upon a failed lookup.

ray.wurlod · Post by **ray.wurlod** » Sat Aug 26, 2006 1:52 am

Please sketch out your job design. In particular, does the Lookup stage precede or follow the Transformer stage?

hhh · Post by **hhh** » Sat Aug 26, 2006 2:01 am

Hello,
I understood that with char(10) we cant give '', we have to provide equal length output for same.

Hence i changed my condition to "If Summary_Report_ToTfm.Date < Summary_Report_ToTfm.Start_Date Then Summary_Report_ToTfm.Date Else Summary_Report_ToTfm.Start_Date" , still i am getting same warnings.

ArndW wrote:There are some anomalies in your logic:
- Why are you checking for IsNotNull() when you assert that no nulls can be found.
- A CHAR(10) field can never be equal to ''.

The only way for a null val ...

ArndW · Post by **ArndW** » Sat Aug 26, 2006 4:15 am

Are you certain that your lookup is successful on every row? If not, then the value returned will be null.

ray.wurlod · Post by **ray.wurlod** » Sat Aug 26, 2006 4:17 am

A quick way to test this is to change your "Lookup Unsuccessful" rule to Fail. Your job will abort on the first unsuccessful lookup. You can always change it back again.