Problem when filtering an input file

[gpbarsky]

Hi.

I have a huge problem when I filter an input file, using a transformer stage

The input file has 24000 records, and from these records about 16000 match the selection criteria. But only 9500 are selected (all numbers were rounded).

Is there any limit in DataStage for the process of records, or filters, or whatever can make this process fail ?

Any help will be appreciated.

Thanks in advance.

P.S.: I checked out the condition and everything is fine.

[kcbland]

Please post your constraint so that we can see what you are doing.

You could be getting fouled up by NULL values in your data, which don't return the appropriate response to Boolean statements. You may have to address that in your constraint.

Another problem could be a literal string comparison with leading/trailing spaces. It looks the same but isn't. You may have to introduce TRIM(link.column) into your equalities.

[gpbarsky]

Ken:

Thanks for your answer.

Here is a cut&paste of what I am doing.

...........

The idea here is to filter the job by those parameters, where the CLASETAR column of the input file may contain a value equal to ClaseTarketa*. Is this clear ?

Thanks again.

[kcbland]

Create an output link to a sequential text file. Put all of your job parameters, plus link columns, into columns in that output file. Do a derivation of

Code: Select all

If ISNULL(link.column or your jobparameter) THEN "NULL" Else link.column or your jobparameter

Add one last column called "RESULT" which contains your constraint. If your constraint is TRUE it will have 1, otherwise FALSE will be 0.

See what's going on. By outputing "NULL", you'll see the word NULL in the output file. Anywhere with a RESULT 1 works, anything with a result of 0 doesn't work, and you'll see the reason by interrogating each of the components in your equation.

[Teej]

You could be getting fouled up by NULL values in your data, which don't return the appropriate response to Boolean statements. You may have to address that in your constraint.

I have been bitten by that so often, it's not funny.

Are you (or anyone on here) aware of the reasoning behind considering NULL values as separate from everything else during boolean inquiries? Is it a SQL standard thing? Is there an option to automatically include NULL as true, except when explicitly noted otherwise?

I'm coming from a C/C++ world, so I don't understand the thinking behind NULL behavior here.

I know it's off-topic, yet I bet plenty of folks here would like to know too.

-T.J.

[chulett]

Yah, it's a "SQL Standard thing" Teej.

In the "Three Valued Logic" world where we all live, booleans come in three flavors: True, False and Unknown. NULL is the unknown value and since we don't know anything about it, it can be neither true nor false. When working with NULLs, you've always got to keep that in mind and account for them in your sql.

You can thank The Codd and The Date (or maybe just one, I don't recall exactly) for that.

[kcbland]

In a world of TRUE or FALSE, NULL means "I don't know". So if you ask a question:

Code: Select all

If something is TRUE, THEN
   Do This
ELSE
   Do That
End

If something is Unknown, it will always take the Else clause, NO MATTER WHAT. Even if you reverse the statement, it's still taking the Else path:

Code: Select all

If something is NOT TRUE, THEN
   Do That
ELSE
   Do This
End

It will "Do This" because you can never take the "THEN" clause because you don't know!

NVL, NVL, NVL. Your friend.