DSXchange

I use this derivation in the transformer stage , i am getting the compliation error:

If i/p_column1 = "A" Then IF IsNotNull((ip_column2)[1,7]) THEN
ip_column2[1,7] ELSE SetNull() Else SetNull()

when i used
If i/p_column1 = "A" Then ip_column2[1,7] ELSE SetNull() it worked with out any prblem

i/p_column1 is CHAR of 2 length and not nullable and ip_column2 is VARCHAR 194 and nullable...

pls explain where the problem is..

thanks

vij wrote:I use this derivation in the transformer stage :

If i/p_column1 = "A" Then IF IsNotNull((ip_column2)[1,7]) THEN
ip_column2[1,7] ELSE SetNull() Else SetNull()

The above is absolutely wrong. It can be a 'If Then Else' statement and cannot be a 'If Then If Then Else Else' statement.

I mean, there is no provision for 'If Then If Then Else Else' statements. . Atleast in the programming languages that I'm aware of.

What exactly are you trying to achieve?

Whale.

Also, Can you post your compilation error.
Many people are facing transfomer compilation error nowdays.

vij wrote:
If i/p_column1 = "A" Then IF IsNotNull((ip_column2)[1,7]) THEN
ip_column2[1,7] ELSE SetNull() Else SetNull()

Why not AND in condition ..... like

If i/p_column1 = "A" AND IsNotNull((ip_column2)[1,7]) THEN
ip_column2[1,7] ELSE SetNull()

Thanks,
Anupam

I_Server_Whale wrote:

vij wrote:I use this derivation in the transformer stage :

If i/p_column1 = "A" Then IF IsNotNull((ip_column2)[1,7]) THEN
ip_column2[1,7] ELSE SetNull() Else SetNull()

The above is absolutely wrong. It can be a 'If Then Else' statement and cannot be a 'If Then If Then Else Else' statement.

I mean, there is no provision for 'If Then If Then Else Else' statements. . Atleast in the programming languages that I'm aware of.

What exactly are you trying to achieve?

Whale.

Thanks for ur reply!

I want to do this :

if ipcol1=A and isnotnull(substring(col2[1,2]) , pass substring(col2[1,2] otherwise null.

i tried this also :

If ipcol1= "a" and ISNOTNULL(col2[1,2]) THEN col2[1,2] ELSE SetNull()

Try now

If i/p_column1 = "A" AND Not(IsNull(ip_column2)) THEN
ip_column2[1,7] ELSE @NULL


Thnaks,
Anupam

Thanks for ur reply!

I tried it, its still not working! the same error.

sb_akarmarkar wrote:Try now

If i/p_column1 = "A" AND Not(IsNull(ip_column2)) THEN
ip_column2[1,7] ELSE @NULL


Thnaks,
Anupam

Whats the actual error that you are getting?

aakashahuja wrote:Whats the actual error that you are getting?

theres no error attached wiith the compliation codes.
but the problem is in the substring in the second if clause. - If i/p_column1 = "A" Then IF IsNotNull((ip_column2)[1,7]) THEN
ip_column2[1,7] ELSE SetNull() Else SetNull()

Look like empty string... Try to add Trim(ColumnName) <> ''...

Thanks,
Anupam

sb_akarmarkar wrote:Look like empty string... Try to add Trim(ColumnName) <> ''...

Thanks,
Anupam

but this is a compliation error rite?

compilation message :


*** Internal Generated Transformer Code follows:
0001: //
0002: // Generated file to implement the V0S2_Xml_Load_Transformer_2 transform operator.
0003: //
0004:
0005: // define our input/output link names
0006: inputname 0 DSLink1;
0007: outputname 0 DSLink4;
0008:
0009: initialize {
0010: // define our row rejected variable
0011: int8 RowRejected0;
0012:
0013: // define our null set variable
0014: int8 NullSetVar0;
0015:
0016: // declare our intermediate variables for this section
0017: string InterVar0_0;
0018: string InterVar0_1;
0019:
0020: // initialise constant values which require conversion
0021: InterVar0_0 = "G1";
0022: InterVar0_1 = "37";
0023: }
0024:
0025: mainloop {
0026: // initialise our row rejected variable
0027: RowRejected0 = 1;
0028:
0029: // evaluate columns (no constraints) for link: DSLink4
0030: DSLink4.Sequence_Nbr = DSLink1.Sequence_Nbr;
0031: NullSetVar0 = 0;
0032: if ((DSLink1.Record_type == InterVar0_0)) {
0033: if (notnull(substring_1(DSLink1.other_columns , 1 , 7))) {
0034: DSLink4.POG_ID_G1 = substring_1(DSLink1.other_columns , 1 , 7);
0035: } else {
0036: NullSetVar0 = 1;
0037: }
0038: //;
0039: } else {
0040: NullSetVar0 = 1;

vij wrote:

aakashahuja wrote:Whats the actual error that you are getting?

theres no error attached wiith the compliation codes.
but the problem is in the substring in the second if clause. - If i/p_column1 = "A" Then IF IsNotNull((ip_column2)[1,7]) THEN
ip_column2[1,7] ELSE SetNull() Else SetNull()

Looks like you are running the same code again.

Can you run the code as suggested by Anupam and post the error message?

Also, what are trying to achieve through your job? And how did you design it? .... ... ......

It always helps to post as many details as possible about your job(s).

Whale.

Hi ,

CAn you try this option , i think if the incoming value has null then you cannot specify (ip_column2)[1,7]

if i/p_column1 = "A" then
IF IsNotNull(ip_column2) THEN
ip_column2[1,7]
ELSE
SetNull()
Else SetNull()

Why not try replace the IsNull()/IsNotNull() with a NullToEmpty like this -
Or if you want to use to IFs (not adviced) -
Aneesh