FirebirdSQL
BIN_OR and BIN_AND as aggreagate function
Hi
as #8173 is closed as not planned, please add BIN_OR and BIN_AND as aggregate functions (can be under different name if you see some conflicts).
Currently we must do this in stored procedures or execute block in the loop. This will simplify things a lot.
example what we must do currently instead of simple grouping function:
EXECUTE BLOCK RETURNS(
RODZ_REP_ID TYPE OF COLUMN RODZAJ_REPARTYCJI.RODZ_REP_ID
, RODZ_REP_NAZWA TYPE OF COLUMN RODZAJ_REPARTYCJI.RODZ_REP_NAZWA
, RODZ_REP_TEXT TYPE OF COLUMN RODZAJ_REPARTYCJI.RODZ_REP_TEXT
, MASKA_OBSZAR_ID TYPE OF COLUMN RODZAJ_UMOWY.MASKA_OBSZAR_ID
, MASKA_STR VARCHAR(16)
)
AS
DECLARE VARIABLE TMP_MASKA_OBSZAR_ID TYPE OF COLUMN RODZAJ_UMOWY.MASKA_OBSZAR_ID;
DECLARE VARIABLE NEXT_RODZ_REP_ID TYPE OF COLUMN RODZAJ_REPARTYCJI.RODZ_REP_ID;
BEGIN
NEXT_RODZ_REP_ID = '';
MASKA_OBSZAR_ID = 0;
FOR
SELECT
DISTINCT
RR.RODZ_REP_ID
, RR.RODZ_REP_NAZWA
, RR.RODZ_REP_TEXT
, RU.MASKA_OBSZAR_ID
/* , LICZ_BIT_TO_STR(RU.MASKA_OBSZAR_ID, 16) AS MASKA_STR */
, LEAD(RR.RODZ_REP_ID, 1) over (order by RR.RODZ_REP_TEXT, RR.RODZ_REP_ID, RD.RDB$DB_KEY) AS NEXT_RODZ_REP_ID
FROM
RODZAJ_REPARTYCJI RR
INNER JOIN RODZAJ_DZIAL RD ON RR.RODZ_REP_ID = RD.RODZ_REP_ID
INNER JOIN RODZAJ_UMOWY RU ON RU.RODZ_UMOWY_ID = RD.RODZ_UMOWY_ID AND RU.TYP_UMOWY_ID = RD.TYP_UMOWY_ID
WHERE
RR.RODZ_REP_ID <> ''
ORDER BY
RR.RODZ_REP_TEXT, RR.RODZ_REP_ID, RD.RDB$DB_KEY
INTO :RODZ_REP_ID, :RODZ_REP_NAZWA, :RODZ_REP_TEXT, :TMP_MASKA_OBSZAR_ID, :NEXT_RODZ_REP_ID
DO
BEGIN
MASKA_OBSZAR_ID = BIN_OR(:MASKA_OBSZAR_ID, :TMP_MASKA_OBSZAR_ID);
IF (:RODZ_REP_ID IS DISTINCT FROM :NEXT_RODZ_REP_ID) THEN
BEGIN
MASKA_STR = LICZ_BIT_TO_STR(MASKA_OBSZAR_ID, 16);
SUSPEND;
TMP_MASKA_OBSZAR_ID = 0;
MASKA_OBSZAR_ID = 0;
END
END
END
#8173 was closed because it's duplicated. I'm not sure this usage would be generic enough to be defined as a builtin aggregate function.
There is actually a SQL standard alternative for this: the use of EVERY, ANY and SOME as aggregate functions (Firebird currently only supports ANY and SOME (and ALL) in quantified comparison predicates), and such this ticket is a duplicate of #6227.
I added BIN_AND additionally to the request, but now that I think about it, I don't know how it would work for BIN_AND. For BIN_OR, it is obvious, but for BIN_AND... Unless you see how it could work for BIN_AND as well, otherwise I will remove it from the request description.
There is no difference between AND and OR. Both of these operations are communicative and associative so result doesn't depend on order of data. The only difference is that OR tends to end up in all bits set while AND ends up in all bits reset.
OK. So for BIN_OR it should start from b00000000... and for BNI_AND it should start from b11111111..
