Error while using FieldNumber()

[#1]

Hi,

I have a resident table from where I am loading data into another table. While loading, I check for the existence of a special field (revrec_partner_code) in that resident table using FieldNumber().

Sample Code:

if(FieldNumber('revrec_partner_code','RevRec1')=0,'N.A.',[revrec_partner_code]) as [revrec_partner_code]

    resident RevRec1

I get the following error:

  Error: Field not found - <revrec_partner_code>

I thought FieldNumber would return 'N.A' since revrec_partner_code column does not exist in the resident table. Can you please help?

Thanks!

[hic]

You have a field reference to a nonexistent field. Then you get a syntax error which means that the call to FieldNumber() never is made.

if(FieldNumber('revrec_partner_code','RevRec1')=0,'N.A.',[revrec_partner_code]) as [revrec_partner_code]

HIC

[swuehl]

A possible workaround may be to concatenate your RevRec1 table to an empty header table that shows all fields needed in subsequent loads:

RevRec1:

LOAD

     'Dummy' as revrec_partner_code

Autogenerate 0;

CONCATENATE (RevRec1)

LOAD * FROM RevRec1Source;

Sample Code:

if( IsNull( revrec_partner_code),'N.A.',[revrec_partner_code]) as [revrec_partner_code]

    resident RevRec1

[swuehl · Accepted solution]

Or if you want to go with FieldNumber(), evaluate the FieldNumber outside the load:

T1:

LOAD

Recno() as Field

//, 1 as Dummy        // comment in or out for testing

AutoGenerate 5;

Let vCode = If(FieldNumber('Dummy','T1')>0, 'Dummy',chr(39)&'NA'&chr(39) ) & ' AS Dummy';

T2:

NOCONCATENATE

LOAD Field, $(vCode)  Resident T1;

DROP TABLE T1;

[#5]

Thanks swuehl...it worked like a charm when I put FieldNumber out of Load. I searched in help file earlier but couldn't capitalize on that clue.