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
In Lkp / Date field /db INSERT Null problem
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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 ...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.