Trouble populating output columns

johnno · Post by **johnno** » Tue Mar 16, 2004 6:09 am

Hi all,

I have a server job which results in an Oracle table being populated by either one of two transformer stages.

A large number of these output columns will default to Null. In the first transformer stage, I just set a number of these columns to @Null and use various other derivations for the remaining columns.

If an input column Full_Sample = 'N' The first transformer does not populate the table but passes the data to another transformer which accesses another Oracle table for additional information. Basically the same derivations will be used in both transformer stages to populate the target Oracle table, obvioulsy having different sources for some of the data. When I compile this job, I get the following errors (sample only provided and I hope it display OK):

Compiling: Source = 'RT_BP368/JOB.307061661.DT.1322056336.TRANS2', Object = 'RT_BP368.O/JOB.307061661.DT.1322056336.TRANS2'
************************************************************************************************************************????***
0254 Pin%%V0S27P8.Column%%18 = (@Null)

^
')' unexpected, Was expecting: '('
0255 Pin%%V0S27P8.Column%%19 = (@Null)

^
')' unexpected, Was expecting: '('
0256 Pin%%V0S27P8.Column%%20 = (@Null)

^
')' unexpected, Was expecting: '('
0257 Pin%%V0S27P8.Column%%21 = (@Null)

^
')' unexpected, Was expecting: '('
0258 Pin%%V0S27P8.Column%%22 = (@Null)

^
')' unexpected, Was expecting: '('
0259 Pin%%V0S27P8.Column%%23 = (@Null)

^
')' unexpected, Was expecting: '('
0260 Pin%%V0S27P8.Column%%24 = (@Null)

^
')' unexpected, Was expecting: '('
0261 Pin%%V0S27P8.Column%%25 = (@Null)

^
')' unexpected, Was expecting: '('
0262 Pin%%V0S27P8.Column%%26 = (1)

^
')' unexpected, Was expecting: '('
0263 Pin%%V0S27P8.Column%%27 = ( If Len(Trim(Field(V0S27.Stagevar%%2, "|", 1))) > 0 Then Field(V0S27.Stagevar%%2, "|", 1) Else @Null)

^
')' unexpected, Was expecting: '('

An error is identified for every column from column 18 onwards where various derivations are used. Now if I create stage variables for these derivations and use them to populate the output columns, everything is OK! I don't understand. I have re-entered the derivations/cut and pasted acceptable derivations from columns prior to coulmn 18 - but nothing works.

As per usual, all assistance appreciated - and many thanks in advance!

Cheers
John

johnno · Post by **johnno** » Tue Mar 16, 2004 6:29 am

Just to keep you up to date, I just finished replacing all the derivations in the second transformer stage to stage variables, and (shock horror!) I got exactly the same errors for the first transformer! I am just about to go and set up stage variables here so I can at least get the job to compile and start testing.

Cheers
Johnno

chulett · Post by **chulett** » Tue Mar 16, 2004 7:36 am

Post the exact syntax for one of your derivations it is complaining about. I suspect you've got a simple syntax error and don't realize it.

Apologies if this is old news, but to set something to null use "@NULL" instead of "(@Null)" which is how it looks like you are doing it from your compiler errors.

johnno · Post by **johnno** » Wed Mar 17, 2004 3:57 am

Craig,

Thanks for the reply. I have tried '@NULL' instead of '@Null' and got the same error. As for an exact derivation, that is it I'm afraid, just '@Null' in the derivation column. An example of another derivation though is:

If Len(Trim(Field(svContributorDetailsOut, "|", 1))) > 0 Then Field(svContributorDetailsOut, "|", 1) Else @Null

I still use @Null in the stage variables and that seems to work, but when I use it in the derivation column for the table it fails. We have a number of jobs that populate this table and have not experienced this issue before.

Anyway, I have changed the job to use stage variables at the moment and have merely copied the derivations from the table output link to the stage variable. This works, but I believe this would not be considered 'best practice'.

Hope this is of some use.

Cheers
John

chulett · Post by **chulett** » Wed Mar 17, 2004 7:21 am

That's... odd.

About the simplest derivation you can put in a job is @NULL, I can't imagine why that would cause all of those compile errors without seeing the actual job. I'd report this to Ascential Support and see if they can make heads or tails of it.

As a suggestion, if you've got several columns that will always be set to null in a job/transformer, simply leave them out of the job. You don't have to mention every column when doing an Insert, any columns not specifically handled by the job can be left out and they will default to NULL in Oracle.

johnno · Post by **johnno** » Wed Mar 24, 2004 4:32 am

Thanks Craig. Will report it to Ascential. Will also take out those fields that default to Null.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 24, 2004 3:15 pm

Try using @NULL rather than @Null. Some versions of the compiler are finicky about the casing of system variable names.
Apart from that, the expressions look OK, at least in terms of matching the left and right parentheses, which is what the compiler is complaining about.
It might help if you could post that part of the generated Transformer code (from the RT_BP... directory) that deals with the derivations, so we can see what's happening in preceding and succeeding lines.