How to avoid NULL to reject - in SWITCH case

kumar_s · Post by **kumar_s** » Fri Jul 01, 2005 12:07 am

Hi all,
In input stage i have a column STATUS which cant have Y or N or null. I used switch to seperate to two pipeline. null should go to N link. Bul i can handle null value to flow to a stream, it goes to Reject link. I tried with '' , "", @NUL, @NULL.STR , NULL but doesent work. But i can make it up with transformer stage with "". STATUS is Varchar with 1 byte length. Can any one help me out.
Thanx in advance
-kumar

elavenil · Post by **elavenil** » Fri Jul 01, 2005 5:29 am

Before passing the selector column to 'Switch' stage, use modify stage to assign 'N' if the field has null value then use case stmt in switch. Then these input rows can be pipelined to two stream.

HTWH.

Regards
Saravanan

ray.wurlod · Post by **ray.wurlod** » Fri Jul 01, 2005 6:40 pm

@NULL and @NULL.STR are DataStage BASIC system variables and therefore inappropriate in parallel jobs.
What kind of "input stage" are you using? For example, the Reject link from a Sequential File stage only handles rows that do not match the schema associated with the input link.
You need to follow up with some method of handing the null, perhaps a Modify stage to convert same to 'N', and only then direct rows according to the value in STATUS, perhaps with a Switch stage. Or do both operations in a Transformer stage.

kumar_s · Post by **kumar_s** » Mon Jul 04, 2005 12:55 am

Input stage is simply a sequential stage. It is always works fine using transformer stage, but i thought in this scenario switch stage might work better

ray.wurlod · Post by **ray.wurlod** » Mon Jul 04, 2005 9:10 pm

It may, but you have to handle null explicitly and have the target column nullable.

alisoviejo · Post by **alisoviejo** » Mon Feb 20, 2006 1:25 pm

Hi,

If we cannot use @Null, how do we pass a null into a float field?. I have tried to sue SetNull() and doesnt work.

benny.lbs · Post by **benny.lbs** » Mon Feb 20, 2006 9:38 pm

Why not have a try on Filter stage ?

filter
-where 'STATUS = "Y" or STATUS = "N"'
-where 'STATUS is null'

alisoviejo · Post by **alisoviejo** » Tue Feb 21, 2006 9:26 am

I need to pass a Null value intoa float field, the solution you have mentioned is to detect Null values I believe.

kumar_s · Post by **kumar_s** » Wed Feb 22, 2006 9:05 am

Hi,
If Null is passed to Float field 0s will be defaulted. SetNull() can be used for the same reason.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 22, 2006 3:57 pm

SetNull() can be used provided the target column is defined as nullable.

kumar_s · Post by **kumar_s** » Wed Feb 22, 2006 4:48 pm

ray.wurlod wrote:SetNull() can be used provided the target column is defined as nullable.

But even nullable float column when passed with SetNull() will hold 0s

alisoviejo · Post by **alisoviejo** » Thu Feb 23, 2006 7:29 am

Thats what is happening on my end , I used SetNull() still it turns up to be 0!

ray.wurlod · Post by **ray.wurlod** » Thu Feb 23, 2006 1:54 pm

Have you reported this apparent bug through your support provider?

Is any of you who are experiencing this symptom running parallel jobs on a Windows platform? I note that the OP is on UNIX. If on UNIX, can you please post what kind of UNIX (for example AIX, HP-UX, Solaris, etc.)?

kumar_s · Post by **kumar_s** » Sun Feb 26, 2006 12:04 pm

UNIX - AIX.
But i was under the impression that, Decimal is also similar to integer which doesnt hold NULL rather 0s in it.

ray.wurlod · Post by **ray.wurlod** » Sun Feb 26, 2006 2:29 pm

ANY data type can hold null, provided that the column is defined as nullable (that is, not defined as NOT NULL).

Null (unknown value) is not the same as "" (empty string, or zero-length string, which is a known value). You can not put "" into any kind of numeric column.