Hi Sergey,
To me it looks like
SELECT ..., BITOR(0,myfield) ..
It would need to store the intermediate in pnResult like
pnResult = 0
SELECT
FUNCTION BITORSQL(tnVal)
pnResult =BITOR(pnResult , tnVal)
RETURN pnResult
and to reset pnResult for each unifier (e.g. group), see below
But anyway BITORSQL is no aggregate function so i end up with error 1807"GROUP BY clause is invalid "
If I SET ENGINEBEHAVIOR 70 it aggregates, but does not, reset, so i change
(asuming i1 is unifier and i2 is pattern)
pnResult = 0
pnLastUni = 0
SELECT i1,BITORSQL(i2,i1) FROM muh GROUP BY 1
FUNCTION BITORSQL(tnVal,tnUni)
IF tnUni#pnLastUni THEN
pnLastUni = tnUni
pnResult = 0
ENDIF
pnResult =BITOR(pnResult , tnVal)
RETURN pnResult
ENDFUNC
I get something, bot a closer look shows that the function will not be called for every record in the source
so, after a closer look at subqueries, I have tried
pnResult = 0
pnLastUni = 0
SELECT;
Cur1.i1,;
(SELECT MAX(BITORSQL(cur2.i2,cur2.i1)) AS i2 FROM muh AS cur2 WHERE cur2.i1=cur1.i1) AS i2;
FROM muh AS cur1;
GROUP BY 1
FUNCTION BITORSQL(tnVal,tnUni)
IF tnUni#pnLastUni THEN
pnLastUni = tnUni
pnResult = 0
ENDIF
pnResult =BITOR(pnResult , tnVal)
RETURN pnResult
ENDFUNC
and unbelievably it works. MAX is ok, I will not use bit 31 anyway.
So now a bit of cleanup for better variable handling
LOCAL;
lnResult,;
lnLastUni
STORE 0 TO;
lnResult,;
lnLastUni
SELECT;
Cur1.i1,;
(SELECT MAX(BITORSQL(cur2.i2,cur2.i1,@lnResult,@lnLastUni)) AS i2 FROM muh AS cur2 WHERE cur2.i1=Cur1.i1) AS i2,;
sum(i2);
FROM muh AS Cur1;
GROUP BY 1
FUNCTION BITORSQL(tnVal,tnUni,tnResult,tnLastUni)
IF tnUni#tnLastUni THEN
tnLastUni = tnUni
tnResult = 0
ENDIF
tnResult =BITOR(tnResult , tnVal)
RETURN tnResult
ENDFUNC
BTW the subquery seems to be identified as aggregate. I have SET ENGINEBEHAVIOR 80 and GROUP BY is only row 1, but all runs fine. Mayby not because GROUP BY 1,2 changes nothing.
Thank you
Agnes
>Hi Agnes,
>
>Would this work for you?
>
>pnResult = 0
>
>SELECT ..., BITORSQL(myfield) ...
>
>FUNCTION BITORSQL(tnVal)
>RETURN BITOR(pnResult , tnVal)
>
>
>>
>>I have a small problem to aggregate a field.
>>
>>The field is a integer holding bit patterns.
>>
>>I like to aggregate like a BITOR, IOW all bits set should remain.
>>
Words are given to man to enable him to conceal his true feelings.
Charles Maurice de Talleyrand-Périgord
Weeks of programming can save you hours of planning.
OffThere is no place like [::1]