Isnull problem

[umamahes]

I am using Isnull function in a transformer to check whether it is a null or not.

Isnull(DOB_LOAD_KEY):Stagevar

when i compile the job i am getting the following compilation error.
If i remove this condition job is compiling successfully and is working fine.

Output from transformer compilation follows:

##I TFCN 000001 11:30:24(000) <main_program>
Ascential DataStage(tm) Enterprise Edition 7.5.0.1
Copyright (c) 2004, 1997-2004 Ascential Software Corporation.
All Rights Reserved


##I TUTL 000031 11:30:24(001) <main_program> The open files limit is 2000; raising to 2147483647.
##I TOSH 000002 11:30:24(002) <main_program> orchgeneral: loaded
##I TOSH 000002 11:30:24(003) <main_program> orchsort: loaded
##I TOSH 000002 11:30:24(004) <main_program> orchstats: loaded
##I TFSC 000001 11:30:24(007) <main_program> APT configuration file: /ds/dsadm/Ascential/DataStage/Configurations/default.apt
##F TFSF 000002 11:30:24(009) <main_program> Fatal Error: When extracting a substring, offset must be non-negative: (CopyOfprdstest2.Transformer_7)

*** Internal Generated Transformer Code follows:
0001: //
0002: // Generated file to implement the V0S7_repos_Transformer_7 transform operator.
0003: //
0004:
0005: // define our input/output link names
0006: inputname 0 DSLink8;
0007: outputname 0 DSLink9;
0008: outputname 1 DSLink15;
0009: outputname 2 DSLink27;
0010:
0011: initialize {
0012: // define our row rejected variable
0013: int8 RowRejected0;
0014:
0015: // define our null set variable
0016: int8 NullSetVar0;
0017:
0018: // declare our intermediate variables for this section
0019: string InterVar0_0;
0020:
0021: // initialise constant values which require conversion
0022: InterVar0_0 = 1;
0023: // Stage variable declaration and initialisation
0024: string StageVar0_StageEnddate;
0025: StageVar0_StageEnddate = "";
0026: string StageVar0_StageDateofBirth;
0027: StageVar0_StageDateofBirth = "";
0028: }
0029:
0030: mainloop {
0031: // initialise our row rejected variable
0032: RowRejected0 = 1;
0033:
0034: // evaluate the stage variables first
0035: StageVar0_StageEnddate = null(DSLink8.END_LOAD_KEY);
0036: StageVar0_StageDateofBirth = null(DSLink8.END_LOAD_KEY);
0037:
0038: // evaluate columns (no constraints) for link: DSLink9
0039: writerecord 0;
0040: RowRejected0 = 0;
0041: // evaluate constraint and columns for link: DSLink15
0042: if ((StageVar0_StageEnddate == InterVar0_0))
0043: {
0044: DSLink15.DAY_KEY = DSLink8.END_EFFECTIVE_DATE;
0045: writerecord 1;
0046: RowRejected0 = 0;
0047: }
0048: // evaluate constraint and columns for link: DSLink27
0049: if ((StageVar0_StageDateofBirth == InterVar0_0))
0050: {
0051: DSLink27.DAY_KEY = DSLink8.DATE_OF_BIRTH;
0052: writerecord 2;
0053: RowRejected0 = 0;
0054: }
0055: }
0056:
0057: finish {
0058: }
0059:
*** End of Internal Generated Transformer Code

[DSguru2B]

IsNull() is to be used in a conditional statement. Like

Code: Select all

If (IsNull(<<col>>)) then do.this else do.that

IsNull() will return a numeric value. What have you defined the stage variable as?

[ray.wurlod]

IsNull() returns 1 or 0 (a numeric value). Therefore you can not use it as an operand of a concatenation operator, as you appear to be attempting to do. What data type is your stage variable?

[Sreedhar]

ISNULL is a function, which returns TRUE or FALSE
1- True
0-False

Regards,

[umlaut]

I have experienced a similar error. In my case I think the problem is due to the combination of a failed outer join from the Join Stage and the IsNull function being performed on a timestamp data type. Is the data type you are checking a timestamp or date?

When I changed the IsNull function to look at non-timestamp data type the job compiled fine. As a workaround I had to pass a constant char value (CHK_CHAR) in the Oracle Enterprise stage and check the failure of the outer join as IsNull(CHK_CHAR).