After recent ERP Upgrades , we have been experiencing an error on screens i.e Operand data type bit is invalid for max operator.We have customizations which have a boolean/bit data type & dacs, and now after the recent upgrades we are now having to change them to use integers. Is it an acumatica issue or we have to workaround with our customizations ?
Best answer by Naveen Boga
This is a SQL Server error, not an Acumatica application error. It occurs when Acumatica's customization tries to execute something like:
SELECT MAX(MyBooleanField) FROM MyTable
-- SQL Server does NOT support MAX() on a BIT column
-- This is a SQL Server limitation, not Acumatica's
Solution:
DAC Change
// BEFORE
[PXDBBool]
[PXUIField(DisplayName = "My Flag")]
public virtual bool? MyFlag { get; set; }
// AFTER
[PXDBInt]
[PXUIField(DisplayName = "My Flag")]
public virtual int? MyFlagInt { get; set; }
// Keep bool property for UI convenience
public virtual bool? MyFlag
{
get { return MyFlagInt == 1; }
set { MyFlagInt = value == true ? 1 : 0; }
}
Database Column Change (Most Thorough)
-- Change BIT column to INT in the database
ALTER TABLE [MyCustomTable]
ALTER COLUMN [MyBooleanField] INT NOT NULL DEFAULT 0;
-- Update existing values
UPDATE [MyCustomTable]
SET [MyBooleanField] = CASE
WHEN [MyBooleanField] = 1 THEN 1
ELSE 0
END;
Hi
=IIf([YourBoolField] = True, 1, 0)
then apply max()
This happens where MAX is used on boolean (BIT) fields. SQL Server does not support this, and the newer version seems to enforce it strictly.
+20
This is a SQL Server error, not an Acumatica application error. It occurs when Acumatica's customization tries to execute something like:
SELECT MAX(MyBooleanField) FROM MyTable
-- SQL Server does NOT support MAX() on a BIT column
-- This is a SQL Server limitation, not Acumatica's
Solution:
DAC Change
// BEFORE
[PXDBBool]
[PXUIField(DisplayName = "My Flag")]
public virtual bool? MyFlag { get; set; }
// AFTER
[PXDBInt]
[PXUIField(DisplayName = "My Flag")]
public virtual int? MyFlagInt { get; set; }
// Keep bool property for UI convenience
public virtual bool? MyFlag
{
get { return MyFlagInt == 1; }
set { MyFlagInt = value == true ? 1 : 0; }
}
Database Column Change (Most Thorough)
-- Change BIT column to INT in the database
ALTER TABLE [MyCustomTable]
ALTER COLUMN [MyBooleanField] INT NOT NULL DEFAULT 0;
-- Update existing values
UPDATE [MyCustomTable]
SET [MyBooleanField] = CASE
WHEN [MyBooleanField] = 1 THEN 1
ELSE 0
END;
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
