exists function issue to drop fields if that field exists

[#1]

Hi all,

I have a application table that contain a list of all customer tables that i loop through. Not all the tables contain the same fields, as a result i would like to drop certain fields.

The code works fine but i get the unwanted fields, i am able to drop some fields as those fields are within each table and are not necessary. The issue is with the "exists()" function, i would like to use that to check to see if a field exists, then drop it from the table.

if i cannot used the exists() function is there a better way to check if field exists, these unwanted fields are NULL.

code

//********************** Categories built for Customer ******************************************

LET NumberOfTables = NoOfRows('FS_Categories');

FOR i = 0 to $(NumberOfTables)-1

LET C= peek('CategoryName', $(i), 'FS_Categories');

[$(C)]:
QUALIFY *;
UNQUALIFY DocID, SatID, DateDelete, DocType, OfflineDate, DeleteFlag, "FS_InboxOwner", "FS_InboxSubject", FS_ReprintServer, FS_ReprintPrinter ;

LOAD *;
SQL Select *
FROM $(vDBName).[$(C)];

UNQUALIFY *;

DROP FIELDS SatID, DateDelete, DocType, OfflineDate, DeleteFlag, "FS_InboxOwner", "FS_InboxSubject" FROM [$(C)];

STORE [$(C)] into $(vDataSourcePath)$(C).qvd; //store every table to qvd in datasources

//if (exists('$(C)'.FS_ReprintServer)=-1) then
// DROP FIELD [$(C).FS_ReprintServer] FROM [$(C)];
//end if

drop table [$(C)];

NEXT

//***************************************************************

drop table FS_Categories;

\code

thankyou all in advance

[#2]

hi all,

I found a workaround using the exists

if(exists("FS_ReprintServer",'NULL')=0 then

drop field ....

end if

however, is there a better way to do this?

thanks

[rwunderlich · Accepted solution]

How about using FieldNumber()? For example:

IF FieldNumber('FS_ReprintServer') > 0 THEN
DROP FIELD FS_ReprintServer;
END IF

-Rob

[#4]

Rob

thankyou for the help!

i just had to add the table name then move this before my store code line

IF FieldNumber('$(C).FS_ReprintServer','$(C)') > 0 THEN
DROP FIELD '$(C).FS_ReprintServer', '$(C).FS_ReprintPrinter' FROM [$(C)];
END IF

[stevenkoppens]

I would like to add to this that if you don't want to use a specific table, you should add the wildcard character.

So, the code which you would get is:

IF FieldNumber('FS_ReprintServer',*) > 0 THEN

DROP FIELD FS_ReprintServer;

END IF

[hugmarcel]

Hi

its working using the * (without quotes), although it is displayed as being syntactically incorrect in the code.

If field 'FS_ReprintServer' exists in several tables though, it looks like the fieldnumber of the latest table loaded is being shown.

Marcel

[hugmarcel]

Addition:

"IF FieldNumber('FS_ReprintServer',*) > 0 THEN ..." does not work if you use it in a sub.

The script will terminate with an error.

I propose to use FIELDVALUECOUNT() instead to determine if a field exists.

E.g. you can use

//Check if field QVField exists (in any table)
SUB CheckFieldExists ( QVField )
  LET gFieldExists = 0;
  IF (NOT ISNULL(FIELDVALUECOUNT('$(QVField)'))) THEN
    LET gFieldExists = 1;
  END IF
END SUB

FIELDVALUECOUNT returns 0 if QVField contains NULL() values only, and NULL if it does not exist.

Marcel

[aritting]

Thanks Marcel,  I extended your code and created this sub to drop a field

// This Sub will check if a field exists before dropping it
// Use to avoid errors during script load when dropping fields due to name changes;
// Example:
// if call QVSDeveloper.DropFieldExist('MyColumnName')

SUB QVSDeveloper.DropFieldExist (vL._Root)
IF (NOT ISNULL(FIELDVALUECOUNT('$(vL._Root)'))) THEN
  DROP FIELD '$(vL._Root)';
ELSE  
  TRACE '$(vL._Root) Field Not Found';
ENDIF
SET vL._Root=;
END SUB;