Page 1 of 1
LookUp Problem
Posted: Fri Jan 04, 2008 11:43 am
by arunselvaraj
Iam trying to lookup a char(1) column with varchar(30) column. But iam not getting null for not matched record in the lookup. When iam looking the records in the dataset after lookup, iam having a blank field but it is not null. I need null when the column is not matched.Please anyone give me an idea what is happening there??
Posted: Fri Jan 04, 2008 11:50 am
by ArndW
Welcome to DSXchange, arunselvaraj.
(that red is very difficult to read)
If the column coming from the lookup is declared as nullable=Yes and your lookup is set to "continue" on failed lookups then the value will be null. What are your nullability settings in your job and please explain which column is Char and which VarChar?
Posted: Fri Jan 04, 2008 12:02 pm
by arunselvaraj
Iam looking up column named start_c char(1) from a dataset with nullable=no and code_val_c varchar(30) from a db2 stage with nullable=no.My lookup is set to "continue" on failed lookups. Iam getting all the matched and unmatched from the lookup. But while checking the nullability for the unmatched records in transformer, i came to know tat null are not coming for the unmatched one.
I checked with isnull(Code_val_c) in transformer. But iam getting only 0. Then i came to know tat null is not coming
Posted: Fri Jan 04, 2008 12:05 pm
by ArndW
Set the column Code_val_c to be nullable from your lookup and try again.
Posted: Fri Jan 04, 2008 12:10 pm
by arunselvaraj
I too tried tat...but all in vain. For the unmatched records, null is not coming. i dono whether it is blank space.
iam worried what is happening there??
Posted: Fri Jan 04, 2008 12:34 pm
by ArndW
Put in an additional column for testing, setting it to 1 if "In.Code_val_c=' '" and 0 otherwise and you'll know the answer.
I doubt that you correctly set the nullable attribute in the DB column, otherwise failed lookup column will be coming back as null.
Posted: Fri Jan 04, 2008 3:22 pm
by ray.wurlod
Have you set default values for these columns?
Posted: Fri Jan 04, 2008 7:18 pm
by WDWolf
I think you need to step back and look at what ArndW said. FRom your description not sure if you are using a lookup fileset or a db2 stage directly, but the field in the lookup must be created nullable (assuming fileset) and the metadata in the job at hand also set this way. Basically all of the fields, input, lookup, output side of lookup must be created as nullable to get this to work correctly...I see it all the time here (ver 7.5.1a)