ETL and Data Integration tools - Modern ETL software for everyone

I'm having some trouble with a job I recently created. The source is a FOX
PRO DB. Target is a MySQL DB. For the source I'm using a Select statement:

Select * from drugndc Where (DLastPriceUpd > (Date() - 91))

Now, it appears that updates are occurring. However, looking at
"Transformation Log", looks like I getting lots of errors. Please advise.

Richard

Information 7/2/2010 11:19:14 AM Starting...
Information 7/2/2010 11:19:14 AM Target Table: medsndccost
Information 7/2/2010 11:19:14 AM Trasnformation Type: ADD New and UPDATE Old Records
Information 7/2/2010 11:19:14 AM SET AUTOCOMMIT=0;

Information 7/2/2010 11:19:14 AM Source Select Statement: Select * from drugndc Where (DLastPriceUpd > (Date() - 91))

Error 7/2/2010 11:19:14 AM SQLExecDirect(FHSMT,PChar ('select count(*) as rec_count from (Select * from drugndc Where (DLastPriceUpd > (Date() - 91))
) a'), SQL_NTS) Failed: 42000 [Microsoft][ODBC Visual FoxPro Driver]Syntax error.
Warning 7/2/2010 11:19:14 AM Unable to determine number of records in the Data Source: SQLExecDirect(FHSMT,PChar ('select count(*) as rec_count from (Select * from drugndc Where (DLastPriceUpd > (Date() - 91))
) a'), SQL_NTS) Failed: 42000 [Microsoft][ODBC Visual FoxPro Driver]Syntax error.
Information 7/2/2010 11:19:14 AM Using Bulk Fetch
Error 7/2/2010 11:19:15 AM
#HY000Field 'PMCUnitCost' doesn't have a default value
Information 7/2/2010 11:19:15 AM insert into medsndccost ("NDCNum","DrugIDQualifier","NDCFi","DrugID","DrugManID","DrugNameID","ManuFactID","Shape","Color","PackageSize","PkgSizeMedicaid","UnitDoseID","AWPCost","DirectCost","MacCost","WACCost","Plan340B","Plan340BCost","SpecialPrice","ProRate","SPriceExp","LastPriceUpdt","VSUpdateDate" ) values (:F1,:F2,:F3,:F4,:F5,:F6,:F7,:F8,:F9,:F11,:F12,:F13,:F14,:F15,:F16,:F18,:F19,:F20,:F21,:F22,:F23,:F24,:F25 )

Information 7/2/2010 11:19:15 AM F1=00406048505
Information 7/2/2010 11:19:15 AM F2=03
Information 7/2/2010 11:19:15 AM F3=1
Information 7/2/2010 11:19:15 AM F4=2Z10L0XX8
Information 7/2/2010 11:19:15 AM F5=
Information 7/2/2010 11:19:15 AM F6=1A10R1I1D
Information 7/2/2010 11:19:15 AM F7=0X50Z003A
Information 7/2/2010 11:19:15 AM F8=ROUND
Information 7/2/2010 11:19:15 AM F9=WHITE
Information 7/2/2010 11:19:15 AM F11=500
Information 7/2/2010 11:19:15 AM F12=0
Information 7/2/2010 11:19:15 AM F13=1
Information 7/2/2010 11:19:15 AM F14=768.35
Information 7/2/2010 11:19:15 AM F15=0
Information 7/2/2010 11:19:15 AM F16=191.65
Information 7/2/2010 11:19:15 AM F18=105.24
Information 7/2/2010 11:19:15 AM F19=0
Information 7/2/2010 11:19:15 AM F20=0
Information 7/2/2010 11:19:15 AM F21=0
Information 7/2/2010 11:19:15 AM F22=0
Information 7/2/2010 11:19:15 AM F23=12:00:00 AM
Information 7/2/2010 11:19:15 AM F24=7/2/2010
Information 7/2/2010 11:19:15 AM F25=7/2/2010 11:19:15 AM
Error 7/2/2010 11:19:15 AM

Hello

It is complaining about field PMCUnitCost

#HY000Field 'PMCUnitCost' doesn'PMCUnitCostt have a default value
Information 7/2/2010 11:19:15 AM insert into medsndccost ("NDCNum","DrugIDQualifier","NDCFi","DrugID","DrugManID","DrugNameID","ManuFactID",
"Shape","Color","PackageSize","PkgSizeMedicaid","UnitDoseID","AWPCost","DirectCost","MacCost","WACCost",
"Plan340B","Plan340BCost","SpecialPrice","ProRate","SPriceExp","LastPriceUpdt","VSUpdateDate" )
values (:F1,:F2,:F3,:F4,:F5,:F6,:F7,:F8,:F9,:F11,:F12,:F13,:F14,:F15,:F16,:F18,:F19,:F20,:F21,:F22,:F23,:F24,:F25 )



I guess it is not null field, plus you have not mapped it and there is no default value.


ALTER TABLE
medsndccost
MODIFY
PMCUnitCost DATE NOT NULL DEFAULT 'somevalue'

Mike