Lookups/Joins propagating 0 instead of NULL - what the hell did the developers of DataStage not get about the concept of NULL?

I was driven quite crazy by DataStage when I set up a lookup stage where the main input does not necessarily find a companion in the input link. All the numerical columns of no-matches got the value 0 assigned.
  • I checked whether I happened to have defined a default - nope.
  • I checked whether the output link column is nullable - yes it is.
  • May seasoned DS colleague double checked - and had no idea why.
  • Some forum of entry of 2005 told me that also the INPUT column must be nullable - bingo! It was not, changing it to nullable (maybe one has to have some stage element between the lookup and a source like a database connection) et voilà the desired and expected behaviour manifested. I tried to use a Modify stage for this purpose such that it gets clear that the change is fully intentional but it aborted gibbering something about enablePrinting.
I suppose other stages like Merge will have the same problem but I did not check.

@ Those developers: NULL is not 0. It means "we do not know". Maybe the following analogy helps you to get the point. Imagine you work paid by the hour but get cashed out weekly. So you start on Monday and on Wednesday somebody asks you how much do you get paid on Friday. Do you say 0? Probably not, if you exactly know how many hours you are supposed to work until Friday you might calculate that hypothetical value but what if you get sick... Or even easier, you are a craftsman and not only produce on order but also just for fun and sell your work. Could you tell how much you earned at the end of the week before Friday evening? Would you say 0? You would say something like "I know that my employer owes my money by Friday but I DO NOT KNOW yet how much it will be". Or in the later case "I DO NOT KNOW yet, maybe I can sell some of my work on Friday bazaar." Got it?

Kommentare

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

Error when checking operator: Could not find input field 'field_name'

Well, you checked and double-checked the existence of respective field and it is there! Yeah, stumbled over it again. And again! Possible solutions You need to compile jobs explicitly. You're done then. Try it. Go! What are you waiting for? If you are really extra sure compiled the job, then double-check the presence of the respective field again, but not in the stage DS tells you but in the very source at the start of the dataflow where this field comes from. It happens that if the column name of your SQL is not identical to the field name propagated. I do absolutely not know why: compilation does not reveal the problem DataStage misses the correct problem location by far
Mehr anzeigen

Missing record delimiter "\n", saw EOF instead

Actually, this warning is quite simple to fix. Apparently, DS expects the last record of a file to terminate with the record delimiter in any case - maybe there is a setting to alter this behaviour but I did not deem worthwhile the effort to look for it. So the solution is just to terminate the file on a single empty line. Obviously, this case is for Unix like line breaks. The others delimiters are handled analogously.
Mehr anzeigen