Hi All,

I have BOOLEAN data type field in my table and and I created synonym on it,

Below is the master file snapshot of FLAG filed ,

 FIELDNAME=FLAG, ALIAS=FLAG, USAGE=I11, ACTUAL=I4,  

Now i need to perform conditional operation based on flag field , If I am using below condition , it says Cannot convert 'INT4' to 'BOOL'

 DEFINE FILE XYZ
A_SALE/D12= IF( FLAG EQ 0 ) THEN SALES ELSE 0;
B_SALE/D12= IF( FLAG EQ 1 ) THEN SALES ELSE 0;
END

TABLE FILE XYZ
SUM A_SALES
B_SALES
BY FILED_A
END  

I did a workaround and that works , I created another field using FLAG field and change the DATA type to check condition on it

 DEFINE FILE XYZ
D_FLAG/I1 = EDIT(FLAG);
A_SALE/D12= IF( D_FLAG EQ 0 ) THEN SALES ELSE 0;
B_SALE/D12= IF( D_FLAG EQ 1 ) THEN SALES ELSE 0;
END

TABLE FILE XYZ
SUM A_SALES
B_SALES
BY FILED_A
END  

Is there any way where I would be able to use FLAG field directly instead of creating another field .

This message has been edited. Last edited by: FP Mod Chuck,

I think that WF can convert the BOOL to Integer. However, when you issue a test of the field, WF doesn't know that the original field is boolean hence the SQL message.
I would suggest 2 possibilities:
1. Try changing the field to USAGE=I1, ACTUAL=I1 and see what happens
2. If this does not work, put the DEFINE in the Master, without using EDIT: DEFINE D_FLAG/I1=FLAG;

quote:

1. Try changing the field to USAGE=I1, ACTUAL=I1 and see what happens
2. If this does not work, put the DEFINE in the Master, without using EDIT: DEFINE D_FLAG/I1=FLAG;

Thanks Danny-SRL ,

I tried both the suggestions .None of these does work for me.

My suspicion:

As long as the table request does not attempt to pass through an arithmetic expression treating the Boolean FLAG field as an integer value, table requests a boolean value (True or False) and takes care of the rest. -- But when table tries to pass through the expression

IF( FLAG EQ 0 ) THEN SALES ELSE 0;

as a Select column, the SQL server raises a syntax error (cannot convert 0 to Boolean).

To test that hunch, see what happens with:
TABLE FILE XYZ
PRINT FLAG SALE FIELD_A
on table set recordlimit 1000
ON TABLE HOLD
END

DEFINE FILE HOLD
A_SALE/D12= IF( FLAG EQ 0 ) THEN SALES ELSE 0;
B_SALE/D12= IF( FLAG EQ 1 ) THEN SALES ELSE 0;
END

TABLE FILE HOLD
SUM A_SALES B_SALES
BY FIELD_A
END