URGENT: Need something like Break By

N

nate100

Member
Hello,
Say if I have the following records:

F
G
A
B
B
G
C
D

What I like to do is a break-by but without the sorting.
The end result I like is to have it be in the same order but give me unique values:

F
G
A
B
C
D

As mentioned, if I do a break-by it sorts it but I do not need the sorting. I need it to show in the same order but without the duplicates.

Thanks
 
If that's only for displaying, you can use the SQL function "DISTINCT" :
Code: 
SELECT DISTINCT myField FROM myTable.
 
Otherwise, here some 4GL code :

Code: 
DEF VAR liste AS CHARACTER NO-UNDO.

FOR EACH myTable NO-LOCK :
  IF LOOKUP(myField,liste) = 0
  THEN DO :
    DISP myField.
    liste = liste + "," + myField.
  END.
END.

which may work.
 
yes it will cause a conflict and you have to be careful with the limit of the char variable.

Using a Temp-Table would be safer.

Code: 
DEF TEMP-TABLE ttCheck
   FIELD f AS CHAR.

FOR EACH myTable NO-LOCK:
  IF CAN-FIND (ttCheck WHERE ttCheck.f = myTable.myField NO-LOCK)     THEN NEXT.

  CREATE ttCheck.
  ttCheck.f = myTable.myField.

  < Your code here >
END.
 
nate100 said:
Hello,
What happens if myField has a , in it. Will this cause a conflict with LOOKUP.

Thanks
Click to expand...
That's true... Use another separator character instead of the coma and specify it when you call the LOOKUP : LOOKUP(myField,liste,separator).

But François is right, there's a risk that you reach the 32k limit of the character variable...
 
I use a buffer and do a find.

def temp-table t_table
field field1 as char.

def temp-table t_list
field field1 like t_table.field1
index idx1 field1.

def buffer b_table for t_table.

create t_table. t_table.field1 = "F".
create t_table. t_table.field1 = "G".
create t_table. t_table.field1 = "A".
create t_table. t_table.field1 = "B".
create t_table. t_table.field1 = "B".
create t_table. t_table.field1 = "G".
create t_table. t_table.field1 = "C".
create t_table. t_table.field1 = "D".

for each t_table no-lock:
find b_table where b_table.field1 = t_table.field1 no-lock no-error.
if ambiguous (b_table) then do:
find t_list where t_list.field1 = t_table.field1 no-lock no-error.
if available t_list then next.
create t_list.
t_list.field1 = t_table.field1.
end.
display t_table.
end.


What this _should_ do is to check each record in table, check whether it is ambiguous, i.e. there are more thanone record with the same criteria, if so then check if that criteria has been met before, if it has then ignore the record (next) if not then create the temp-table to stop that criteria from being checked again.

Just change the names of t_table and field1 and the routine will work for any tables.
 
You must log in or register to reply here.
Back
Top