I want to count group by statement values and write control.
There is a table which have 3 fields; ItemId, TaxGroup and TaxItemGroup. Same itemId must have same taxgroup and taxItemGroup values. If lines which have same ItemId have different tax values, i should throw an exception.
I wrote this code but this return number of all records. How can i write this control?
while select count(RecId) from ActivityLineExtraLcl
group by Itemid, TaxGroup, TaxItemGroup
where ActivityLineExtraLcl.Amount != 0
if(ActivityLineExtraLcl.RecId > 1 )
throw error("Same ItemIds can't have different values!");
The logic you're attempting won't work as written and it doesn't really make sense. You can try something like the below. This identifies the actual items. Your code (if it worked) just looks to see if the entire table ActivityLineExtraLcl
has any issues.
ActivityLineExtraLcl ActivityLineExtraLcl;
ActivityLineExtraLcl ActivityLineExtraLclExists;
while select ItemId from ActivityLineExtraLcl
group by ItemId
where ActivityLineExtraLcl.Amount != 0
exists join ActivityLineExtraLclExists
where ActivityLineExtraLclExists.ItemId == ActivityLineExtraLcl.ItemId &&
ActivityLineExtraLclExists.Amount != 0 &&
(ActivityLineExtraLclExists.TaxGroup != ActivityLineExtraLcl.TaxGroup ||
ActivityLineExtraLclExists.TaxItemGroup != ActivityLineExtraLcl.TaxItemGroup)
{
error(strFmt("Item '%1' has different tax values", ActivityLineExtraLcl.ItemId));
}