"ERROR: JOIN 'denormedrecs' contains no equality condition -

[PAULO FERNANDO BRUNOZI]

DenormalizeBrunozi.ecl

Main file - input files are in Playground

(1.83 KiB) Downloaded 52 times

Ao executar a função DENORMALIZE, ocorre o erro: "ERROR: JOIN 'denormedrecs' contains no equality condition - use ALL to allow (26,17) error code 4162. It is attached the ecl files[code][/code] The error descrition looks clear, it is not been matching noone condition defined on the DENORMALIZE function in order to return the expected result. Please, could you check if the code is correct? If am I doing any error? I have used the DENORMALIZE example of workshop last Tuesday. Best regards.
Additional information: Denormalize will use the range defined by columns LabelInicial and LabelFinal on the parent table BrunozziGRP see . The values of the column CodGrupo see of BrunozziDB (data base) will be validated if is in the range described before, in order to apply the denormalization.

I realized my logic is not correct, however it should work for some itens, I think. Maybe a solution could be create a C++ function that could test.

[rtaylor]

Paulo,

I can suggest one improvement:

Code: Select all

LoseDots(STRING9 s) := s[1..2] + s[4..5] + s[7..9];

DeNormedRecs := DENORMALIZE(ParentTbl,BrunozziDB,
       LoseDots(RIGHT.CodGrupo) BETWEEN
             LoseDots(LEFT.LabelInicial) AND LoseDots(LEFT.LabelFinal),
      DeNormThem(LEFT,RIGHT,COUNTER));

The BETWEEN operator duplicates your comparison logic more succinctly, and eliminating the periods in the data makes it easy to compare just the numeric values.

HTH,

Richard

[PAULO FERNANDO BRUNOZI]

Hi Richard, Thanks a lot. I will test your suggestion that looks very simple and clear.

In my previous logic, I have realized error, so I have changed by MAP command detail bellow and tested ok on separeted test ECL, but still getting the same error message when run main ECL. This is the MAP logic is bellow

*********
DeNormedRecs := DENORMALIZE(ParentTbl,BrunozziDB,
MAP(
(((INTEGER2)LEFT.LabelFinal[1..2]-(INTEGER2)LEFT.LabelInicial[1..2]) > ((INTEGER2)RIGHT.CodGrupo[1..2]-(INTEGER2)LEFT.LabelInicial[1..2])) AND ((INTEGER2)RIGHT.CodGrupo[1..2] > (INTEGER2)LEFT.LabelInicial[1..2]) => TRUE,
(((INTEGER2)LEFT.LabelFinal[1..2]-(INTEGER2)LEFT.LabelInicial[1..2]) = ((INTEGER2)RIGHT.CodGrupo[1..2]-(INTEGER2)LEFT.LabelInicial[1..2])) AND ((INTEGER2)RIGHT.CodGrupo[4..5] >= (INTEGER2)LEFT.LabelInicial[4..5]) AND ((INTEGER2)RIGHT.CodGrupo[4..5] < (INTEGER2)LEFT.LabelFinal[4..5]) => TRUE,
(((INTEGER2)LEFT.LabelFinal[1..2]-(INTEGER2)LEFT.LabelInicial[1..2]) = ((INTEGER2)RIGHT.CodGrupo[1..2]-(INTEGER2)LEFT.LabelInicial[1..2])) AND ((INTEGER2)RIGHT.CodGrupo[4..5] = (INTEGER2)LEFT.LabelFinal[4..5]) AND ((INTEGER2)LEFT.LabelInicial[7..9] <= (INTEGER2)RIGHT.CodGrupo[7..9]) AND ((INTEGER2)RIGHT.CodGrupo[7..9] <= (INTEGER2)LEFT.LabelFinal[7..9]) => TRUE,
FALSE),
DeNormThem(LEFT,RIGHT,COUNTER));

Best regards,

[PAULO FERNANDO BRUNOZI]

Hi,
I have changed the DENORMALIZE logic using LoseDots string function, but got the same error message:

Error: JOIN denormedrecs contains no equality conditions - use ,ALL to allow (43, 17 - C:\Users\Public\Documents\HPCC Systems\ECL\My Files\BrunozziAgro\DenormalizeBrunozi.ecl)

Any insight?

[hwatanuki]

Olá Paulo,

Como você está usando um intervalo de valores na condição de ligação entre os dois dataset´s (por meio do BETWEEN), é necessário incluir a opção "ALL" na função DENORMALIZE.

Veja abaixo, um exemplo de código funcional para esse DERNOMALIZE baseado na proposta do Richard acima.

HTH,
hwatanuki

Code: Select all

IMPORT $;

DenormedRec := RECORD
   $.File_BrunozziLblAnalise.Rec;
   UNSIGNED1 NumRows ;
   DATASET($.File_BrunozziAgroDB.Layout) Children ;
END;

BrunozziDB := $.File_BrunozziAgroDB.File;
SORT(BrunozziDB,CodGrupo);
BrunozziGRP := $.File_BrunozziLblAnalise.File;

DenormedRec ParentLoad($.File_BrunozziLblAnalise.Rec Le) := TRANSFORM
  SELF.NumRows  := 0;
  SELF.Children := [];
  SELF := Le;
END;
ParentTbl:= PROJECT(BrunozziGRP,ParentLoad(LEFT));

ParentTbl DeNormThem(ParentTbl Le, BrunozziDB Ri , INTEGER Ct) := TRANSFORM
  SELF.NumRows := Ct;
  SELF.Children := Le.Children + Ri;
  SELF := Le;
END;

                                          
LoseDots(STRING9 s) := s[1..2] + s[4..5] + s[7..9];
DeNormedRecs := DENORMALIZE(ParentTbl,BrunozziDB,
                  LoseDots(RIGHT.CodGrupo) BETWEEN
                            LoseDots(LEFT.LabelInicial) AND LoseDots(LEFT.LabelFinal),
             DeNormThem(LEFT,RIGHT,COUNTER), ALL);
                                          
OUTPUT(DeNormedRecs,NAMED('BrunoziChildDataset'));