Testing for Null on a not nullable field which is Nullable

[ggarze]

I'm getting an error using the IsNull() function on a field that DS is saying is not nullable. However the field is nullable on the table(SQL server) yet datastage, looking at the schema looks like it doesn't see it that way.
Below is the SQL. As you can see the field in question comes from a subselect. (b.MINI_WAVE_ASSIGNED as MINI_WAVE_ASSIGNED_15413
)

select a.PICK_WAVE_NUM
, a.BELT_WAVE_NUM
, a.MINI_WAVE_NUM
, a.CHUTE_NUM
, a.MINI_WAVE_ASSIGNED
, a.MINI_WAVE_ASSIGNED_14937
, a.MINI_WAVE_ASSIGNED_14939
, a.MINI_WAVE_ASSIGNED_14938
, a.MINI_WAVE_ASSIGNED_14942
, a.MINI_WAVE_ASSIGNED_14902
, b.MINI_WAVE_ASSIGNED as MINI_WAVE_ASSIGNED_15413
from STAGING_BEUMER_MAIN a, STAGING_BEUMER_15413 b
where a.chute_num = b.chute_num
and b.MINI_WAVE_ASSIGNED = (Select min(c.MINI_WAVE_ASSIGNED)
from STAGING_BEUMER_15413 c


Here is what the scema shows in the job log:
Data set "SQL_STAGING_BEUMER_MAIN_JOIN:LNK_JOIN_WITH_15413.v":
record
( PICK_WAVE_NUM: string[max=20];
BELT_WAVE_NUM: int32;
MINI_WAVE_NUM: int32;
CHUTE_NUM: int32;
MINI_WAVE_ASSIGNED: nullable string[max=26];
MINI_WAVE_ASSIGNED_14937: nullable string[max=26];
MINI_WAVE_ASSIGNED_14939: nullable string[max=26];
MINI_WAVE_ASSIGNED_14938: nullable string[max=26];
MINI_WAVE_ASSIGNED_14942: nullable string[max=26];
MINI_WAVE_ASSIGNED_14902: nullable string[max=26];
MINI_WAVE_ASSIGNED_15413: string[max=26];
)
All the other fields show nullable except this one which I need to check for null. Is there a way in the AS statment on the SQL to define the field as varchar nullable?

thnaks,
Glenn

[ray.wurlod]

You'd better post the exact error message because your subject ("on a not nullable field which is Nullable") is totally confusing.

[ggarze]

Yeah... sorry about that. Anyway it turns out the field on the table was Not Nullable on the table where the other table it was nullable. I don't have the error anymore as the logs cleared.

Anyway is there a way in a select statement in the "AS" statement to define a field's SQL type?

For example:
Select fielda AS new_field from .....

So can I do something like Select fielda AS new_field(VARCHAR(26)) from ...

[ray.wurlod]

Generically use CAST operator. You can still use AS to create the alias.

Code: Select all

SELECT CAST(fielda AS VARCHAR(26)) AS new_fielda FROM ...

[ggarze]

Great Thanks!