Usage of BREAK BY

[Chemi]

Usage of BREAK BY and OUTER-JOIN

Hello all.

I am very newbie with Progress and OpenEdge, still learning via documentation. I come from Oracle.

Firstly, many thanks to the community of ProgressTalk, this forum has saved my life meny times.

I have a problem using BREAK BY and OUTER-JOIN in a query. I am trying to do something like this:

FOR EACH socios WHERE socios.estado = 'A',
    EACH afiliaciones OF socios WHERE afiliaciones.tipo <> 'D' AND
                                     afiliaciones.estado = 'A' AND
                                     afiliaciones.cod_periodicidad = 1,
    EACH devo_impa OF afiliaciones WHERE devo_impa.cod_motivo <> 11 AND
                                         devo_impa.cod_motivo <> 12 AND
                                         devo_impa.cod_motivo <> 13 AND
                                         devo_impa.cod_motivo <> 14 AND
                                         devo_impa.cod_agrupacion = 0 AND
                                         devo_impa.fecha_recibo > 05/01/2008 AND
                                         devo_impa.fecha_recibo > 07/31/2008,
    EACH ayuda.telefonos OUTER-JOIN OF socios
 BREAK BY socios.cod_socio:
    DISP "data to display".
END.

It generates error 2833 (Join can only be used in OPEN QUERY statements).

I add the OPEN QUERY statement, but then I obtain error 14283 (query must be defined as SCROLLING to use BREAK BY). I tried to search error 14283 in ProKB, but it shows me "Unused message".

I am trying to use SCROLLING in the sentence, but I am not able to do it.

Could anyone help me?

As additional information, when completed (it is still in early stages) my code in Oracle would be something like this:

SELECT socios.cod_socio, COUNT(distinct(devo_impa.cod_recibo))
FROM socios, afiliaciones, devo_impa, telefonos
WHERE socios.estado = 'A' AND
    socios.cod_socio NOT IN
    (SELECT DISTINCT(cod_socio)
     FROM devo_impa
     WHERE  DEVO_IMPA.Cod_motivo NOT IN (11, 12, 13, 14) AND
        DEVO_IMPA.Cod_agrupacion = 0 AND
        DEVO_IMPA.fecha_recibo < 05/01/2008) AND
    afiliaciones.cod_socio = socios.cod_socio AND
    AFILIACIONES.Tipo <> 'D' AND
    AFILIACIONES.Estado = 'A' AND
    AFILIACIONES.Cod_periodicidad = 1 AND
    devo_impa.cod_socio = socios.cod_socio AND
    devo_impa.cod_afiliacion = afiliaciones.cod_afiliacion AND
    DEVO_IMPA.Cod_motivo NOT IN (11, 12, 13, 14) AND
    DEVO_IMPA.Cod_agrupacion = 0 AND
    DEVO_IMPA.fecha_recibo BETWEEN 05/01/2008 AND 07/31/2008 AND
    socios.cod_socio = telefonos.cod_socio(+) AND
GROUP BY socios.cod_socio 
HAVING COUNT(distinct(devo_impa.cod_recibo)) BETWEEN 2 AND 3.

I hope my english will be understandable...

Thanks in advance.
Regards.
- José Miguel Giménez

 

[germanUser]

Hello Mr Giménez,
i changed your code and i hope it will hit your requirements.

[quote] 
DEFINE VARIABLE lFound      AS LOGICAL      NO-UNDO.
FOR EACH socios WHERE socios.estado = 'A' NO-LOCK,
    EACH afiliaciones OF socios WHERE afiliaciones.tipo <> 'D' AND
                                     afiliaciones.estado = 'A' AND
                                     afiliaciones.cod_periodicidad = 1 NO-LOCK,
    EACH devo_impa OF afiliaciones WHERE devo_impa.cod_motivo <> 11 AND
                                         devo_impa.cod_motivo <> 12 AND
                                         devo_impa.cod_motivo <> 13 AND
                                         devo_impa.cod_motivo <> 14 AND
                                         devo_impa.cod_agrupacion = 0 AND
                                         devo_impa.fecha_recibo > 05/01/2008 AND
                                         devo_impa.fecha_recibo > 07/31/2008 NO-LOCK BREAK BY socios.cod_socio:
    ASSIGN lFound = FALSE.
    FOR EACH ayuda.telefonos OF socios NO-LOCK:
        ASSIGN lFound = TRUE.
        DISP "data to display".
    END.
    IF NOT lFound THEN DO:
        DISP "data to display".
    END.
END.
 
 
[/quote

]

Why you need the break statement? If you want sort the output your performance will grow up if you use an an fitting index.

Regards

germanUser

 

[Chemi]

Hello.

Thank you very much. I'll try your changes.

I need the BREAK statement to show de number of DEVO_IMPA records related with each SOCIOS record.

Regards.
- José Miguel Gimenez

 

[TomBascom]

Index usage is probably going to be fairly poor with all of those <> in the WHERE clause.