Hello,

I have SQL Server database columns that are defined as a BIT data type. These have values of either 0 or 1 stored in the database, which appear as such when doing a query in SQL Server Management Studio (SSMS). WebFOCUS, however, displays these as either 0 or -1. I have found a reference to this at iDM displays wrong value for SQLMSS 2008 BIT Data Type but from this "Solution" I take it that this was done by design.

My question is: How do you overcome the difference between what is "displayed" and what the actual value is? If I try to use a filter to limit the records to the -1 that is displayed, it does not find the records, because in the database the value is really 1. I CAN set up my filter to select records where that column is not equal to 0. This is just very non-intuitive and it seems there should be a better solution. Other than making defined fields for all of these, is there any other way to deal with these?

Thanks,
Chris

This message has been edited. Last edited by: <Kathryn Henning>,

Chris,

Are you using SQL pass thru or Master file?

If its SQL pass thru, then your filter for 1 should work fine.

Thanks,
Ram

I am using a master file. What is the best approach for this?

Thanks,
Chris

That "Troubleshooting" page posts no solution in the "Solution" section. I would open a case.

Meanwhile, perhaps you can modify the Master and add a DEFINE column, reference the DEFINE column in your fexes:

DEFINE BIT_COL_DEF/I1 = IF BIT_COL EQ -1 THEN 1 ELSE BIT_COL;

Unfortunately, this *may* effect performance when filtering on the DEFINE column, but give it a try.

Change the format of the BIT field to ...USAGE=A5,ACTUAL=A5...

Then you can use True or False as screening conditions.

WHERE BITFIELD EQ 'True'
or
WHERE BITFIELD EQ 'False'

False is 0, True is non-zero.

Changing usage/actual to A5 works well, and seems more straightforward than creating defined fields. Thanks for your help.

Chris