Help with Lookups

jmperezrus · Post by **jmperezrus** » Mon Sep 04, 2006 5:12 pm

I am going nuts with this guys... your help will be much appreciated.

What is the output of a Lookup stage (parallel) if no results are found matching the input? If we select "View data" it appears like a "null" value for this field, or like a 0-length string. Obviously these are different but for "View data" the appear the same. So I decided to open the output file itself with Ultraedit.
My field is a Char10 and if I check the output file physically it is a 10 null characters (hex 00 00) string. However, the Isnull function returns 0 (which means false). How can this be??

Thanks, best regards
Jose

ray.wurlod · Post by **ray.wurlod** » Mon Sep 04, 2006 9:09 pm

The output depends on your "failed lookup" rule. Sounds like you are using Continue.

In this case, the failed lookup link's columns are all set to NULL. This may be further affected by whether the receiving link's column definition specifies nullable or not and, where applicable, what its Null Field Value property is set to.

What you are seeing is two things in combination. The default NULL representation of 0x00 (ASCII NULL) and the pad character (default value also 0x00) being used to pad a Char field.

Use APT_STRING_PADCHAR to override the default pad character, and specify a Null Field Value property the same length as the Char datatype.

jmperezrus · Post by **jmperezrus** » Tue Sep 05, 2006 6:00 am

Thanks for your post Ray.
I can't read the whole post though...

Anyway, I must use "Continue" in the lookup, cause I want to keep those registries.

What I actually get is a string with 10 null (00 00 hex) characters (the field is a char 10).

Any other ideas? Please...

kumar_s · Post by **kumar_s** » Tue Sep 05, 2006 7:17 am

If you intend to check Null you can optionally use LEN(TRIM(Link.Column))=0 which will give you the desired result.
The "00 00 hex" is configurable in Datastage Adminstrator client in Environmental variable.

jmperezrus · Post by **jmperezrus** » Tue Sep 05, 2006 7:25 am

Thanks Kumar.

I have actually tried that and the returned value for LEN is 10. I know, weird... My field has ten "00 00 hex" characters and its "LEN" is 10. Therefore I cannot distinguish valid registries from these null ones by using that function...

kumar_s · Post by **kumar_s** » Tue Sep 05, 2006 7:35 am

LEN should retun 10 but have you tried LEN(TRIM())?

jmperezrus · Post by **jmperezrus** » Tue Sep 05, 2006 8:37 am

Yes, I did... Sorry I did not mention it. Result was 10...

ray.wurlod · Post by **ray.wurlod** » Tue Sep 05, 2006 4:27 pm

For less than US$1 per week you CAN read the whole post.
They do have to fund this site. Premium memberships contribute only a small proportion of the actual running cost.

kumar_s · Post by **kumar_s** » Tue Sep 05, 2006 4:46 pm

Assign APT_STRING_PADCHAR to a space ' '. As suggested by Ray.

jmperezrus · Post by **jmperezrus** » Tue Sep 05, 2006 4:58 pm

Thanks Kumar but I think that by doing that I could create problems with all the null handling I have done so far in the project...

rasi · Post by **rasi** » Tue Sep 05, 2006 8:09 pm

Hi

You can enable it by job level. You don't have to apply this to your entire project.

jmperezrus · Post by **jmperezrus** » Tue Sep 19, 2006 2:55 am

Thanks everyone for your posts...

I have finally created a routine that removes these null characters in-between strings by using Seq function...

Best regards
Jose