1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Select Distinct <> First Of

Discussion in 'Development' started by pinne65, Aug 25, 2017.

  1. pinne65

    pinne65 Member

    Been trying to figure this one out for a a while w.o. success. What am I missing here?

    OE 11.X

    They say SELECT DISTINCT = BREAK BY & FIRST OF: Progress KB - What is an ABL equivalent for a SQL SELECT DISTINCT?

    Code (progress):
    2. FOR EACH icsec NO-LOCK WHERE cono = 1 AND icsec.altprod = '33-835'
    3.    BREAK BY icsec.altprod BY icsec.prod BY icsec.custno:
    4.         IF FIRST-OF(icsec.altprod) AND FIRST-OF(icsec.prod) AND
    5. FIRST-OF(icsec.custno) THEN
    6. DISP icsec.altprod icsec.prod icsec.custno.
    7. END.
    33-835 076174338355 0

    SELECT DISTINCT icsec.altprod, icsec.prod, icsec.custno
    FROM icsec
    WHERE icsec.alt-prod = '33-835'
    33-835 076174338355 0
    33-835 10092080 1608
    33-835 1857625 2716
    33-835 1857625 9300
    33-835 1857625 9310
    33-835 1857625 9313
    33-835 1857625 9314
    33-835 1857625 9316
    33-835 1857625 9317
    Last edited by a moderator: Aug 26, 2017
  3. tamhas

    tamhas ProgressTalk.com Sponsor

    Why would you expect these to be the same. In the first on you used two fields to make the selection; in the second you used one. In the first you are ganging together three FIRST-OF, which is obviously not doing what you think. On the very first record, all three fields will be first-of, but when the next unique value of the second field shows up, the first field will no longer be first-of, so that won't print. Likewise for both the first and second fields when there is a break by the third field.

    And, of course, there is always the chance of a different index being used.
  4. Cringer

    Cringer ProgressTalk.com Moderator Staff Member

    What happens if you change it to:

    IF FIRST-OF(icsec.altprod) OR FIRST-OF(icsec.prod) OR
    FIRST-OF(icsec.custno) THEN
  5. pinne65

    pinne65 Member

    Ooops - Yea, typoed the sql query example above. Adding the same criteria, cono = 1 does yield the same result as depicted though, which was what Intended to show.

    I was just trying following the example in the Progress KB:
    Works in OpenEdge 10.x and 11.x

    Replace "SomeTable, "SomeConditions", "SomeField1" and "SomeField2" with your values

    FOR EACH SomeTable NO-LOCK WHERE SomeConditions BREAK BY SomeField1 BY SomeField2:
    IF FIRST-OF(SomeField1) AND FIRST-OF(SomeField2) THEN
    /* Do What You Want With The Record Here */

    Thanks - OR produced the desired result!

Share This Page